Calculation of the average value in Microsoft Excel

In the process of various calculations and work 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 Microsoft Excel in various ways.

The easiest and most well-known way to find the arithmetic mean of a set of numbers is to use the special button on the Microsoft Excel ribbon. We select a range of numbers located in a column or line of a document. Being in the "Home" tab, click on the "Autosum" button, which is located on the ribbon in the "Editing" tool block. Select "Average" from the drop-down list.

After that, using the "AVERAGE" function, the calculation is made. In the cell under the selected column, or to the right of the selected row, the arithmetic mean of the given set of numbers is displayed.

This method is good for 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, with an array of cells, or with scattered cells on a sheet, you cannot work using this method.

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

Calculation with the Function Wizard

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

We 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, we type the combination Shift + F3 on the keyboard.

The Function Wizard starts. In the list of functions presented, we are looking for "AVERAGE". Select it and click on the "OK" button.

The arguments window for this function opens. Function arguments are entered into the "Number" fields. These can be both ordinary numbers and cell addresses where these numbers are located. If it is inconvenient for you to enter cell addresses manually, then you should click on the button located to the right of the data entry field.

After that, the function arguments window will collapse, and you can select the group of cells on the sheet that you take for 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 the numbers in disparate groups of cells, then do the same steps as mentioned above in the "Number 2" field. And so on until all the desired groups of cells are selected.

After that, click on the "OK" button.

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

Formula bar

There is a third way to run 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 group of tools "Library of functions" on the ribbon, click on the button "Other functions". A list appears in which you need to sequentially go through the items "Statistical" and "AVERAGE".

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

The next steps 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 certain formulas to be kept in the user's head, but it is more flexible.

Calculation of the 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 run 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 be used to determine the arithmetic mean. We do this in the same way as with the AVERAGE function.

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

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

When all the data is entered, click on the "OK" button.

After that, the result of the calculation of the arithmetic average for the selected range is displayed in the pre-selected cell, with the exception of cells whose data do not meet the conditions.

As you can see, in Microsoft Excel there are a number of tools with which you can calculate the average value of a selected series of numbers. Moreover, there is a function that automatically selects numbers from a range that do not meet a user-defined criteria. This makes calculations in Microsoft Excel even more user-friendly.