To calculate the arithmetic mean in Excel, enter =AVERAGE(B2:B20), replacing the range with the cells containing your numbers. Excel ignores blank cells and text in a referenced range but includes zeros, so the most useful AVERAGE tips are knowing which cells count, choosing a criteria-based function when needed, and checking that the result matches the question you intend to answer.
What Excel’s AVERAGE function calculates
AVERAGE adds the numeric values in its arguments and divides their sum by the number of numeric values. It calculates the ordinary arithmetic mean, not the median or the most frequent value. For example, if a range contains 10, 20, and 30, the average is 20: the sum, 60, divided by three values.
With numbers in A2:A5 containing 10, 20, 30, and 40, the formula =AVERAGE(A2:A5) returns 25. The function accepts individual numbers and ranges, and you can combine them. With numbers in A2:A4 and another number in C2, =AVERAGE(A2:A4,C2) averages the values in those four cells. Keep ranges easy to inspect, especially in workbooks other people will maintain.
In Microsoft 365 for Windows, type the formula in a cell and press Enter. You can also select a cell and choose Formulas > AutoSum > Average, then confirm or adjust the suggested range before pressing Enter. The suggested range is not guaranteed to match your intended data, so inspect it rather than accepting it automatically. Excel for Mac has the same AVERAGE function and a similar ribbon command; shortcut keys may differ, so use the visible ribbon command if a Windows shortcut does not apply. Excel for the web also supports AVERAGE.
Choose the right averaging function
AVERAGE is the right starting point when every numeric value in the chosen cells should count equally. Excel also offers related functions for different data rules. Choosing among them is not just a matter of convenience: each one answers a different question.
- AVERAGE: Calculates the mean of numeric values in its arguments, ignoring blank cells and text in referenced ranges.
- AVERAGEIF: Averages values that meet one condition, such as sales above zero or records for a particular category.
- AVERAGEIFS: Averages values that meet two or more conditions, such as sales for one region during a particular period.
- MEDIAN: Returns the middle value after sorting the numbers. It can describe a typical result better when a few unusually high or low values skew the mean.
- AVERAGEA: Also considers text and logical values supplied directly or in references, assigning them values under its own rules. Use it only when those rules fit the data you intend to summarize.
Averages can be misleading when the values are unevenly distributed. If five customers spend 10, 12, 13, 14, and 100 dollars, the mean is 29.80, while the median is 13. The mean is mathematically correct, but it does not describe what most customers spent particularly well. Consider the shape and purpose of the data before choosing a summary.
Blanks, zeros, and text: what counts?
A blank cell and a cell containing zero are not equivalent to AVERAGE. Blank cells in a referenced range are ignored. A numeric zero is included in both the sum and the count, which can lower the result. For example, with 10, 20, and 30 in A2:A4, =AVERAGE(A2:A4) returns 20. If A5 contains 0, =AVERAGE(A2:A5) returns 15 because 60 is divided by four.
This distinction matters in surveys, attendance records, and performance tracking. A blank might mean “not recorded” or “not applicable,” while zero might mean a measured result of none. Decide what each represents before calculating. Do not delete or replace zeros just to make an average look more plausible; that changes the underlying data.
Text entries such as labels are ignored when they are in a referenced range, as are cells that contain formulas returning an empty text string. But a number stored as text may not be treated as a number. If values appear numeric but are left-aligned or show a warning indicator, check their cell contents and number formats. Formatting a text value as a number alone does not always convert it; you may need to convert the values to real numbers.
To exclude zero values while retaining positive and negative numbers, place the same range in both parts of AVERAGEIFS. With values in A2:A20, =AVERAGEIFS(A2:A20,A2:A20,"<>0") averages the nonzero numeric entries. The criterion means “not equal to zero.” If the data is guaranteed to be nonnegative, =AVERAGEIF(A2:A20,">0") averages positive values, but it also excludes negative values; that is different from excluding only zero.
Average only values that meet conditions
AVERAGEIF is useful when a data table contains both a measurement and a category. With region names in A2:A20 and corresponding sales amounts in B2:B20, =AVERAGEIF(A2:A20,"East",B2:B20) returns the average sales amount for rows marked East. The first range is checked against the criterion; the last range contains the numbers to average. Those ranges should cover corresponding rows.
You can also use a numeric condition. With scores in B2:B20, =AVERAGEIF(B2:B20,">=70") averages scores of 70 or higher. AVERAGEIFS handles multiple conditions. With region names in A2:A20, product names in B2:B20, and sales in C2:C20, =AVERAGEIFS(C2:C20,A2:A20,"East",B2:B20,"Widgets") averages sales only for rows where the region is East and the product is Widgets.
When a criterion should come from a cell, join the operator to that cell’s value. With scores in B2:B20 and a minimum threshold in D1, =AVERAGEIF(B2:B20,">="&D1) averages values at least as large as the threshold. This lets you change D1 without editing the formula. The formula examples use commas as argument separators; some regional Excel settings use semicolons instead, depending on system and language settings.
Use weighted averages when values have different importance
A regular average gives each number equal weight. That is unsuitable when each result represents a different number of items or has a different share of the total. A class average based on assignment scores, for example, may need to reflect each assignment’s percentage weight. A weighted average multiplies each value by its weight, adds those products, and divides by the sum of the weights.
With prices in B2:B4 and quantities in C2:C4, where prices are 10, 20, and 30 and quantities are 2, 3, and 5, use =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). The weighted total is 10×2 + 20×3 + 30×5 = 230; the total quantity is 10, so the result is 23. A plain average of the three prices would be 20, which answers a different question because it treats each price as equally important regardless of quantity.
Check that weights have the meaning you expect and that their total is not zero. If the weights are percentages, they often sum to 100%, or 1 when stored as decimal fractions; either representation works when applied consistently, because the division scales the result. If the weights do not sum to the intended total, investigate the source rather than assuming the formula will correct the data.
Common errors and ways to check a result
The most common formula error is selecting the wrong range. A formula can return a plausible number while leaving out the newest row or including a subtotal. Compare the highlighted range in the formula bar with the data table, and check that the first and last rows are correct. Converting a data range to an Excel table can help new rows become part of structured references, although the formula syntax will look different from a simple cell range.
If AVERAGE returns #DIV/0!, Excel has no numeric values to average in the supplied arguments. Check for numbers stored as text, an incorrectly selected range, or a criteria condition that matches no numeric rows. To show a more helpful message, with the intended values in A2:A20, use =IFERROR(AVERAGE(A2:A20),"No numeric values"). This displays the message for any error, so it can also mask a different problem; use it where that behavior is appropriate, not as a substitute for checking the source data.
For an average that looks unexpectedly low or high, inspect zeros, outliers, filters, and criteria. AVERAGE does not automatically ignore hidden rows simply because a filter is applied. If the intended result should respond to filtered or manually hidden rows, investigate SUBTOTAL or AGGREGATE and choose the option that matches how hidden rows should be handled. Also verify whether the question calls for a mean, a median, a weighted mean, or an average under specific conditions.
Make averages easier to read and maintain
Format the result to suit the data. Select the result cell and use Home > Number to choose a number format or adjust decimal places. Currency formatting is suitable for money amounts, while a percentage format is appropriate when the underlying result is a proportion. Formatting changes how a value appears, not the calculation itself; reducing displayed decimals does not round the stored result used by other formulas.
For clearer workbooks, label the result with the population it describes, such as “Average response time, completed orders” rather than just “Average.” Put criteria in visible cells when they are likely to change, and use consistent units across the range. A mix of minutes and seconds, or dollars and cents, can produce a valid formula with a meaningless answer.
Before sharing a workbook, check that the formula range includes the intended records, that blank and zero values have been interpreted correctly, and that any criteria are visible or documented. If a chart displays an average as a reference line, explain whether it uses all records or only the currently selected subset. A clear definition makes the number easier to trust and prevents readers from assuming it represents something different.
Frequently Asked Questions
Does Excel AVERAGE include zero?
Yes. A zero is a numeric value and counts in the sum and divisor. Blank cells and text in referenced ranges are ignored. Use a criteria-based formula only if the data definition calls for excluding zeros.
How do I average a column while ignoring blank cells?
Use AVERAGE with the desired range, such as =AVERAGE(B2:B100) when the measurements are in B2:B100. Excel ignores blank cells in that range but includes numeric zeros.
How do I average only cells above a certain number?
Use AVERAGEIF. With the numbers in B2:B20, =AVERAGEIF(B2:B20,">50") averages entries greater than 50. Use >= if 50 should count too.
Why is my Excel average showing #DIV/0!?
The function did not find numeric values to average, which can happen if the range is empty, numbers are stored as text, or criteria match no numeric entries. Check the selected range and the underlying values.
Should I use AVERAGE or MEDIAN?
Use AVERAGE when the arithmetic mean is the measure you need and every value should contribute equally. MEDIAN can better represent the center when a few unusually high or low values strongly affect the mean. The choice depends on what you want the result to communicate.
Related
- Excel Daylight Saving Time: How Dates and Hours Work
- Monthly Excel Budget Format: Build a Simple Template
- Year Calendar in Excel: Create a 12-Month View
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Delete a Full Page in WordTo delete a full page in Word, place the cursor on that page, press Ctrl+G, type \page in…
- How to Delete a Page in Microsoft Word: Blank or FilledTo delete a page in Microsoft Word, click anywhere on the page, press Ctrl+G on Windows (Option+Command+G on…
- How to Create and Add a Signature Stamp in OutlookTo create a signature stamp in Outlook, prepare an image of your handwritten signature, add it to an…
- Google Sheets Duplicate Shortcut: Cells, Rows, and SheetsGoogle Sheets has no single dedicated keyboard shortcut that duplicates every kind of item. To copy cells, use…
- Remove a Page Break in Excel: Manual and AutomaticTo remove a manual page break in Excel for Microsoft 365 on Windows, select the cell immediately below…
- How to Insert a Coloured Line in Word on Any PageTo insert a coloured line in Word, choose Insert > Shapes > Line, draw the line, then select…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.