Use the AVERAGE function

The AVERAGE function calculates the average. It can include up to 255 arguments. An argument can be a number, a name, an array, a formula, a logical value, a function, a reference, or a text representation of a number.

Syntax:                

AVERAGE(argument1, argument2, ...)

When you use the AVERAGE function, Excel calculates the SUM and then divides by the COUNT. For example, the formula =AVERAGE(3,7,2) adds 3 plus 7 plus 2 , divides the result, 12, by 3, and returns 4.

TRUE and FALSE are logical values. In a cell, Excel considers a number preceded by an apostrophe text. In a formula, Excel considers a number enclosed in double quotes text.

When using the AVERAGE function, how Excel treats a data type may depend on whether the data type is located in a cell, is located in an array, or is an argument. If a cell contains a logical value, text, or an error value Excel ignores the cell. If an array contains a logical value, text, or an error value, Excel ignores the logical value, text, or error value. If an argument is a logical value or text, Excel includes it in the calculation. TRUE becomes 1, FALSE becomes 0, and text causes an error if Excel cannot convert the text to a number. For example, = AVERAGE(TRUE, FALSE, TRUE,FALSE) returns .50, =AVERAGE("One", "1") returns the error #VALUE!, and =AVERAGE("1", "1") returns 1.

In the following worksheet, all of the cells that have orange text contain a calculation. All of the calculations use the AVERAGE function. Feel free to modify the worksheet and to create your own functions.

The Advanced Examples Tab Explained

Example 1

Excel evaluates cells A1:A4: 3, TRUE, 2, and 1. It ignores cell A2, TRUE, because it is a logical value. It adds cells A1, A3, and A4: 3, 2, 1, gets 6, and then divides by 3, and returns 2.

Example 2

This example has two arguments: 1, 1. Excel adds them together, gets 2; then divides by 2 and returns 1.

Example 3

Cells B2:B6 are named SampleData. Excel adds cells B2, B4, and B6: 3, 2, 4, and then divides by 3, and returns 3. It ignores cell B3, because Excel considers a number preceded by an apostrophe text. It ignores cell B5, because it is text.

Example 4

Example 4 contains an array. Excel multiplies 2 times 5, gets 10; 3 times 5, gets 15; and 4 times 5, gets 20, adds those results together, divides by 3 and returns 15.

Example 5

Example 5 combines the arguments in examples 1, 2, 3, and 4 into a single function and returns the result 5.636363636.

Example 6

If a logical value is an argument, Excel recognizes it. Therefore, the AVERAGE of 1 and TRUE is 1.

Example 7

Excel cannot convert "two" to a number, so the function returns the error #VALUE.

Example 8

The argument "4" is text, but Excel can convert it to the number 4. When text can be converted to a number and is used as an argument, Excel recognizes it. The function adds 7, 4, 2, and 3, gets 16 then divides by 4 and returns 4.

Tip

You can also use AutoSum to access the AVERAGE function. Click the down-arrow associated with the AutoSum button on the Home tab. Then click Average. When you access the AVERAGE function through AutoSum, Excel provides you with extra features that enable you to calculate an average quickly. For example, you can quickly find the average of the numbers in each column or each row in a set of columns or rows. AutoSum AVERAGE has the same features as AutoSum SUM does.   Click here to find out more.