AVERAGE formula in Excel with examples

The average formula in Excel is used to calculate the arithmetic mean of a set of numbers. Here’s how you can use the AVERAGE function with examples:

Basic Average Formula

To calculate the average of a range of cells, you can use the following formula:

=AVERAGE(A1:A10)

This will calculate the average of the numbers in the cells from A1 to A10.

Average with Criteria

If you want to average numbers that meet certain criteria, you can use the AVERAGEIF or AVERAGEIFS function. For example, to average cells in the range B1:B10 that are greater than 50:

=AVERAGEIF(B1:B10, ">50")

Average Excluding Zeros

To calculate the average of a range but exclude any zero values, you can combine the AVERAGE and IF functions:

=AVERAGE(IF(A1:A10<>0, A1:A10))

Remember to enter this as an array formula by pressing Ctrl+Shift+Enter.

Average with Non-Adjacent Cells

If you need to average non-adjacent cells, you can list them individually in the formula:

=AVERAGE(A1, A3, A5, A7, A9)

Rounding the Average

To round the average to a certain number of decimal places, you can wrap the AVERAGE function with the ROUND function. For example, to round the average to 2 decimal places:

=ROUND(AVERAGE(A1:A10), 2)

These examples should give you a good starting point for using the average formula in Excel. Remember, the AVERAGE function will ignore text, logical values (TRUE/FALSE), and empty cells. However, cells with zero values are included in the calculation unless specifically excluded.