In mathematics (and statistics), the mean is some number between the smallest and largest of the values ​​in some set of numbers. There are three common averages: arithmetic mean, median, and mode. In Microsoft Excel, you can calculate all three values, as well as find the weighted average, which is used to calculate the average price.

Steps

Average

Enter the numbers whose arithmetic mean you want to find. For example, consider a set of ten numbers.

• In most cases, numbers are entered in columns (as in our example), so enter numbers in cells A1 - A10.
• Input numbers: 2, 3, 5, 5, 7, 7, 7, 9, 16, and 19.
• If you want, find the sum of the numbers by entering the formula "=SUM(A1:A10)" in cell A11 (without quotes).

1. You can find the arithmetic mean using the AVERAGE function.

   • Click on an empty cell, for example, A12, and then enter "=AVERAGE(A1:10)" (without quotes).
   • Click on an empty cell and then click on the "fx" button (in the formula bar directly above the Excel worksheet). In the window that opens, in the "Select a function" list, find and highlight "AVERAGE", and then click OK. Enter the range "A1:A10" in the "Number 1" line of the window that opens and click OK.
   • Type an equal sign (=) in the formula bar. To the left of the formula bar, in the "Functions" drop-down list, find and select "AVERAGE". Enter the range "A1:A10" in the "Number 1" line of the window that opens and click OK.

2. The cell where you entered the formula will display the arithmetic mean, equal to the ratio of the sum of numbers in the specified range of cells (80) to the total number of numbers in the range (10): 80/10 = 8.

   • To check the correctness of the answer in any empty cell, enter the formula "=A11/10".
   • The arithmetic mean is good for centering a distribution when the individual numbers in a set of numbers are not very different from each other.

   Median

   1. Enter the numbers whose median you want to find.

      You can find the arithmetic mean using the MEDIAN function. You can enter the formula in one of three ways:

      • Click on an empty cell, for example, A13, and then enter "=MEDIAN(A1:10)" (without quotes).
      • Click on an empty cell and then click on the "fx" button (in the formula bar directly above the Excel worksheet). In the window that opens, in the "Select a function" list, find and highlight "MEDIAN", and then click OK. Enter the range "A1:A10" in the "Number 1" line of the window that opens and click OK.
      • Type an equal sign (=) in the formula bar. To the left of the formula bar, in the "Functions" drop-down list, find and select "MEDIAN". Enter the range "A1:A10" in the "Number 1" line of the window that opens and click OK.

   2. The cell where you entered the formula will display the median value, in which half of the numbers in some set of numbers have higher values ​​than the median, and the other half have lower values ​​than the median (in our example, the median is 7). The median may or may not be equal to one of the numbers in some set of numbers.

   Fashion

   Enter the numbers whose mode you want to find. For example, consider a set of ten numbers (2, 3, 5, 5, 7, 7, 7, 9, 16, and 19). Enter these numbers in cells A1 - A10.

   In different versions of Excel, the mode finding function is different.

   1. You can enter the formula in one of three ways:

      • Click on an empty cell, for example, A14, and then enter "=MODA(A1:10)" or "=MODA.ONE(A1:10)" (without quotes).
      • Click on an empty cell and then click on the "fx" button (in the formula bar directly above the Excel worksheet). In the window that opens, in the "Select a function" list, find and highlight "FASHION" or "FASHION.ONE", and then press OK. Enter the range "A1:A10" in the "Number 1" line of the window that opens and click OK.
      • Type an equal sign (=) in the formula bar. To the left of the formula bar, in the "Functions" drop-down list, find and select "MEDIAN" or "MOD.SINGLE". Enter the range "A1:A10" in the "Number 1" line of the window that opens and click OK.

   2. In the cell in which you entered the formula, the value of the mode will be displayed, equal to the number that occurs most often in a certain set of numbers. In our example, the mode is 7, since the number 7 occurs three times in our set of numbers.

      • If two numbers occur the same number of times in a range of numbers, the FASHION or FASHION.ONE function will display the first number encountered. In our example, if you change 3 to 5, then the mod will be 5, not 7, because 5 occurs first.

   weighted average

   1. Enter the numbers whose weighted average you want to find. Two columns with numbers are required here. For example, consider several tonic shipments.

      • In our example, we will enter the column headings. Enter "Unit Price" in cell A1, and "Number of Units Shipped" in cell B1.
      • The first batch consisted of 10 units at $20 per unit. Enter "$20" in cell A2 and "10" in cell B2.
      • Demand for the tonic increased, so the second batch consisted of 40 units at $30 each. Enter "$30" in cell A3 and "40" in cell B3.
      • As the price went up, demand for the tonic dropped and the third batch consisted of 20 units at $25 each. Enter "$25" in cell A4 and "20" in cell B4.

   2. To calculate the weighted average in Excel, you need to use two functions:

      • SUMPRODUCT The SUMPRODUCT function multiplies the numbers in one row and adds the products of the numbers in all rows. You specify the range of each column; in our example: "SUMPRODUCT=(A2:A4,B2:B4)". As a result, you will get the total cost of the entire shipped tonic.
      • SUM. The SUM function adds numbers in a single row or column. Since you want to find the average unit price of tonic shipped, you need to find the total amount of tonic shipped. In our example: "=SUM(B2:B4)".
      Since the average is determined by the ratio of the sum of all numbers to the number of numbers, you can combine these two functions into one formula: "SUMPRODUCT=(A2:A4,B2:B4)/SUM(B2:B4)".

   3. The cell where you entered the formula will display the value of the weighted average. In our example, this is the average price of a tonic unit, equal to the ratio of the total cost of the shipped tonic to the total quantity of the tonic.

      • Total cost of the shipped tonic: 20 x 10 + 30 x 40 + 25 x 20 = 200 + 1200 + 500 = $1900.
      • Total tonic shipped: 10 + 40 + 20 = 70.
      • Average price: 1900/70 = $27.14.
   • You don't have to enter all the numbers in a row (in a column or row), but you do need to make sure Excel understands which numbers you want to include and exclude. In our example, if you want to find the average of the first five numbers, enter the formula like this: "=AVERAGE(A1:A5,A10)".

The Excel program is multi-faceted, so there are several options that will allow you to find the average:

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

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

Third option. If you select the required range, then 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 value, you just need to choose the best one for you and use it constantly.

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

The mean value is the value that is the arithmetic mean, i.e. is calculated by adding a set of numbers and then dividing the total sum of numbers by their number. For example, for the numbers 2, 3, 6, 7, 2 it 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 in brackets indicate the range of numbers in the cells, highlighting the column with the data. After that, press ENTER, or simply left-click on any cell. The result will be displayed in the cell below the column. On the face of it, the description is incomprehensible, but in fact it is a matter of minutes.

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



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

This is very easy if the data is already entered into the cells. If you are just interested in 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 in the status bar at the bottom right.

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

Finally, you can use the formulas directly - click Insert Function next to the formula bar and cell address. The AVERAGE function is in the Statisticalquot ; category, and takes as arguments both numbers and cell references, etc. There you can also choose more complex options, for example, AVERAGEIF - calculation of the average by condition.

As easy as pie. In order to find the average value in excel, you only need 3 cells. In the first we write one number, in the second - another. And in the third cell, we will score 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 like this:

This formula calculates the arithmetic mean of two numbers.

For the beauty of our calculations, we can highlight the cells with lines, in the form of a plate.

There is also a function in Excel itself to determine 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 give a lot of advice, but with each new advice you will have a new question, it can and well, on the one hand, it will be an incentive to improve your level on this site, so I will not give you a bunch of advice, but I will give a link to YouTube a channel with a course on mastering such a necessary application as Excel, your right to use it or not, you will have a link to a detailed course where you will always find the answer to your question on Excel

circle the values ​​\u200b\u200bthat will be involved in the calculation, click the Formulasquot ; tab, you will see on the left there is and next to it is a downward-pointing triangle. click on this triangle and select Averagequot ;. Voila, done) at the bottom of the column you will see the average value :)

Let's assume that you need to find the average number of days for tasks to be completed by different employees. Or you want to calculate a time interval of 10 years Average temperature on a particular day. Calculating the average value of a series of numbers in several ways.

The mean is a function of the measure of central tendency, which is the center of a series of numbers in a statistical distribution. The three most common criteria for the central trend are.

The average The arithmetic mean is calculated by adding a series of numbers and then dividing the number of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 has 30 divided by 6, 5;

Median The middle number of a series of numbers. Half of the numbers have values ​​that are greater than the Median, and half of the numbers have values ​​that are less than the Median. For example, the median of 2, 3, 3, 5, 7 and 10 is 4.

Mode The most frequently occurring number in a group of numbers. For example mode 2, 3, 3, 5, 7 and 10 - 3.

These three measures of the central tendency of the symmetrical distribution of a series of numbers are one and the same. In an asymmetric distribution of a number of numbers, they can be different.

Calculate the average value of cells located continuously in one row or one column

Do the following.

Calculating the Average of Scattered Cells

To accomplish this task, use the function AVERAGE. Copy the table below onto a blank sheet.

Calculating the weighted average

SUMPRODUCT and amounts. The vThis example calculates the average unit price paid across three purchases, where each purchase is for a different number of units of measure at different unit prices.

Copy the table below onto a blank sheet.

Calculating the average value of numbers, ignoring zero values

To accomplish this task, use the functions AVERAGE and if. Copy the table below and keep in mind that in this example, to make it easier to understand, copy it onto a blank sheet.

Excel has made calculating the average of multiple cells a very easy task - just use the function AVERAGE(AVERAGE). But what if some values ​​carry more weight than others? For example, in many courses, tests carry more weight than assignments. For such cases, it is necessary to calculate weighted average.

Excel does not have a function for calculating the weighted average, but there is a function that will do most of the work for you: SUMPRODUCT(SUM PRODUCT). And even if you've never used this feature before, by the end of this article you'll be using it like a pro. The method we use works in any version of Excel as well as other spreadsheets such as Google Sheets.

We prepare the table

If you are going to calculate a weighted average, you will need at least two columns. The first column (column B in our example) contains the scores for each assignment or test. The second column (column C) contains the weights. More weight means more influence of the task or test on the final grade.

To understand what weight is, you can think of it as a percentage of your final grade. In fact, this is not the case, since in this case the weights should add up to 100%. The formula that we will analyze in this lesson will calculate everything correctly and does not depend on the amount that the weights add up to.

We enter the formula

Now that our table is ready, we add the formula to the cell B10(any empty cell will do). As with any other formula in Excel, we start with an equal sign (=).

The first part of our formula is the function SUMPRODUCT(SUM PRODUCT). Arguments must be enclosed in brackets, so we open them:

SUMPRODUCT(
=SUMPRODUCT(

Next, add the function arguments. SUMPRODUCT(SUMPRODUCT) can have multiple arguments, but usually two are used. In our example, the first argument will be a range of cells. B2:B9 A that contains the scores.

SUMPRODUCT(B2:B9
=SUMPRODUCT(B2:B9

The second argument will be a range of cells C2:C9, which contains the weights. These arguments must be separated by a semicolon (comma). When everything is ready, close the brackets:

SUMPRODUCT(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)

Now let's add the second part of our formula, which will divide the result calculated by the function SUMPRODUCT(SUMPRODUCT) by the sum of the weights. We will discuss later why this is important.

To perform the division operation, we continue the already entered formula with the symbol / (straight slash), and then write the function SUM(SUM):

SUMPRODUCT(B2:B9;C2:C9)/SUM(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(

For function SUM(SUM) we will specify only one argument - a range of cells C2:C9. Don't forget to close the parentheses after entering the argument:

SUMPRODUCT(B2:B9;C2:C9)/SUM(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)

Ready! After pressing the key Enter, Excel will calculate the weighted average. In our example, the final result will be 83,6 .

How it works

Let's break down each part of the formula, starting with the function SUMPRODUCT(SUMPRODUCT) to understand how it works. Function SUMPRODUCT(SUMPRODUCT) calculates the product of each item's score and its weight, and then sums all the resulting products. In other words, the function finds the sum of the products, hence the name. So for Tasks 1 multiply 85 by 5, and for Testa multiply 83 by 25.

If you're wondering why we need to multiply the values ​​in the first part, imagine that the greater the weight of the task, the more times we have to consider the grade for it. For example, Task 2 counted 5 times and Final exam- 45 times. That's why Final exam has a greater impact on the final grade.

For comparison, when calculating the usual arithmetic mean, each value is taken into account only once, that is, all values ​​have equal weight.

If you could look under the hood of a function SUMPRODUCT(SUMPRODUCT), we saw that in fact she believes this:

=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)

Luckily, we don't need to write such a long formula because SUMPRODUCT(SUMPRODUCT) does all this automatically.

The function itself SUMPRODUCT(SUMPRODUCT) returns us a huge number − 10450 . At this point, the second part of the formula comes into play: /SUM(C2:C9) or /SUM(C2:C9), which returns the result to the normal range of scores, giving the answer 83,6 .

The second part of the formula is very important because allows you to automatically correct calculations. Remember that weights don't have to add up to 100%? All this thanks to the second part of the formula. For example, if we increase one or more weight values, the second part of the formula will simply divide by the larger value, again resulting in the correct answer. Or we can make the weights much smaller, for example by specifying values ​​like 0,5 , 2,5 , 3 or 4,5 , and the formula will still work correctly. It's great, right?

Good afternoon, dear theorists and practitioners of statistical data analysis.

In this article, we will continue the once started conversation about averages. This time we will move from theory to practical calculations. The topic is vast even theoretically. If you add practical nuances, it becomes even more interesting. Let me remind you that some questions about averages are discussed in the articles on the essence of the average, its main purpose and the weighted average. The properties of the indicator and its behavior depending on the initial data were also considered: a small sample and the presence of anomalous values.

These articles should generally give a good idea of ​​the calculation rules and the correct use of averages. But now in the yard of the 21st (twenty-first) century and manually counting is quite rare, which, unfortunately, is not reflected in the better on the mental abilities of citizens. Even calculators are out of fashion (including programmable and engineering ones), especially abacus and slide rules. In short, all kinds of statistical calculations are now being done in a program such as an Excel spreadsheet processor. I already wrote something about Excel, but then I temporarily abandoned it. For the time being, I decided to pay more attention to theoretical issues of data analysis, so that when describing calculations, for example, in Excel, one could refer to basic knowledge of statistics. In general, today we will talk about how to calculate the average in Excel. I will only clarify that we are talking about the arithmetic mean (yes, there are other averages, but they are used much less frequently).

The arithmetic mean is one of the most commonly used statistics. An analyst simply needs to be able to use Excel to calculate it, as well as to calculate other indicators. And in general, an analyst without a virtuoso command of Excel is an impostor, not an analyst.

An inquisitive reader may ask: what is there to count? - I wrote the formula and everything. It is, of course, so, Excel calculates according to the formula, but the form of the formula and the result are highly dependent on the source data. And the initial data can be very different, including dynamic, that is, changeable. Therefore, adjusting one formula so that it fits all occasions is not such a trivial matter.

Let's start with a simple one, then move on to more complex and, accordingly, more interesting. The simplest thing is if you need to draw a table with data, and at the bottom, in the final line, show the average value. To do this, if you are a “blonde”, you can use the summation of individual cells with the help of a plus sign (previously taking it in brackets) and then dividing by the number of these cells. If you are a "brunette", then instead of separately designated cells through the "+" sign, you can use the summation formula SUM () and then divide by the number of values. However, more advanced Excel users know that there is a ready-made formula - AVERAGE(). In parentheses, the range of initial data is indicated, according to which the average value is calculated, which is convenient to do with the mouse (computer).

Formula AVERAGE

The Excel statistical function AVERAGE is used quite often. It looks like this.

This formula has a wonderful property that gives it value and compares favorably with manual summation by dividing by the number of values. If in the range by which the formula is calculated, there are empty cells (not zero, but empty), then this value is ignored and excluded from the calculation. Thus, if there is no data for some observations, the average value will not be underestimated (when summing up, an empty cell is perceived by Excel as zero). This fact makes the AVERAGE formula a valuable tool in the analyst's arsenal.

There are different ways to get to the formula. First you need to select the cell in which the formula will be placed. The formula itself can be written manually in the formula bar or take advantage of its presence on the taskbar - the "Home" tab, at the top right there is an expandable button with the autosum icon Σ:

After calling the formula in brackets, you will need to enter the data range for which the average value will be calculated. You can do this with the mouse by pressing the left key and dragging it over the desired range. If the data range is not continuous, then by holding down the Ctrl key on the keyboard, you can select the desired places. Next, press "Enter". This method is very convenient and often used.

There is also a standard way of calling for all functions. Need to press the button fx at the beginning of the line where functions (formulas) are written and thereby call the Function Wizard. Then, either using the search, or simply select the AVERAGE function from the list (you can pre-sort the entire list of functions by the “statistical” category).

After selecting the function, press "Enter" or "OK" and then select the range or ranges. Press “Enter” or “OK” again. The result of the calculation will be reflected in the cell with the formula. Everything is simple.

Calculation of the arithmetic weighted average in Excel

(module 111)

As you might guess, the AVERAGE formula can only calculate the simple arithmetic average, that is, it adds up everything and divides by the number of terms (minus the number of empty cells). However, often you have to deal with the arithmetic weighted average. There is no ready-made formula in Excel, at least I did not find it. Therefore, several formulas will have to be used here. There is no need to be scared, it is not much more difficult than using AVERAGE, except that you need to make a couple of extra moves.

Let me remind you that the arithmetic weighted average formula assumes in the numerator the sum of the products of the values ​​of the analyzed indicator and the corresponding weights. There are different ways to get the right amount. Often, an intermediate calculation is made in a separate column, in which the product of each value and its corresponding weight is calculated. Then the sum of these works is knocked out. It turns out the numerator of the weighted average formula. Then all this is divided by the sum of the weights, in the same or a separate cell. Looks like this.

In general, the developers of Excel obviously did not finalize this moment. We have to dodge and calculate the weighted average in the "semi-automatic" mode. However, it is possible to reduce the number of calculations. To do this, there is a wonderful function SUMPRODUCT. With this function, you can avoid the intermediate calculation in the adjacent column and calculate the numerator in one function. You can divide by the sum of the weights in the same cell by adding the formula manually, or in the next one.

As you can see, there are several options. In general, the same tasks in Excel can be solved in different ways, which makes the spreadsheet processor very flexible and practical.

Calculation of the arithmetic mean by condition

When calculating the average value, situations may arise when not all values ​​need to be included in the calculation, but only the necessary ones that meet certain conditions (for example, goods for certain product groups). There is a formula for this AVERAGEIF.

It happens that the average value needs to be calculated from the filtered values. There is also such an opportunity - the INTERMEDIATE RESULTS function. In the formula selection parameter, you should put 1 (and not 9, as in the case of summation).

Excel offers quite a lot of possibilities for calculating the average value. I just described the main and most popular methods. It is impossible to make out all the existing options, there are millions of them. However, what is described above occurs in 90% of cases and is quite enough for successful application. The main thing here is to clearly understand what is being done and why. Excel does not analyze, but only helps to quickly make a calculation. Behind any formulas there should be a cold calculation and a sober understanding of the analysis being carried out.

Here, perhaps, is all you need to know about calculating the arithmetic mean in Excel in the first place.

Below is a video about the AVERAGEIF function and calculating the arithmetic weighted average in Excel