Finding the Average Value with Excel's AVERAGE Function

01
of 01

Finding the Arithmetic Mean with Excel's AVERAGE Function

Find the Arithmetic Mean or Average with the Excel Average Function
Find the Arithmetic Mean or Average with the Excel Average Function. © Ted French

Measuring Average or Central Tendency in Excel

Mathematically, there are a number of ways of measuring central tendency or, as it is more commonly called, the average for a set of values. These methods include the arithmetic mean, the median, and the mode.

The most commonly calculated measure of central tendency is the arithmetic mean - or simple average - and it is calculated by adding a group of numbers together and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.

To make it easier to measure central tendency, Excel has a number of functions that will calculate the more commonly used average values. These include:

  • The AVERAGE function - finds the arithmetic mean for a list of numbers
  • The MEDIAN function - finds the median or middle value in a list of numbers
  • The MODE function - finds the mode or most commonly occurring value in a list of numbers

The AVERAGE Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

The syntax for the AVERAGE function is:

= AVERAGE ( Number1, Number2, ... Number255 )

Number1 - (required) the data to be averaged by the function

Number2 to Number 255 - (optional) additional data values to be included in the average. The maximum number of entries allowed is 255.

This argument can contain:

Finding the AVERAGE Function

Options for entering the function and its arguments include:

  1. Typing the complete function, such as =AVERAGE(C1:C7) into a worksheet cell;
  2. Entering the function and arguments using the function's dialog box;
  3. Entering the function and arguments using Excel's Average function shortcut.

AVERAGE Function Shortcut

Excel has a shortcut to entering the AVERAGE function - sometimes referred to as AutoAverage due to its association with the better known AutoSum feature - located on the Home tab of the ribbon.

The icon on the toolbar for these and several other popular functions is the Greek letter Sigma (Σ). By default the AutoSum function is displayed next to the icon.

The Auto part of the name refers to the fact that when entered using this method, the function automatically selects what it believes is the range of cells to be summed by the function.

Finding the Average with AutoAverage

  1. Click on cell C8 - the location where the function results are displayed;
  2. As shown in the image above, only cell C7 should be selected by the function - due to the fact that cell C6 is blank;
  3. Select the correct range for the function C1 to C7;
  4. Press the Enter key on the keyboard to accept the function;
  5. The answer 13.4 should appear in cell C8.

Excel AVERAGE Function Example

The steps below cover how to enter the AVERAGE function shown in row four in the example in the image above using  the shortcut to the AVERAGE function mentioned above.

Entering the AVERAGE Function

  1. Click on cell D4 - the location where the formula results will be displayed;
  2. Click on the Home tab of the ribbon
  3. Click on the down arrow beside the AutoSum button on the ribbon to open the drop down list of functions
  4. Click on the word Average in the list to enter the AVERAGE function into cell D4
  5. Click on the Functions icon on toolbar above the  to open the drop down list of functions;
  6. Select Average from the list to place a blank copy of the function in cell D4;
  7. By default, the function selects the numbers in the cell D4;
  8. Change this by highlighting cells A4 to C4 to enter these references as arguments for the function and press the Enter key on the keyboard;
  9. The number 10 should appear in cell D4. This is the average of the three numbers - 4, 20, and 6;
  10. When you click on cell A8 the complete function =AVERAGE(A4:C4) appears in the formula bar above the worksheet.

Notes:

  • Individual cells, rather than a continuous range can be added as arguments but each cell reference must be separated by a comma.
  • Text entries and cells containing Boolean values (TRUE or FALSE) and cells that are blank are ignored by the function as shown in rows 6, 8 and 9 above.

  • After entering the function, if changes are made to the data in the selected cells, the function, by default, automatically recalculates to reflect the change.

How AutoAverage Selects the Argument Range

  • The default range includes only cells containing numbers, the range of selected numbers is interrupted by a cell containing text or a blank cell.
  • The AVERAGE function is designed to be entered at the bottom of a column of data or at the right end of a row of data. It looks first for number data above and then to the left.
  • Since the AVERAGE function is, in effect, guessing at the range it selects for the Number argument, this selection should always be checked for correctness before pressing the Enter key on the keyboard to complete the function.

Blank Cells vs. Zero

When it comes to finding average values in Excel, there is a difference between blank or empty cells and those containing a zero value.

Blank cells are ignored by the AVERAGE function, which can be very handy since it makes finding the average for non-contiguous cells of data very easy as shown in row 6 above.

Cells containing a zero value, however, are included in the average as shown in row 7.

Displaying Zeros

By default, Excel displays a zero in cells with a zero value - such as the result of calculations, but if this option is turned off, such cells are left blank, but still included in average calculations.

To turn this option off:

  1. Click on the File tab of the ribbon to display the file menu options;
  2. Click Options in the list to open the Excel Options dialog box, and then
  3. Click on the Advanced category in the left hand pane of the dialog box to see the available options;
  4. In the right hand pane, in the Display options for this worksheet section clear the check box for Show a zero in cells that have zero value check box;
  5. To display zero (0) values in cells ensure that the Show a zero in cells that have zero value check box is selected.