Finding the mean with AVERAGE function in Excel

  1. Use the AVERAGE function to find the mean in a set of data
  2. Syntax and arguments of the function AVERAGE
  3. Excel AVERAGE function example
  4. Best Practices for Using the AVERAGE function
  5. How AutoAverage selects ranges
  6. Empty cells vs. empty cells
  7. Enable/Disable Zero Cells (Windows PC)
  8. Enable/Disable Null Cells (Mac):

Use the AVERAGE function to find the mean in a set of data

Excel has several functions that calculate the central trend in a set of data: AVERAGE , MEDIAN and MODE . The most commonly obtained measure of central tendency is the simple mean (mean) and is calculated by adding a group of numbers and then dividing by the number of those numbers.

Here’s how to find the arithmetic mean using the function AVERAGE .

Remark . These instructions apply to Excel 2019, 2016, 2013, 2010, Excel for Mac, and Excel for Office 365.

Syntax and arguments of the function AVERAGE

Function syntax refers to the layout of the function and includes the function name, parentheses, comma separators, and arguments. Function Syntax AVERAGE :

 = СРЗНАЧ (число1, число2, ... Number255) 
  • Number 1 (required) are the data over which the function must find the mean value.
  • Number from 2 to 255 (optional) is the additional data you want to include in the average calculation. The maximum number of entries allowed is 255.

Input options for a function and its arguments include:

  • Enter a complete function in a worksheet cell

  • Enter function and arguments with Dialog Function

  • Enter functions and arguments with Excel shortcut Average function

Excel AVERAGE function example

Excel has a shortcut to enter a function AVERAGE sometimes called car average because of its association with the more familiar function AutoSum located on House tab on the adhesive tape .

The steps below describe how to enter the function: AVERAGE as shown in the fourth line of the sample image above, using the shortcut mentioned above.

  1. Click cell D4, true the results of the formula are displayed.

  2. Click House tab on tape .

  3. Click down arrow next to the button AutoSum on the adhesive tape to open the drop-down menu.

  4. Select The average in the list to enable the function AVERAGE in a cell D4.

  5. highlight cells A4 C4 to enter these references as arguments to the function, and press the . key Enter on keyboard.

  6. Number 10 should appear in the cell D4; is the average of three numbers ( 4 , twenty and 6) .

Best Practices for Using the AVERAGE function

  • You can add individual cells as arguments instead of a running range, but you must separate the cell reference with a comma.

  • Excel ignores blank cells, text input, and cells with boolean values ​​( TRUE or FALSE ).

  • By default, if you make changes to the data in the selected cells after entering the function, it will be automatically recalculated to reflect the changes.

How AutoAverage selects ranges

  • The default range contains only cells that contain numbers.

  • Function AVERAGE must be entered at the bottom of a column of data or at the right end of a row of data because it searches for numeric data first, then to the left.

  • Since the function AVERAGE , essentially guesses the range it chooses for the Number argument; You should always check the accuracy before pressing any key. Enter on the keyboard to complete the function.

Empty cells vs. empty cells

When it comes to finding averages in Excel, there is a difference between blank or blank cells and cells with a zero value. Empty cells are ignored by the function AVERAGE which can be very useful as it is very easy to find the mean of non-contiguous data cells.

By default, Excel displays zero in zero-valued cells, such as the result of a calculation, but if this option is disabled, such cells remain blank, but are included in the average calculations.

Enable/Disable Zero Cells (Windows PC)

  1. Click on menu File .

  2. Click parameters: in the list to open the dialog box Excel options .

  3. Click on a category Additionally on the left side of the dialog box.

  4. On the right panel, below Display options for this sheet uncheck Show zero in cells with zero value .

  5. Make sure the checkbox is checked to show null values ​​in cells Show zero in cells with zero value .

Enable/Disable Null Cells (Mac):

  1. Click on menu excel .

  2. Click Settings in the list to open the dialog box Settings .

  3. Click on category link Vision .

  4. On the top panel in the section Show in workbook uncheck Null values .

  5. Make sure the checkbox is checked to show null values ​​in cells Null values .

Leave a Reply

Your email address will not be published. Required fields are marked *