Finding the Average Value With Excel's AVERAGE Function GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office

Finding the Average Value With Excel's AVERAGE Function

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

By Ted French Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. lifewire's editorial guidelines Updated on July 16, 2020 Tweet Share Email Tweet Share Email

In This Article

Expand Jump to a Section Syntax and Arguments Example Best Practices AutoAverage Select Ranges Blank Cells vs Zero Cells Turn Zero Cells Off and On Excel has a few functions that calculate the central tendency in a range of data: AVERAGE, MEDIAN, and MODE. The most commonly derived measure of central tendency is the simple average (mean). It is calculated by adding a group of numbers and dividing the result by the count of those numbers. Here's how to find the arithmetic mean using the AVERAGE function in Excel. These instructions apply to Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Mac.

AVERAGE Function Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function name, brackets, comma separators, and arguments. The syntax of the AVERAGE function is: =AVERAGE(Number1,Number2,...Number255) Number1 (required) is the data from which you want the function to find the average. Number2 to Number 255 (optional) is the additional data you want to include in the average calculation. The maximum number of entries allowed is 255. Options for entering the function and its arguments include: Typing the complete function into a worksheet cell.Inputting the function and arguments using the Function dialog box.Entering the function and arguments using the Excel Average Function shortcut.

Excel AVERAGE Function Example

Excel has a shortcut to enter the AVERAGE function, sometimes referred to as AutoAverage due to its association with the AutoSum feature, which is located on the Home tab of the ribbon. The steps below cover how to enter the AVERAGE function, as shown in row four of the example image above, using the shortcut method. Select cell D4, which is where the formula results will display. Go to the Home tab and, in the Editing group, select the AutoSum drop-down arrow. Select Average in the list to enter the AVERAGE function into cell D4. Highlight cells A4 to C4 to enter these references as arguments for the function, then press the Enter key on the keyboard. The number 10 appears in cell D4. This result is the average of the three numbers (4, 20, and 6).

Best Practices for Using the AVERAGE Function

Follow these guidelines when entering data for the AVERAGE function: You can add individual cells as arguments, rather than a continuous range. When using cells as arguments, separate cell references with a comma. Excel ignores blank cells, text entries, and cells containing Boolean values (TRUE or FALSE). If you make changes to the data in the selected cells after entering the function, it automatically recalculates the result to reflect the change.

How to AutoAverage Select Ranges

When using the AutoAverage feature, keep these tips in mind: The default range includes only cells containing numbers. The AVERAGE function should be entered at the bottom of a column of data or the right end of a row of data. It looks first for the number data above and then to the left. Since the AVERAGE function guesses at the range it selects for the Number argument, you should check that it's accurate before pressing the Enter key on the keyboard to complete the function.

Blank Cells vs Zero Cells

When it comes to finding average values in Excel, there's a difference between blank or empty cells and those containing a zero value. Blank cells are ignored by the AVERAGE function, which can be handy since it makes finding the average for non-contiguous cells of data easy.

Turn Zero Cells Off and On

Excel displays a zero in cells with a zero value by default, such as the result of calculations. If this option is off, such cells are left blank but are included in average calculations. Select File > Options to open Excel Options. In the left pane, select Advanced. To hide zero values in cells, go to the Display options for this worksheet section, then clear the Show a zero in cells that have zero value check box. To display zero values in cells, select the Show a zero in cells that have zero value check box.

Turn Off or Turn On Zero Cells in Excel for Mac

Select the Excel menu. Select Preferences in the list to open the Preferences dialog box. Select the View category. To hide zero values in cells, go to the Show in Workbook section, then clear the Zero values check box. To display zero values in cells, select the Zero values check box. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Use the Google Spreadsheets AVERAGE Function Use the Excel RIGHT Function to Extract Characters How to Round Numbers Down in Excel With the ROUNDDOWN Function How to Count Data in Selected Cells With Excel's COUNTIF Function Ignore Error Values When Finding the Average in Excel How to Use Excel's MROUND Function Use Excel's EOMONTH Function to Add or Subtract Months How to Combine the ROUND and SUM Functions in Excel How to Find Variance in Excel How to Use the Excel TRUNC Function Excel SUMIFS: Sum Only Values Meeting Multiple Criteria How to Use the Excel DATE Function How to Use the COUNTIF Function in Excel Round up Numbers in Excel With the ROUNDUP Function How to Use the Round Function in Excel How to Use a Dynamic Range in Excel With COUNTIF and INDIRECT Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies