Contents
Statistics is a science that is used for any other research, as well as processing a large amount of quantitative and even qualitative data. And importantly, this is one of the main uses of Excel spreadsheets, so let’s take a closer look at statistical formulas. First, what do they give us? First of all, they allow you to structure information and analyze it. Statistical functions in Excel belong to a completely separate category.
How to use statistical functions
There are several ways to enter any function, and statistical ones are no exception:
- Enter directly in the cell, after pressing the = key. This applies to the simplest functions that are easy to remember and contain one or two arguments. For example, you can do this for multiplication, addition, subtraction, and division. But if the function is complex, then you can use the assistant. This is the second way.
- Function helper. It not only tells you which formula means what, but also helps you enter the correct arguments for a particular function.
You can call the helper in several ways:
- Use the “Insert Function” button located to the left of the formula bar.
- Call the function entry wizard through the “Insert function” button, which is located on the left side of the panel, which opens by clicking on the “Formulas” tab.
- Using the hot keys Shift+F3.
Any of these methods leads to one result – the call of the function wizard. You can use the one that is most suitable for a particular situation. After the window opens, the first thing we need to do is select a category: statistical functions.
After the function type is selected, we need to select the appropriate formula from the list. Under the list, we see that there is a description that tells what a particular function does.
To confirm the selection of the function to be entered, press the OK key. After that, a window will appear in which you can enter the parameters of the function (or, as they are also called, arguments).
Interesting fact. You can select a function in another way. To do this, go to the “Formulas” tab and click on the “Other functions” button located on the ribbon.
Next, there will be an item “Other functions” – “Statistical” and in the list that appears, look for a suitable function and select it. This list is scrollable.
List of statistical functions
And now let’s go directly to the consideration of statistical functions.
SRGEOM function
Many people know about such a parameter as the arithmetic mean. It is calculated using a function, which we will definitely talk about today. But there is another function that determines the geometric mean.
The formula is very simple: =SRGEOM(number1;number2;…). In addition to numbers, you can also specify a range of values that are taken into account by this function. What is the geometric mean? This is a number that can replace any of the numbers in the sequence in such a way that the product of these values does not change. Another commonly used term is the proportional mean. It is synonymous with the geometric mean. Such a second term is used because the geometric mean is proportional to the first and second numbers.
STANDARD DEVIATION function
One of the main statistical parameters that should be calculated instead of the arithmetic mean is the standard deviation. This is a measure that shows the degree of dispersion of values. It performs the same function as the variance, it is simply represented in the same form as the mean, as opposed to the variance.
In general, the standard deviation is calculated as the square root of the variance. But in Excel there is a special formula that immediately calculates the degree of dispersion, after which, based on the obtained value, it receives the standard (or standard) deviation.
This formula itself is quite old, but you need to know about it, because from time to time it can be found in ready-made tables. Now there are newer versions of this function – STDEV.V и STDEV.G. The latter function finds the standard deviation for the general population, while the former focuses solely on the sample.
Otherwise, the syntax of both functions is the same as for calculating the arithmetic mean (we’ll talk about this later) – the numbers that are listed through the bracket.
MODE.ONE function
The fashion of the sample has absolutely nothing to do with clothes or popular cars. But at the same time, it is associated with the word “popular”. If we talk about statistics, then this is the value in the sample that occurs most often. Accordingly, the function FASHION.ONE makes it possible to determine this value.
In terms of syntax, it is similar to many other statistical functions. First, the operator is written, after which its arguments are written in brackets, which are numbers separated by a comma. The value of the argument can be not only a number, but also individual cells, ranges of values. This makes it possible to more flexibly manage the sample. This screenshot clearly shows how it works in practice.
This function is suitable for horizontal arrays. If you want to determine the sampling mode for a vertical array, use a similar function FASHION.NSK. The general appearance of the function is as follows: =MODA.ONE(argument 1, argument 2; argument …).
SMALL function
The task of this function is to perform a search from the set of values that was specified by the user. The principle of its operation is the same as the following, only the search is carried out in the direction from the bottom up, from the smallest number to the largest. The syntax for this function is extremely simple: =SMALL(array,k).
The function has two main arguments: an array of data to be searched for and the ordinal number of the element to be found. Further, the function works as follows: first, it looks for the smallest value, then it starts iterating over the numbers from bottom to top. The first value is considered 1. That is, if you use the number 1 in the second argument, the result will be equivalent to the function MIN, which we’ll talk about a little later.
GREATEST function
Function LARGE is similar, only the countdown is performed starting from the largest value. After passing the coefficient to it, it searches in the ordinal series from the largest to the smallest number that occupies the corresponding place and returns it. Both functions work in the same way. Suppose we have a number series. If you specify 2 as the number k in it, then the result will be the number 15, since it is the second largest in the range that is specified in the first argument.
This function can be useful in situations, for example, when the goods arrived in a certain sequence, and you need to determine how much, for example, the fur coat that came second in a row cost.
MEDIAN function
In statistics, the median is a type of average that is exactly in the middle of the number series. Very often, the median is a better solution than the standard arithmetic mean, because it allows you to determine the actual average value. The syntax of this function is similar to that of any other operator that defines the average value – a list of numbers, cells or ranges from which the data will be obtained.
This example shows how to work with the function in practice. You can enter a large number of numbers, cells, and ranges in the Function Arguments dialog box. In the picture, we tried to enter a number in the first row, a cell in the second, and a range of values in the third. We got the number 12 as a result. The maximum number of arguments for this function is 255, which is more than enough for the full use of this function.
Function SZNACHESLI
This is an improved version of the feature. AVERAGE, whose task is to find the arithmetic mean, but only under the condition that a certain condition is met. This function is already somewhat more complicated than those given above: =SRINUMBERS(range, condition, range_averages). Let’s look at each argument in more detail:
- Range. These are the cells that are checked to see if they meet a certain condition.
- Condition. This is the criterion against which the range is checked.
- Averaging range. This is the range from which the arithmetic mean will be taken. This argument is optional because the cell range and the average range can be the same.
MIN function
In statistical calculations, it is often necessary not only to determine the average value, standard deviation and calculate other indicators. The value of the smallest and largest number is also important, including for obtaining these indicators. The practical application of this function is quite extensive:
- The stock market to determine the time when the target was the lowest.
- To identify weaknesses in the annual budget (for example, in which month the company’s income was minimal) in order to correct them further. For example, you can determine the least profitable month and analyze the factors that contributed to this.
There are a huge number of other situations where you can use the function MIN. In its most general form, it looks like this: =MIN(number1,number2,…). The principle of filling the arguments of this function is similar to the function MAX.
Function MAX
As the name implies, this function looks for the maximum value in a certain number sample. The situations in which it can be used are, in principle, the same, with the only exception that everything is in the opposite direction. For example, a company can use the function MAX determine the most profitable month and understand what are the reasons for this success.
Functions SRZNAČ and SRZNAČ
Standard Function AVERAGE determines the arithmetic mean in a numeric sample. The general form of the formula is the same as for any other selection of values. First, the name of the function is written, after which the numbers and ranges that need to be processed using this function are given in brackets. That is, the general form of the formula is as follows: =SRIVAL(number1;number2;…).
As we understood, it can be used as regular numbers (very useful for using values that will not change in the near future), cell references (they are used for those values that will change in the future) and range (in this case, a whole set of numbers at once). To start writing another argument after entering one argument, just click on the corresponding field in the function wizard or simply press the Tab key.
The maximum number of arguments that can be used in this function is 255. In this case, only the first number is a required argument. Text and boolean values cannot be used as arguments. They are simply not taken into account by a formula that uses the specified operator. The main difference between the function HEART from AVERAGE is that text values and “FALSE” are considered zero, and the value “True” is equated to one.
Function RANK.AVG
Using the function RANK.SR the user can return the rank of a number. If multiple numbers in the same range are of the same rank, then the average is returned. It has three arguments, two of which are required:
- Number. This is the number for which the rank is determined.
- Link. This is an array of numbers, or a reference to this array.
- Order. This is a number that affects the way in which the values will be ordered.
Thus, Excel statistical functions are an excellent tool for processing large amounts of information.