Home > Formulas > Statistical > Average Excel Average FunctionsExamples show how to average Excel cells with numbers, text, blanks, or cells based on specific criteria. Use the AVERAGE, AVERAGEA, AVERAGEIF or AVERAGEIFS functions, or TRIMMEAN, for an average that excludes outliers. |
Average Cells with Numbers -- AVERAGEThe Microsoft Excel AVERAGE function will calculate the average (arithmetic mean) for a specified set of numbers in an Excel spreadsheet. To calculate the average, Excel divides the sum of all numbers by the count of all numbers. AVERAGE is one of the central tendency functions, like MEDIAN and MODE.
AVERAGE SyntaxThe AVERAGE function syntax has the following arguments:
The arguments (e.g. number1) can be cell references, or cell ranges, or numbers typed into the AVERAGE formula. In the list of arguments, you can include up to 255 arguments, with numbers to include in the average AVERAGE Function NotesHere are a few things to keep in mind when using the AVERAGE function:
AVERAGE Function ExampleThe following AVERAGE function example uses one argument -- a reference to cells A1:A5.
How the Formula WorksIn cell A7, the formula result is 21.8333333, which is the average of the cells that contain numbers, in the reference range -- A1 to A3.
In column C, there are 3 formulas, to verify the result of the AVERAGE formula.
The result in cell C7 matches the result of the AVERAGE formula in cell A7 Format AVERAGE FormulaFrequently, the result of an AVERAGE formula is a number with many decimal places. To hide some of those decimals, you can format the cell with Number Format.
The Number format is applied, with its default setting of 2 decimal places. The value in cell A7 does not change -- only its formatting is affected. Average Cells with Data -- AVERAGEAThe AVERAGEA function will average cells that are not empty. Its
syntax is:
Note: AVERAGEA will include cells with formulas — including those that look empty, because they evaluate to "", e.g. =IF(B2="","",B2). |
Average cells that match criteria -- AVERAGEIFThis short video show how to create a flexible AVERAGEIF formula, to calculate the average quantity sold, for the selected product name. There are written steps below the video, and the sample file is in the Download section Match criterion exactlyIn Excel, calculate average of the numbers for cells that meet a specific criterion. In this example only the quantities for the Pen orders will be averaged.
Match criterion in a stringIn Excel, average cells in rows that contain a criterion as part of the cell's contents. In this example all Pen, Gel Pen, and Pencil orders will be averaged, because they contain the string "pen".
Note: Instead of typing the criterion in a formula, you can
refer to a cell. For example, the formula in step 7 above could be
changed to: Criterion and operatorYou can use an operator with a criterion. In this example only the rows where the quantity is greater than or equal to ten will be averaged.
Note: Instead of typing the criterion in a formula, you can
refer to a cell. For example, the formula in step 8 above could be
changed to: |
Average Rows in a Filtered List -- SUBTOTALAfter you filter the rows in a list, you can use the SUBTOTAL function to average the visible rows.
Note: In the Subtotal function, you can use 101, instead
of 1: Video: Trimmed Mean With TRIMMEAN FunctionTo exclude a specific percentage of outlying data from an average, you can use Excel's TRIMMEAN function. Watch this video to see how to set up the formula, and the written instructions are below the video. Trimmed Mean With TRIMMEAN FunctionTo exclude outlying data from an average, you can use the TRIMMEAN function. This function excludes a specific percentage of data points from the top and bottom of the data set, then returns the average (mean) of the remaining data points. NOTE: In this example, the quantities are sorted in ascending order, so it is easier to see the top and bottom numbers. Numbers do NOT need to be sorted, for the TRIMMEAN function to calculate correctly. TRIMMEAN FunctionThe TRIMMEAN function requires 2 arguments: TRIMMEAN(array, percent) In this example,
To calculate the trimmed mean, enter this formula in cell E4:: =TRIMMEAN(B2:B21, E3) The TRIMMEAN result (53.06) is different from the AVERAGE (51.95), which is shown in cell E5. How TRIMMEAN WorksIn this example, there are 20 values, and the trim percent is 25%. To calculate how many number to trim,
So, in this excample, the top 2 (86,97) and bottom 2 (3,4) data points will not be included in the average that TRIMMEAN calculates. The AVERAGE function, used on cells B4:B19 in this example, returns the same result as the TRIMMEAN function, with a trim percentage of 25%. |
Get the WorkbookTo see the formulas and test data used in this example, you can download the Average functions sample file. The file is in xlsx format and does not contain any macros Excel Function Tutorials |
Last updated: February 18, 2024 1:44 PM