How to use AVERAGEIF in MS Excel

The AVERAGEIF inn a spreadsheet application or in Excel is used to calculate the average of a range of cells that meet a certain criteria.

Here’s how to use it:

1. Identify the range of cells you want to calculate the average for. For example, A1:A10.
2. Identify the criteria you want to apply. For example, cells that contain the value “Yes”.
3. Enter the AVERAGEIF formula in a cell. The basic syntax is: =AVERAGEIF(range, criteria, [average_range])
4. For the example above, the formula would be: =AVERAGEIF(A1:A10, “Yes”, B1:B10)
5. Press Enter to get the result, which in this case would be the average of the values in range B1:B10, but only for the rows where the corresponding cell in range A1:A10 contains the value “Yes”.

If you don’t specify an average_range, the AVERAGEIF function will use the same range as the range argument for the calculation.

You can also use wildcards in your criteria. For example, =AVERAGEIF(A1:A10, “?es”, B1:B10) would calculate the average of the values in range B1:B10, but only for the rows where the corresponding cell in range A1:A10 contains any three-letter word ending in “es”.

The AVERAGEIFS function can be used when you want to apply multiple criteria to different ranges. The basic syntax is: =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)

For example, =AVERAGEIFS(B1:B10, A1:A10, “Yes”, C1:C10, “>20”) would calculate the average of the values in range B1:B10, but only for the rows where the corresponding cell in range A1:A10 contains the value “Yes” and the corresponding cell in range C1:C10 contains a value greater than 20.

About Author

SURF

See author's posts