Excel: Average if function - here's how

In Microsoft Excel, you can use formulas and functions to perform all sorts of calculations. For example, if you want to determine the average value, it works with the mean-if function. Find out how you can use the function in Excel and what you need to bear in mind.

Use mean-range function - how to do it

Using the mean-range function, you can calculate the average value for all cells in a certain range that match certain criteria.

• In a free cell, enter the formula "=AVERAGE_VALUEWENN(range, criteria, [average_range])".
• At the "range" item in the formula, specify the cells for which you want Excel to calculate the average value.
• Using the argument "Criteria" you define the factors that Excel should consider in the calculation.
• By means of the optional argument "Mean_range" you can define the actual range that Excel considers for the calculation. If the argument is missing, Excel uses the cell range defined in the argument "Range".

This is what you should pay attention to with the function

If you use the mean_range function in your Excel document, there are a few little things to keep in mind when doing so.

• For example, cells that you have defined via the argument "Range", which contain "TRUE" or "FALSE" as content, will be ignored when calculating the mean value.
• If none of the cells you defined in the "Range" argument matches the specified criteria, the error value "#DIV/0!" will be output as the result.
• You will also receive this error message if the defined cell range is empty or contains text values.

You might also be interested in:

Excel: Let calculate standard deviationExcel calculates incorrectly: Help the program print