Statistical functions in Microsoft Excel

Knowing the statistical formulas and techniques, you can process, analyze and organize a large amount of information. In Excel, statistics tools are displayed in a separate category of functions. Let’s see how to find them, as well as which ones are the most popular among users.

Content

Using statistical functions

Function formulas in Excel can be entered manually directly in the cell where you plan to perform the corresponding calculations. This is easily applicable to simple operations such as addition, subtraction, multiplication, and division. But remembering the formulas of complex functions is no longer easy, so it’s easier to use a special assistant that is built into the program.

So, to insert a function into a cell, do one of the following:

  1. From any tab of the program, click on the icon “Insert function” (fx), which is on the left side of the formula bar.Statistical functions in Microsoft Excel
  2. Go to the tab “Formulas”, where we see a button in the left corner of the tool ribbon “Insert function”.Statistical functions in Microsoft Excel
  3. Using a keyboard shortcut Shift + F3.

Regardless of the method chosen above, a window for inserting functions will appear in front of us. Click on the current category and select an item from the drop-down list “Statistical”.

Statistical functions in Microsoft Excel

Next, one of the statistical operators will be offered to choose from. Mark the desired one and click OK.

Statistical functions in Microsoft Excel

A window will appear on the screen with the arguments of the selected function that you need to fill in.

Statistical functions in Microsoft Excel

Note: there is another way to select the required function. Being in the tab “Formulas” in the toolbox “Function Library” click on the icon “Other Features”, then select the item “Statistical” and, finally, in the list that opens (which can be scrolled down) – the desired operator.

Statistical functions in Microsoft Excel

Let’s now look at the most popular features.

AVERAGE

The operator calculates the arithmetic mean of the specified values ​​(range). The function formula looks like this:

=SRVALUE(number1;number2;…)

As function arguments, you can specify:

  1. specific numbers;
  2. links to cells that can be specified either manually (typed using the keyboard) or, being in the corresponding field, click on the desired element in the table itself;
  3. range of cells – specified manually or by selecting in the table.
  4. the transition to the next argument occurs by clicking on the corresponding field opposite it or simply by pressing the key Tab.

    Statistical functions in Microsoft Excel

MAX

The function helps to determine the maximum value from the given numbers (range). The operator formula is as follows:

=MAX(number1,number2,…)

In function arguments, as well as in the case of the operator AVERAGE you can specify specific numbers, cell references, or ranges of cells.

Statistical functions in Microsoft Excel

MIN

The function finds the minimum number from the specified values ​​(range of cells). In general, the syntax looks like this:

=MIN(number1,number2,…)

Function arguments are filled in the same way as for the operator MAX.

Statistical functions in Microsoft Excel

HEARTLESS

The function allows you to find the arithmetic mean, but under a given condition. Operator formula:

=SRINUMBERS(range, condition, range_averages)

The arguments are:

  1. Cell range – manually or using selection in the table;
  2. The condition for selecting values ​​from the specified range (greater than, less than, not equal to) – in quotes;
  3. Average_range – is not a mandatory argument to fill.Statistical functions in Microsoft Excel

MEDIAN

The operator finds the median of a given range of values. Function syntax:

=MEDIAN(number1,number2,…) 

The arguments are specified: specific numbers, references to cells or ranges of elements.

Statistical functions in Microsoft Excel

LARGE

The function allows you to find from the specified range of values ​​with a given position (descending). Operator formula:

=LARGE(array,k)

There are two arguments to the function: an array and a position number – K.

Statistical functions in Microsoft Excel

Suppose there is a series of numbers 4, 6, 12, 24, 15, 9. If we specify as an argument “K” number 2, the result will be a value equal to 15, because it is the second largest in the selected range.

LEAST

A function is the same as an operator LARGE, searches from the specified range of values. However, in this case, the score is in ascending order. The operator syntax is as follows:

=SMALL(array,k)

Statistical functions in Microsoft Excel

FASHION.ONE

The function has replaced the older operator “FASHION” (now categorized “Full alphabetical list”). Allows you to determine the number that repeats more often than others in the selected range. The function works according to the formula:

=MODE.ONE(number1;number2;…)

Argument values ​​specify specific numeric values, individual cells, or their ranges.

Statistical functions in Microsoft Excel

For vertical arrays, also use the function FASHION.NSK.

STDEV

Function STDEV also deprecated (but can still be found by selecting the alphabetical list) and is now represented by two new ones:

  • HERD DEVIATION.V – finds the standard deviation of the sample
  • HERD DECLINE.G – determines the standard deviation for the general population

Function formulas look like this:

  • = HERD DEVIATION.B(number1, number2,…)
  • = STANDARD DEVIATION.H(number1; number2;…)

Statistical functions in Microsoft Excel

SERGEOM

The operator finds the geometric mean for a given array or range. Function formula:

=SRGEOM(number1;number2;…)

Statistical functions in Microsoft Excel 

Conclusion

There are more than 100 statistical functions in Excel. We only looked at the ones that are used most often by users, as well as where they can be found and how to fill in the arguments to get the correct result.

Leave a Reply