Contents
Calculating the arithmetic average is a fairly common procedure that Excel users have to do. There are several possible methods at once that allow you to do this in a variety of situations, including the use of statistical functions. Let’s take a closer look at what to do to calculate the arithmetic average in Excel?
How to find the arithmetic mean of numbers (mathematics)?
To determine the arithmetic mean of several numbers or cells, you need to take all the values in the sequence, perform an addition operation between them, and divide the result by their total number. It is very easy to show the work of the arithmetic mean using the example of school grades in the report card, since the average score is familiar to everyone who studied at an institution of secondary education.
Suppose we have a final table that shows the grades of one student for the completed intermediate tests: 3, 4, 3, 5, 5. The average value for the quarter in this case will be 4 points.
Excel arithmetic mean
Let’s say we have a table with a set of specific values. What they mean is not so important in this situation. Let it be a random set of numbers. This will not affect the logic of the reasoning in any way.
We need to left-click on cell A2, thereby activating it. After that, go to the main menu or pay attention to the ribbon, find the “Editing” option there, and in the menu that appears, left-click on the “Amount” button. In this case, the click is carried out not on the icon itself, but on the arrow located next to it. After that, another menu will appear, in which there will be a set of different functions. In our case, we are interested in “Average”. After you click on it, the formula will be automatically written in the selected cell.
That’s not all. After that, you need to enter the argument of the function, for which it is enough just to select those cells for which the definition of the arithmetic mean is required. After that, press the “Enter” key.
Syntax SRZNACH
As you can see, we use the formula AVERAGE, which has its own syntax. It is very simple. The equal sign is written first, as with any other function. After that, the name is written AVERAGE, after which the bracket is opened and one single argument is written – the range of values from which you want to get the arithmetic mean. After that, the bracket closes.
Arguments AVERAGE
In practice, the formula, together with the entered argument, will look like this.
=CORE(A1:A10).
Such a formula will calculate the sum of the values from the first to the tenth row, after which it will divide the resulting result by 10.
Calculating the Arithmetic Average Using the Function Wizard
The Function Wizard is a universal Excel feature that allows you to perform the most complex calculations without knowing the names of the formulas. It is enough just to choose the correct one from the list, and then enter the correct arguments. Moreover, all of them are shown in a separate dialog box with hints. So the user can easily figure out which function is responsible for what and what syntax it has.
It is recommended that you play around with this list at your leisure to get an idea of what features Excel has been designed for.
To call the function wizard, you must press the key combination Shift + F3 or find the fx key near the formula input line. After doing this, a window will appear in which we need to find the “AVERAGE” function. It is much easier to search for the function we need if we select its type. In the special drop-down menu located at the top of the screen, you need to select the item: “Statistical”. Then the list will be significantly narrowed and it will be easier to choose.
Then another window will appear in which you can enter the arguments of the function AVERAGE.
A private option is to call the function for outputting the arithmetic mean from the tape. To do this, you need to find the “Formulas” tab, then go to the “Other functions” section, there hover over the “Statistical” item with the mouse. After all these operations, the function will appear HEART.
Formula bar
Every document contains a formula bar that changes depending on which cell you select. If the formula is there, then it will be written there. If there is no formula, then just the value of the cell will be displayed there (for example, if just text is written there). This screenshot shows a specific example of how the formula input bar can be used. With its help, you can look at what formula lies behind a certain number (13,2) using an example, as well as edit the arguments. Or even remove the old formula and introduce a new one. Or remove all formulas, and leave an empty value or number. She has plenty of opportunities. You can choose any that will help you complete the task.
Manual entry of functions
Function AVERAGE refers to simple. It’s easy to remember, and it only takes one argument. Therefore, we will enter it manually. Let’s use the screenshot above as an example. As you can see, you can use two disparate ranges that are not directly connected to each other.
We will enter it manually.
=CORE(A1:B1;F1:H1)
Obviously, in the appropriate places you need to put your addresses. If you want them to be saved when copying to other cells, do not forget to make the links absolute. To do this, you need to select them, and then press the F4 button.
It is highly recommended that you memorize all the features you learn because it will save you a lot of time in the future.
Calculation of the average value by condition
You may want to average certain numbers only if they meet a particular criterion. The condition can be anything, both textual and numeric. It can also be written both directly in the formula and in other cells.
You can of course use the function IF combined with the function AVERAGE, but it’s a bit of a chore. If you have to use this combination very often, it all takes a lot of time. It makes much more sense to use the function HEARTLESS. It uses two or three arguments in different situations, but entering them is still faster than writing two different functions in one formula.
Imagine the following situation: we were given the task of determining the arithmetic mean for those values that are equal to or greater than 10.
The final formula will be:
=SIZED(A1:A8;»>=10″)
As a result, this value will be obtained.
Let’s analyze the arguments of this function in more detail.
- Range. This is directly the range in which the set of criteria will be contained.
- Condition. This is the immediate condition. That is, the value must somehow correlate with the criterion. In our case, it must be greater than or equal to 10.
- Averaging range. An optional argument that is used if the values for which to look for the arithmetic mean are located elsewhere, and are not directly criteria.
We omitted the third point because the range in the first argument is numeric, while it is better to use it only with text criteria there.
How to find average value given text?
If there is such a need, the criterion can be written in some cell, and then just give a link to it.
Now let’s find the arithmetic mean using the set of text values from column A as the range of the criterion.
The appearance of the function will be as follows: =СРЗНАЧЕСЛИ($A$2:$A$12;A7;$B$2:$B$12). The appropriate column is used here as a range (links are fixed in case you need to copy), and the search criterion will be the address of the cell containing the word “tables” (or you can just write this word in the appropriate place). And here we used column B as the averaging range.
Important! Specifying the averaging range is a mandatory requirement for text criteria.
How to calculate weighted average price in Excel?
To determine the weighted average price, you need to use the functions SUMPRODUCT и SUM. So, we have a sheet describing different types of goods, their quantity and cost.
To find out the weighted average price in this case, you need to use the formula.
=СУММПРОИЗВ(C2:C12;B2:B12)/СУММ(C2:C12).
This formula works in two steps. Function SUMPRODUCT allows you to determine the total amount of money that the company managed to earn after all the goods were sold. And then the function is used SUM, which simply determines the total number of items sold.
Next, the operation of dividing the total revenue of the goods by the number of units occurs. This is how it turned out to find the weighted average cost – an indicator that determines how significant a particular product is in the total revenue.
Standard deviation: formula in Excel
When talking about the arithmetic mean, there is definitely another concept somewhere nearby – the standard deviation (or just the standard deviation). But to understand what it is, you must first understand what variance is.
This term refers to the degree of dispersion of values. Still, the difference between a set of values 4 and 6 with an arithmetic mean of 5 and 1 and 9 with the same mean is enormous. In the first case, the dispersion is minimal, and in the second, the values are in a very wide range.
The formula for calculating the variance is quite complex, but it can be easily calculated using standard Excel tools. There are two functions for this: DISP.B and DISP.G
In practice, this value by itself is rarely used. It can be used to test the validity of a statistical hypothesis or to determine correlation coefficients. In the context of our article, the variance is used to determine the standard deviation, which is formed by a simple formula. It is necessary to extract the square root from the obtained value of the variance.
There are two types of standard deviation in Excel – for the general population and for the sample.
We do not need the dispersion formula to calculate the standard deviation (with the only exception, if for some reason it is already known, then you can simply extract the root from it). As you can see from the screenshot above, there are two standard deviation formulas in Excel.
Here, as we see, it is necessary to understand two more terms: general and sample population. The first is the entire range of analyzed data (society, for example), and the second is a part of this range, which should represent the general population (for example, a specific group of people that corresponds to it in terms of demographic, socio-economic indicators).
The standard deviation is characterized by a binding to the scale of the data. To get a full picture of how wide the range of values is, having absolute values alone is not enough. You still need to get relative.
For this, the coefficient of variation is used. To calculate it, you need to divide the standard deviation by the mean. It can be used if the value is non-zero, and it is useful in situations where knowing the mean value, you can understand how big the deviation is.
Thus, getting the arithmetic mean in Excel can be done in a number of ways. This is one of the most important formulas used in spreadsheets. Therefore, it is imperative to know it by heart. Moreover, it is easy to remember, the name is intuitive, and there is only one argument (although if you need to analyze several ranges, then the number of parameters will be large).