How to calculate the average value formula. Calculating the average value in Microsoft Excel

In mathematics, the arithmetic mean of numbers (or simply the average) is the sum of all the numbers in a given set divided by the number of numbers. This is the most general and widespread concept average size. As you already understood, to find the average, you need to sum up all the numbers given to you, and divide the resulting result by the number of terms.

What is the arithmetic mean?

Let's look at an example.

Example 1. Given numbers: 6, 7, 11. You need to find their average value.

Solution.

First, let's find the sum of all these numbers.

Now divide the resulting sum by the number of terms. Since we have three terms, we will therefore divide by three.

Therefore, the average of the numbers 6, 7 and 11 is 8. Why 8? Yes, because the sum of 6, 7 and 11 will be the same as three eights. This can be clearly seen in the illustration.

The average is a bit like “evening out” a series of numbers. As you can see, the piles of pencils have become the same level.

Let's look at another example to consolidate the knowledge gained.

Example 2. Given numbers: 3, 7, 5, 13, 20, 23, 39, 23, 40, 23, 14, 12, 56, 23, 29. You need to find their arithmetic mean.

Solution.

Find the amount.

3 + 7 + 5 + 13 + 20 + 23 + 39 + 23 + 40 + 23 + 14 + 12 + 56 + 23 + 29 = 330

Divide by the number of terms (in this case - 15).

Therefore, the average value of this series of numbers is 22.

Now let's consider negative numbers. Let's remember how to summarize them. For example, you have two numbers 1 and -4. Let's find their sum.

1 + (-4) = 1 – 4 = -3

Knowing this, let's look at another example.

Example 3. Find the average value of a series of numbers: 3, -7, 5, 13, -2.

Solution.

Find the sum of numbers.

3 + (-7) + 5 + 13 + (-2) = 12

Since there are 5 terms, divide the resulting sum by 5.

Therefore, the arithmetic mean of the numbers 3, -7, 5, 13, -2 is 2.4.

In our time of technological progress, it is much more convenient to use to find the average value computer programs. Microsoft Office Excel is one of them. Finding the average in Excel is quick and easy. Moreover, this program is included in the Microsoft Office software package. Let's consider brief instructions, how to find the arithmetic mean using this program.

In order to calculate the average value of a series of numbers, you must use the AVERAGE function. The syntax for this function is:
= Average(argument1, argument2, ... argument255)
where argument1, argument2, ... argument255 are either numbers or cell references (by cells we mean ranges and arrays).

To make it more clear, let’s try out the knowledge we have gained.

1. Enter the numbers 11, 12, 13, 14, 15, 16 in cells C1 – C6.
2. Select cell C7 by clicking on it. In this cell we will display the average value.
3. Click on the Formulas tab.
4. Select More Functions > Statistical to open the drop-down list.
5. Select AVERAGE. After this, a dialog box should open.
6. Select and drag cells C1 through C6 there to set the range in the dialog box.
7. Confirm your actions with the "OK" button.
8. If you did everything correctly, you should have the answer in cell C7 - 13.7. When you click on cell C7, the function (=Average(C1:C6)) will appear in the formula bar.

This feature is very useful for accounting, invoices, or when you just need to find the average of a very long series of numbers. Therefore, it is often used in offices and large companies. This allows you to keep your records in order and makes it possible to quickly calculate something (for example, average monthly income). Also with using Excel you can find the average value of the function.

Average

This term has other meanings, see average meaning.

Average(in mathematics and statistics) sets of numbers - the sum of all numbers divided by their number. It is one of the most common measures of central tendency.

It was proposed (along with the geometric mean and harmonic mean) by the Pythagoreans.

Special cases of the arithmetic mean are the mean ( population) and sample mean (samples).

Introduction

Let us denote the set of data X = (x 1 , x 2 , …, x n), then the sample mean is usually indicated by a horizontal bar over the variable (x ¯ (\displaystyle (\bar (x))), pronounced " x with a line").

The Greek letter μ is used to denote the arithmetic mean of the entire population. For random variable, for which the average value is determined, μ is probabilistic average or expected value random variable. If the set X is a collection random numbers with a probabilistic mean μ, then for any sample x i from this set μ = E( x i) is the mathematical expectation of this sample.

In practice, the difference between μ and x ¯ (\displaystyle (\bar (x))) is that μ is a typical variable because you can see a sample rather than the entire population. Therefore, if the sample is represented randomly (in terms of probability theory), then x ¯ (\displaystyle (\bar (x))) (but not μ) can be treated as a random variable having a probability distribution on the sample (the probability distribution of the mean).

Both of these quantities are calculated in the same way:

X ¯ = 1 n ∑ i = 1 n x i = 1 n (x 1 + ⋯ + x n) . (\displaystyle (\bar (x))=(\frac (1)(n))\sum _(i=1)^(n)x_(i)=(\frac (1)(n))(x_ (1)+\cdots +x_(n)).)

If X is a random variable, then the mathematical expectation X can be considered as the arithmetic mean of values ​​in repeated measurements of a quantity X. This is a manifestation of the law of large numbers. Therefore, the sample mean is used to estimate the unknown expected value.

It has been proven in elementary algebra that the mean n+ 1 numbers above average n numbers if and only if the new number is greater than the old average, less if and only if the new number is less than the average, and does not change if and only if the new number is equal to the average. The more n, the smaller the difference between the new and old averages.

Note that there are several other "averages" available, including the power mean, the Kolmogorov mean, the harmonic mean, the arithmetic-geometric mean, and various weighted averages (e.g., weighted arithmetic mean, weighted geometric mean, weighted harmonic mean).

Examples

• For three numbers, you need to add them and divide by 3:

x 1 + x 2 + x 3 3 . (\displaystyle (\frac (x_(1)+x_(2)+x_(3))(3)).)

• For four numbers you need to add them up and divide them by 4:

x 1 + x 2 + x 3 + x 4 4 . (\displaystyle (\frac (x_(1)+x_(2)+x_(3)+x_(4))(4)).)

Or simpler 5+5=10, 10:2. Because we were adding 2 numbers, which means how many numbers we add, we divide by that many.

Continuous random variable

For a continuously distributed quantity f (x) (\displaystyle f(x)), the arithmetic mean on the interval [ a ; b ] (\displaystyle ) is determined through a definite integral:

F (x) ¯ [ a ; b ] = 1 b − a ∫ a b f (x) d x (\displaystyle (\overline (f(x)))_()=(\frac (1)(b-a))\int _(a)^(b) f(x)dx)

Some problems of using the average

Lack of robustness

Main article: Robustness in statistics

Although arithmetic means are often used as averages or central tendencies, this concept is not a robust statistic, meaning that the arithmetic mean is heavily influenced by "large deviations." It is noteworthy that for distributions with a large coefficient of skewness, the arithmetic mean may not correspond to the concept of “mean”, and the values ​​of the mean from robust statistics (for example, the median) may better describe the central tendency.

A classic example is calculating average income. The arithmetic mean can be misinterpreted as a median, which may lead to the conclusion that there are more people with higher incomes than there actually are. “Average” income is interpreted to mean that most people have incomes around this number. This “average” (in the sense of the arithmetic mean) income is higher than the incomes of most people, since a high income with a large deviation from the average makes the arithmetic mean highly skewed (in contrast, the average income at the median “resists” such skew). However, this "average" income says nothing about the number of people near the median income (and says nothing about the number of people near the modal income). However, if you take the concepts of “average” and “most people” lightly, you can draw the incorrect conclusion that most people have incomes higher than they actually are. For example, a report of the "average" net income in Medina, Washington, calculated as the arithmetic average of all annual net incomes of residents, would produce a surprisingly large number due to Bill Gates. Consider the sample (1, 2, 2, 2, 3, 9). The arithmetic mean is 3.17, but five out of six values ​​are below this mean.

Compound interest

Main article: Return on Investment

If the numbers multiply, but not fold, you need to use the geometric mean, not the arithmetic mean. Most often this incident occurs when calculating the return on investment in finance.

For example, if a stock fell 10% in the first year and rose 30% in the second, then it is incorrect to calculate the “average” increase over those two years as the arithmetic mean (−10% + 30%) / 2 = 10%; the correct average in this case is given by the compound annual growth rate, which gives an annual growth rate of only about 8.16653826392% ≈ 8.2%.

The reason for this is that percentages have a new starting point each time: 30% is 30% from a number less than the price at the beginning of the first year: if a stock started out at $30 and fell 10%, it is worth $27 at the start of the second year. If the stock rose 30%, it would be worth $35.1 at the end of the second year. The arithmetic average of this growth is 10%, but since the shares rose by only $5.1 in 2 years, average height gives 8.2% final result $35.1:

[$30 (1 - 0.1) (1 + 0.3) = $30 (1 + 0.082) (1 + 0.082) = $35.1]. If we use the arithmetic average of 10% in the same way, we will not get the actual value: [$30 (1 + 0.1) (1 + 0.1) = $36.3].

Compound interest at the end of 2 years: 90% * 130% = 117%, that is, the total increase is 17%, and the average annual compound interest is 117% ≈ 108.2% (\displaystyle (\sqrt (117\%))\approx 108.2\%) , that is, an average annual increase of 8.2%.

Directions

Main article: Destination statistics

When calculating the arithmetic mean of some variable that changes cyclically (such as phase or angle), special care must be taken. For example, the average of 1° and 359° would be 1 ∘ + 359 ∘ 2 = (\displaystyle (\frac (1^(\circ )+359^(\circ ))(2))=) 180°. This number is incorrect for two reasons.

• First, angular measures are defined only for the range from 0° to 360° (or from 0 to 2π when measured in radians). So the same pair of numbers could be written as (1° and −1°) or as (1° and 719°). The average values ​​of each pair will be different: 1 ∘ + (− 1 ∘) 2 = 0 ∘ (\displaystyle (\frac (1^(\circ )+(-1^(\circ )))(2))=0 ^(\circ )) , 1 ∘ + 719 ∘ 2 = 360 ∘ (\displaystyle (\frac (1^(\circ )+719^(\circ ))(2))=360^(\circ )) .
• Secondly, in in this case, a value of 0° (equivalent to 360°) will be a geometrically better average, since the numbers deviate less from 0° than from any other value (the value 0° has the smallest variance). Compare:
  • the number 1° deviates from 0° by only 1°;
  • the number 1° deviates from the calculated average of 180° by 179°.

The average value for a cyclic variable calculated using the above formula will be artificially shifted relative to the real average towards the middle of the numerical range. Because of this, the average is calculated in a different way, namely, the number with the smallest variance ( center point). Also, instead of subtraction, the modular distance (that is, the circumferential distance) is used. For example, the modular distance between 1° and 359° is 2°, not 358° (on the circle between 359° and 360°==0° - one degree, between 0° and 1° - also 1°, in total - 2 °).

Weighted average - what is it and how to calculate it?

In the process of studying mathematics, schoolchildren become familiar with the concept of arithmetic mean. Later in statistics and some other sciences, students are faced with the calculation of other average values. What can they be and how do they differ from each other?

Averages: meaning and differences

Accurate indicators do not always provide an understanding of the situation. In order to assess a particular situation, it is sometimes necessary to analyze a huge number of figures. And then averages come to the rescue. They allow us to assess the situation as a whole.

Since school days, many adults remember the existence of the arithmetic mean. It is very simple to calculate - the sum of a sequence of n terms is divided by n. That is, if you need to calculate the arithmetic mean in the sequence of values ​​27, 22, 34 and 37, then you need to solve the expression (27+22+34+37)/4, since 4 values ​​are used in the calculations. In this case, the required value will be 30.

Often within school course Geometric mean is also studied. Calculation given value is based on extracting the nth root of the product of n-terms. If we take the same numbers: 27, 22, 34 and 37, then the result of the calculations will be equal to 29.4.

Harmonic mean in secondary school is not usually the subject of study. However, it is used quite often. This value is the inverse of the arithmetic mean and is calculated as the quotient of n - the number of values ​​and the sum 1/a 1 +1/a 2 +...+1/a n. If we again take the same series of numbers for calculation, then the harmonic will be 29.6.

Weighted average: features

However, all of the above values ​​may not be used everywhere. For example, in statistics, when calculating certain averages, the “weight” of each number used in the calculations plays an important role. The results are more indicative and correct because they take into account more information. This group of quantities is common name "weighted average"They are not taught in school, so it is worth looking at them in more detail.

First of all, it is worth telling what is meant by the “weight” of a particular value. The easiest way to explain this is specific example. Twice a day in the hospital the body temperature of each patient is measured. Out of 100 patients in different departments of the hospital, 44 will have a normal temperature - 36.6 degrees. Another 30 will have an increased value - 37.2, 14 - 38, 7 - 38.5, 3 - 39, and the remaining two - 40. And if we take the arithmetic average, then this value in general for the hospital will be more than 38 degrees! But almost half of the patients have a completely normal temperature. And here it would be more correct to use a weighted average, and the “weight” of each value would be the number of people. In this case, the calculation result will be 37.25 degrees. The difference is obvious.

In the case of weighted average calculations, the “weight” can be taken as the number of shipments, the number of people working on a given day, in general, anything that can be measured and affect the final result.

Varieties

The weighted average is related to the arithmetic mean discussed at the beginning of the article. However, the first value, as already mentioned, also takes into account the weight of each number used in the calculations. In addition, there are also weighted geometric and harmonic values.

There is another interesting variation used in number series. It's about about a weighted moving average. It is on this basis that trends are calculated. In addition to the values ​​themselves and their weight, periodicity is also used there. And when calculating the average value at some point in time, values ​​​​for previous time periods are also taken into account.

Calculating all these values ​​is not that difficult, but in practice only the ordinary weighted average is usually used.

Calculation methods

In the age of widespread computerization, there is no need to calculate the weighted average manually. However, it would be useful to know the calculation formula so that you can check and, if necessary, adjust the results obtained.

The easiest way is to consider the calculation using a specific example.

It is necessary to find out what the average wage is at this enterprise, taking into account the number of workers receiving one or another salary.

So, the weighted average is calculated using the following formula:

x = (a 1 *w 1 +a 2 *w 2 +...+a n *w n)/(w 1 +w 2 +...+w n)

For example, the calculation would be like this:

x = (32*20+33*35+34*14+40*6)/(20+35+14+6) = (640+1155+476+240)/75 = 33.48

Obviously not special difficulties in order to manually calculate the weighted average. The formula for calculating this value in one of the most popular applications with formulas - Excel - looks like the SUMPRODUCT (series of numbers; series of weights) / SUM (series of weights) function.

How to find the average in excel?

how to find the arithmetic mean in excel?

Vladimir09854

As easy as pie. To find the average in excel, you only need 3 cells. In the first we will write one number, in the second - another. And in the third cell we will enter a formula that will give us the average value between these two numbers from the first and second cells. If cell No. 1 is called A1, cell No. 2 is called B1, then in the cell with the formula you need to write this:

This formula calculates the arithmetic mean of two numbers.

To make our calculations more beautiful, we can highlight the cells with lines, in the form of a plate.

In Excel itself there is also a function for determining the average value, but I use the old-fashioned method and enter the formula I need. Thus, I am sure that Excel will calculate exactly as I need, and will not come up with some kind of rounding of its own.

M3sergey

This is very simple if the data is already entered into the cells. If you are interested in just a number, just select the desired range/ranges, and the value of the sum of these numbers, their arithmetic mean and their number will appear at the bottom right in the status bar.

You can select an empty cell, click on the triangle (drop-down list) “AutoSum” and select “Average” there, after which you will agree with the proposed range for calculation, or select your own.

Finally, you can use formulas directly by clicking "Insert Function" next to the formula bar and cell address. The AVERAGE function is located in the “Statistical” category, and takes as arguments both numbers and cell references, etc. There you can also select more complex options, for example, AVERAGEIF - calculating the average according to the condition.

Find average value in excel is a fairly simple task. Here you need to understand whether you want to use this average value in some formulas or not.

If you only need to get the value, then just select the required range of numbers, after which Excel will automatically calculate the average value - it will be displayed in the status bar, the heading "Average".

In the case when you want to use the result in formulas, you can do this:

1) Sum the cells using the SUM function and divide it all by the number of numbers.

2) A more correct option is to use a special function called AVERAGE. The arguments to this function can be numbers specified sequentially or a range of numbers.

Vladimir Tikhonov

Circle the values ​​that will participate in the calculation, click the “Formulas” tab, there you will see on the left there is “AutoSum” and next to it a triangle pointing down. Click on this triangle and select "Medium". Voila, done) at the bottom of the column you will see the average value :)

Ekaterina Mutalapova

Let's start from the beginning and in order. What does average mean?

The mean is a value that is the average arithmetic value, i.e. is calculated by adding a set of numbers and then dividing the entire sum of numbers by their number. For example, for the numbers 2, 3, 6, 7, 2 there will be 4 (the sum of the numbers 20 is divided by their number 5)

IN Excel spreadsheet For me personally, the easiest way was to use the formula = AVERAGE. To calculate the average value, you need to enter data into the table, write the function =AVERAGE() under the data column, and indicate the range of numbers in the cells in brackets, highlighting the column with the data. After that, press ENTER, or simply left-click on any cell. The result appears in the cell below the column. It looks incomprehensibly described, but in fact it’s a matter of minutes.

Adventurer 2000

Excel is a varied program, so there are several options that will allow you to find averages:

First option. You simply sum all the cells and divide by their number;

Second option. Use a special command, write the formula “= AVERAGE (and here indicate the range of cells)” in the required cell;

Third option. If you select the required range, please note that on the page below, the average value in these cells is also displayed.

Thus, there are a lot of ways to find the average, you just need to choose the best one for you and use it constantly.

In Excel, you can use the AVERAGE function to calculate the simple arithmetic average. To do this, you need to enter a number of values. Press equals and select Statistical in the Category, among which select the AVERAGE function

Also, using statistical formulas, you can calculate the weighted arithmetic mean, which is considered more accurate. To calculate it, we need indicator values ​​and frequency.

How to find the average in Excel?

This is the situation. There is the following table:

The columns shaded in red contain the numerical values ​​of grades in subjects. In the "Average Score" column, you need to calculate their average.
The problem is this: there are 60-70 items in total and some of them are on another sheet.
I looked in another document and the average has already been calculated, and in the cell there is a formula like
="sheet name"!|E12
but this was done by some programmer who was fired.
Please tell me who understands this.

Hector

In the functions line, you insert “AVERAGE” from the proposed functions and select where they need to be calculated from (B6:N6) for Ivanov, for example. I don’t know for sure about the adjacent sheets, but it’s probably contained in the standard Windows help

Tell me how to calculate the average value in Word

Please tell me how to calculate the average value in Word. Namely, the average value of the ratings, and not the number of people who received the ratings.

Yulia Pavlova

Word can do a lot with macros. Press ALT+F11 and write a macro program..
In addition, Insert-Object... will allow you to use other programs, even Excel, to create a sheet with a table inside a Word document.
But in this case, you need to write down your numbers in a column of the table, and enter the average in the bottom cell of the same column, right?
To do this, insert a field into the bottom cell.
Insert-Field... -Formula
Field content
[=AVERAGE(ABOVE)]
gives the average of the sum of the cells above.
If you select a field and click the right mouse button, you can Update it if the numbers have changed,
view the code or value of a field, change the code directly in the field.
If something goes wrong, delete the entire field in the cell and create it again.
AVERAGE means average, ABOVE - about, that is, a number of cells lying above.
I didn’t know all this myself, but I easily discovered it in HELP, of course, with a little thinking.

Excel is a varied program, so there are several options that will allow you to find averages:

First option. You simply sum all the cells and divide by their number;

Second option. Use a special command, write the formula = AVERAGE (and here indicate the range of cells) in the required cell;

Third option. If you select the required range, please note that on the page below, the average value in these cells is also displayed.

Thus, there are a lot of ways to find the average, you just need to choose the best one for you and use it constantly.

Let's start from the beginning and in order. What does average mean?

The mean is a value that is the arithmetic mean, i.e. is calculated by adding a set of numbers and then dividing the entire sum of numbers by their number. For example, for the numbers 2, 3, 6, 7, 2 there will be 4 (the sum of the numbers 20 is divided by their number 5)

In an Excel spreadsheet, for me personally, the easiest way was to use the formula = AVERAGE. To calculate the average value, you need to enter data into the table, write the function =AVERAGE() under the data column, and indicate the range of numbers in the cells in brackets, highlighting the column with the data. After that, press ENTER, or simply left-click on any cell. The result appears in the cell below the column. It looks incomprehensibly described, but in fact it’s a matter of minutes.

In Excel, you can use the AVERAGE function to calculate the simple arithmetic average. To do this, you need to enter a number of values. Press equals and select Statistical in the Category, among which select the AVERAGE function



Also, using statistical formulas, you can calculate the weighted arithmetic mean, which is considered more accurate. To calculate it, we need indicator values ​​and frequency.

This is very simple if the data is already entered into the cells. If you are interested in just a number, just select the desired range/ranges, and the value of the sum of these numbers, their arithmetic mean and their number will appear at the bottom right in the status bar.

You can select an empty cell, click on the triangle (drop-down list) AutoSum and select Average there, after which you will agree with the proposed range for calculation, or select your own.

Finally, you can use formulas directly by clicking Insert Function next to the formula bar and cell address. The AVERAGE function is located in the Statistical category, and takes as arguments both numbers and cell references, etc. There you can also select more complex options, for example, AVERAGEIF - calculating the average according to the condition.

As easy as pie. To find the average in excel, you only need 3 cells. In the first we will write one number, in the second - another. And in the third cell we will enter a formula that will give us the average value between these two numbers from the first and second cells. If cell 1 is called A1, cell 2 is called B1, then in the cell with the formula you need to write this:

This formula calculates the arithmetic mean of two numbers.

To make our calculations more beautiful, we can highlight the cells with lines, in the form of a plate.

In Excel itself there is also a function for determining the average value, but I use the old-fashioned method and enter the formula I need. Thus, I am sure that Excel will calculate exactly as I need, and will not come up with some kind of rounding of its own.

Here you can be given a lot of advice, but with each new advice you will have more new question, this may be good, on the one hand there will be an incentive to improve your level on this site, so I won’t give you a bunch of advice, but I will give you a link to a YouTube channel with a course on mastering such a necessary application as Excel, it’s your right to use it or not, know you will have a link to a detailed course where you will always find the answer to your question in Excel

Circle the values ​​that will be involved in the calculation, click the Formulas tab, there you will see on the left there is AutoSum and next to it a triangle pointing down. Click on this triangle and select Average. Voila, done) at the bottom of the column you will see the average value :)

When working with numerical expressions, sometimes there is a need to calculate their average value. called the arithmetic mean. In Excel, a spreadsheet editor from Microsoft, it is possible not to calculate it manually, but to use special tools. This article will present methods that allow you to find out and derive the number of the arithmetic mean.

Method 1: standard

First of all, let's look at the way to calculate the arithmetic mean in Excel, which involves using a standard tool for this. The method is the simplest and most convenient to use, but it also has some disadvantages. But more about them later, and now let’s move on to completing the task at hand.

1. Select the cells in the column or row that contain numeric values for calculation.
2. Go to the "Home" tab.
3. On the toolbar in the “Editing” category, click on the “AutoSum” button, but you must click on the arrow next to it so that a drop-down list appears.
4. In it you need to click on the “Average” item.

As soon as you do this, the result of calculating the arithmetic mean of the selected values ​​will appear in the cell next to it. Its location will depend on the data block; if you selected a row, then the result will be located to the right of the selection, if a column, it will be below.

But as was said earlier, this method there are also disadvantages. So, you will not be able to calculate a value from a range of cells, or cells located in different places. For example, if your table contains two adjacent columns with numeric values, then by selecting them and performing the steps described above, you will get the result for each column separately.

Method 2: Using the Function Wizard

There are many ways to find the arithmetic mean in Excel, and naturally, with their help it is possible to bypass the limitations of the previous method. Now we will talk about performing calculations using the Function Wizard. So here's what you need to do.

1. By clicking the left mouse button, select the cell in which you want to see the calculation result.
2. Open the Function Wizard window by clicking the “Insert Function” button located to the left of the formula bar or using the Shift+F3 hotkeys.
3. In the window that appears, find the line “AVERAGE” in the list, highlight it and click the “OK” button.
4. A new window will appear for entering function arguments. In it you will see two fields: “Number1” and “Number2”.
5. In the first field, enter the addresses of the cells in which the numeric values ​​for calculation are located. This can be done either manually or using a special tool. In the second case, click on the button located on the right side of the input field. The Wizard window will collapse and you will need to select the cells for calculation with the mouse.
6. If another range of cells with data is located elsewhere in the sheet, then indicate it in the “Number2” field.
7. Continue entering the data until you have provided all the required information.
8. Click OK.

When you complete the input, the Wizard window will close, and the result of the calculation will appear in the cell that you selected at the very beginning. Now you know the second way to calculate the arithmetic mean in Excel. But it’s far from the last, so let’s move on.

Method 3: Through the Formula Bar

This method of how to calculate the arithmetic mean in Excel is not much different from the previous one, but in some cases it may seem more convenient, so it’s worth looking into. For the most part, this method only offers an alternative option for calling the Function Wizard.

As soon as all the actions in the list are completed, the Function Wizard window will appear in front of you, where you need to enter arguments. You already know how to do this from the previous method; all subsequent actions are no different.

Method 4: Manually entering a function

If you wish, you can avoid interacting with the Function Wizard if you know the arithmetic average formula in Excel. In some situations, manually entering it will speed up the calculation process many times over.

To understand all the nuances, you need to look at the syntax of the formula, it looks like this:

AVERAGE(cell_address(number); cell_address(number))

From the syntax it follows that in the function arguments it is necessary to specify either the address of the range of cells in which the numbers to be calculated are located, or the numbers themselves to be calculated. In practice, using this method looks like this:

AVERAGE(C4:D6,C8:D9)

Method 5: calculation by condition

• select the cell in which the calculation will be performed;
• click the “insert function” button;
• in the wizard window that appears, select the line “averageif” in the list;
• Click OK.

After this, a window for entering function arguments will appear. It is very similar to what was demonstrated earlier, only now there is an additional field - “Condition”. This is where the condition needs to be entered. Thus, by entering “>1500”, only those values ​​that are greater than the specified value will be taken into account.

In order to find the average value in Excel (no matter whether it is a numeric, text, percentage or other value), there are many functions. And each of them has its own characteristics and advantages. Indeed, in this task certain conditions may be set.

For example, the average values ​​of a series of numbers in Excel are calculated using statistical functions. You can also manually enter your own formula. Let's consider various options.

How to find the arithmetic mean of numbers?

To find the arithmetic mean, you need to add up all the numbers in the set and divide the sum by the quantity. For example, a student’s grades in computer science: 3, 4, 3, 5, 5. What is included in the quarter: 4. We found the arithmetic mean using the formula: =(3+4+3+5+5)/5.

How to quickly do this using Excel functions? Let's take for example a series of random numbers in a string:

Or: make the active cell and simply enter the formula manually: =AVERAGE(A1:A8).

Now let's see what else the AVERAGE function can do.

Let's find the arithmetic mean of the first two and three last numbers. Formula: =AVERAGE(A1:B1,F1:H1). Result:



Condition average

The condition for finding the arithmetic mean can be a numerical criterion or a text one. We will use the function: =AVERAGEIF().

Find the arithmetic mean of numbers that are greater than or equal to 10.

Function: =AVERAGEIF(A1:A8,">=10")

The result of using the AVERAGEIF function under the condition ">=10":

The third argument – ​​“Averaging range” – is omitted. First of all, it is not required. Secondly, the range analyzed by the program contains ONLY numeric values. The cells specified in the first argument will be searched according to the condition specified in the second argument.

Attention! The search criterion can be specified in the cell. And make a link to it in the formula.

Let's find the average value of the numbers using the text criterion. For example, the average sales of the product “tables”.

The function will look like this: =AVERAGEIF($A$2:$A$12,A7,$B$2:$B$12). Range – a column with product names. The search criterion is a link to a cell with the word “tables” (you can insert the word “tables” instead of link A7). Averaging range – those cells from which data will be taken to calculate the average value.

As a result of calculating the function, we obtain the following value:

Attention! For a text criterion (condition), the averaging range must be specified.

How to calculate the weighted average price in Excel?

How did we find out the weighted average price?

Formula: =SUMPRODUCT(C2:C12,B2:B12)/SUM(C2:C12).

Using the SUMPRODUCT formula, we find out the total revenue after selling the entire quantity of goods. And the SUM function sums up the quantity of goods. By dividing the total revenue from the sale of goods by the total number of units of goods, we found the weighted average price. This indicator takes into account the “weight” of each price. Its share in the total mass of values.

Standard deviation: formula in Excel

Distinguish between average standard deviation for the general population and for the sample. In the first case, this is the root of the general variance. In the second, from the sample variance.

To calculate this statistical indicator, a dispersion formula is compiled. The root is extracted from it. But in Excel there is a ready-made function for finding the standard deviation.

The standard deviation is tied to the scale of the source data. This is not enough for a figurative representation of the variation of the analyzed range. To obtain the relative level of data scatter, the coefficient of variation is calculated:

standard deviation / arithmetic mean

The formula in Excel looks like this:

STDEV (range of values) / AVERAGE (range of values).

The coefficient of variation is calculated as a percentage. Therefore, we set the percentage format in the cell.

In the process of various calculations and working with data, it is often necessary to calculate their average value. It is calculated by adding the numbers and dividing the total by their number. Let's find out how to calculate the average of a set of numbers using the program Microsoft Excel different ways.

The simplest and known method To find the arithmetic mean of a set of numbers is to use a special button on the Microsoft Excel ribbon. Select a range of numbers located in a column or row of a document. While in the “Home” tab, click on the “AutoSum” button, which is located on the ribbon in the “Editing” tool block. From the drop-down list, select “Average”.

After this, using the “AVERAGE” function, the calculation is made. The arithmetic mean of a given set of numbers is displayed in the cell under the selected column, or to the right of the selected row.

This method is good for its simplicity and convenience. But it also has significant drawbacks. Using this method, you can calculate the average value of only those numbers that are arranged in a row in one column or in one row. But you cannot work with an array of cells, or with scattered cells on a sheet, using this method.

For example, if you select two columns and calculate the arithmetic mean using the method described above, then the answer will be given for each column separately, and not for the entire array of cells.

Calculation using the Function Wizard

For cases when you need to calculate the arithmetic mean of an array of cells, or scattered cells, you can use the Function Wizard. It uses the same “AVERAGE” function, known to us from the first calculation method, but does it in a slightly different way.

Click on the cell where we want the result of calculating the average value to be displayed. Click on the “Insert Function” button, which is located to the left of the formula bar. Or, type the combination Shift+F3 on the keyboard.

The Function Wizard starts. In the list of functions presented, look for “AVERAGE”. Select it and click on the “OK” button.

The arguments window for this function opens. The function arguments are entered into the “Number” fields. These can be either regular numbers or addresses of the cells where these numbers are located. If you are uncomfortable entering cell addresses manually, you should click on the button located to the right of the data entry field.

After this, the function arguments window will be minimized, and you will be able to select the group of cells on the sheet that you take for the calculation. Then, again click on the button to the left of the data entry field to return to the function arguments window.

If you want to calculate the arithmetic mean between numbers located in separate groups of cells, then do the same actions mentioned above in the “Number 2” field. And so on until everything necessary groups no cells will be highlighted.

After that, click on the “OK” button.

The result of calculating the arithmetic mean will be highlighted in the cell that you selected before launching the Function Wizard.

Formula bar

There is a third way to launch the AVERAGE function. To do this, go to the “Formulas” tab. Select the cell in which the result will be displayed. After that, in the “Function Library” tool group on the ribbon, click on the “Other Functions” button. A list appears in which you need to sequentially go through the items “Statistical” and “AVERAGE”.

Then, exactly the same window of function arguments is launched as when using the Function Wizard, the work of which we described in detail above.

Further actions are exactly the same.

Manual function entry

But, do not forget that you can always enter the “AVERAGE” function manually if you wish. It will have the following pattern: “=AVERAGE(cell_range_address(number); cell_range_address(number)).

Of course, this method is not as convenient as the previous ones, and requires the user to keep certain formulas in his head, but it is more flexible.

Calculation of average value by condition

In addition to the usual calculation of the average value, it is possible to calculate the average value by condition. In this case, only those numbers from the selected range that meet a certain condition will be taken into account. For example, if these numbers are greater or less than a specific value.

For these purposes, the “AVERAGEIF” function is used. Like the AVERAGE function, you can launch it through the Function Wizard, from the formula bar, or by manually entering it into a cell. After the function arguments window has opened, you need to enter its parameters. In the “Range” field, enter the range of cells whose values ​​will participate in determining the average arithmetic number. We do this in the same way as with the “AVERAGE” function.

But in the “Condition” field we must indicate a specific value, numbers greater or less than which will participate in the calculation. This can be done using comparison signs. For example, we took the expression “>=15000”. That is, for the calculation, only cells in the range containing numbers greater than or equal to 15000 will be taken. If necessary, instead of a specific number, you can specify the address of the cell in which the corresponding number is located.

The “Averaging range” field is optional. Entering data into it is only required when using cells with text content.

When all the data has been entered, click on the “OK” button.

After this, the result of calculating the arithmetic average for the selected range is displayed in a pre-selected cell, with the exception of cells whose data does not meet the conditions.

As we see, in Microsoft program Excel has a number of tools that can be used to calculate the average of a selected series of numbers. Moreover, there is a function that automatically selects numbers from the range that do not meet a user-defined criterion. This makes calculations in Microsoft Excel even more user-friendly.