AVERAGE()

This article is also available in: Bahasa Malaysia Bahasa Indonesia

AVERAGE() is used to calculate the average of a range of numbers.

=AVERAGE(number1, number2, ...)

Number1, Number2, etc. are the numbers you want to average.

AVERAGE(1,2,3,4,5) would give the answer 3, the total of the numbers, which equals 15, divided by how many numbers there are, which is 5.

The maximum set of numbers that you can enter into the AVERAGE() function is 255.

If you enter more than 255 numbers, Excel will return an error message.

AVERAGE() ignores blank cells, text, or logical values (true, false, yes, no) in the calculation.

For example, in the range that you select, you could have AVERAGE(1, 2, 3, Apple, 5) and this will return 2.75, ignoring the cell with text in it.

However, cells with zero will be counted in arriving at the average.

For example, if we did AVERAGE(1, 2, 3, 0), the result will be 1.5 as the total, 6 (1+2+3+0), will be divided by 4 even though there is a zero.

Instead of typing in the numbers, you can use references to cells or a range of cells.

For example, if we had daily sales as shown in column F in the image below, and we want to know the average sales.

In an empty cell, start the formula by typing in =AVERAGE(.

Then select the cells in column F from F2 to F20, and press enter to complete the formula.

Excel calculates the average of the daily sales and displays the result.

The average daily sales in our example here is 731.79.

Average of a range of numbers

You are not limited to selecting one column, you can select a range of columns.

You can either do it as

=AVERAGE(E2:F20)

or, if the columns are not next to each other, as individual ranges separated by a comma

 =AVERAGE(E2:E20,F2:F20)

Average of selected cells

It’s also possible to select individual cells, each separated by a comma, as shown below.

Here we want the average of sales for one product, QuantumGlow LED Lamp, so we have selected all cells with sales amounts related to that product.

=AVERAGE(F6,F16,F19)

This article is also available in: Bahasa Malaysia Bahasa Indonesia