To calculate an average value in Excel, use the AVERAGE function: with the numbers 12, 15, 18, 21, and 24 in cells B2:B6, enter =AVERAGE(B2:B6) in another cell to get 18. The function returns the arithmetic mean: the sum of the included numbers divided by how many numbers are included.
What “average” means in Excel
In everyday spreadsheet work, “average” usually means the arithmetic mean. Excel adds the numeric values in a specified range and divides the total by the count of those values. It is useful for summarizing data such as test scores, delivery times, monthly sales, temperatures, or customer ratings.
The mean is only one way to describe a typical value. A few unusually high or low numbers can pull it away from what most observations look like. For example, a set of five values—10, 11, 12, 13, and 54—has a mean of 20, even though four of the five values are between 10 and 13. For skewed data or data with outliers, the median may be a more representative summary. Excel’s AVERAGE function calculates the mean, not the median or most common value.
Excel does not have one universal “average value” command that automatically understands what a dataset means. You choose the range and the appropriate function. A simple range average treats each included number equally; averages with conditions or weights require different approaches.
How to use AVERAGE
In Microsoft Excel for Microsoft 365 on Windows, select the cell where the result should appear, type the formula, and press Enter. You can type a range address directly or select the cells with the mouse while entering the formula. The function accepts individual numbers, cell references, and ranges; separate multiple arguments with commas.
With daily sales values in B2:B31, enter =AVERAGE(B2:B31) in the result cell. If the values total 3,100 across 30 numeric cells, the displayed result is about 103.33. Excel stores more precision than may be visible: use the number-format controls on the Home tab to show fewer or more decimal places. Changing the display does not change the underlying calculation.
For a quick check without inserting a formula, select the cells. Excel can show Average on the status bar at the bottom of the window. If it is not visible, right-click the status bar and select Average from the available calculations. The status-bar figure is a convenient preview; a cell formula is better when the result needs to be reused, referenced, or saved as part of a report.
What AVERAGE includes and ignores
Knowing which cells count is essential for interpreting the result. When AVERAGE refers to a range, it includes numeric values, including zero. It ignores empty cells and text in the referenced cells. A cell with a formula that returns an empty text string is also treated as text rather than a numeric observation. Error values in the range generally cause the average formula to return an error, so check for errors before relying on the result.
For example, if B2:B6 contains 10, 20, a blank cell, the text “pending,” and 0, the average is 10: Excel calculates (10 + 20 + 0) divided by 3. The blank and text cell do not enter the count, but the zero does. If the zero actually means “not measured,” leaving it in the range lowers the result; if it means a real measurement of zero, it belongs in the calculation.
This behavior can differ from functions that accept values directly as arguments. AVERAGE ignores text within a referenced range, but text supplied directly as an argument may be evaluated differently. For predictable results, use cell ranges for ordinary data and check the source cells for numbers stored as text. A green warning marker or left-aligned number can sometimes indicate that a value is stored as text, though formatting alone is not a reliable test.
AVERAGEA is a related function, but it does not treat text in referenced cells the same way: it counts text as zero and logical values as 1 for TRUE and 0 for FALSE. That behavior may be useful for particular data designs, but it can produce a surprising result if labels or notes are mixed into a range. For most numeric datasets, AVERAGE is the usual choice.
Average only values that meet a condition
Use AVERAGEIF when values should be included only if a corresponding cell meets one condition. For example, assume A2:A6 contains region names and B2:B6 contains sales: North 100, South 80, North 120, East 90, and North 140. With regions in A2:A6 and sales in B2:B6, enter =AVERAGEIF(A2:A6,"North",B2:B6) to return 120, the average of 100, 120, and 140.
The first range in AVERAGEIF is the range Excel checks against the condition; the final range supplies the values to average. If the cells being checked are also the cells to average, the final range can be omitted. Conditions can use text, numbers, or comparison operators. For example, a criterion can select values greater than a threshold; when an operator is combined with a cell reference, construct the criterion by joining the operator in quotation marks to the reference with an ampersand.
For two or more conditions, use AVERAGEIFS. Suppose A2:A7 contains regions, B2:B7 contains sales, and C2:C7 contains quarter labels. The six rows are North/Q1/100, North/Q2/120, North/Q1/140, South/Q1/80, North/Q2/160, and South/Q2/90. With sales in B2:B7, regions in A2:A7, and quarters in C2:C7, enter =AVERAGEIFS(B2:B7,A2:A7,"North",C2:C7,"Q1") to return 120. It averages the two North Q1 sales values, 100 and 140. In AVERAGEIFS, the average range comes first, followed by each criteria range and its condition.
When a weighted average is better
A plain average gives every observation equal influence. That is appropriate when each row represents one equally important observation, but not when rows represent different quantities or importance. A weighted average multiplies each value by its weight, adds those products, then divides by the total weight.
Suppose A2:A4 lists three courses, B2:B4 contains their credit weights of 2, 3, and 5, and C2:C4 contains scores of 80, 90, and 70. With weights in B2:B4 and scores in C2:C4, enter =SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4) to get 78. The weighted total is 2×80 + 3×90 + 5×70 = 780, and the total weight is 10, so 780 divided by 10 is 78. The ordinary mean of the three scores would be 80; it differs because the score of 70 has the largest weight.
Before using weights, confirm what they represent. They might be units sold, course credits, or the number of observations represented by each row. Weights should be numeric and aligned row by row with the values. If the total weight is zero, the division cannot produce a meaningful average. If your data is already one observation per row, adding weights unnecessarily can distort a result that should simply use AVERAGE.
Choosing between mean, median, and other summaries
Use the mean when every numeric observation matters equally and a few extreme values will not mislead the reader. Use the median when the middle observation better describes a skewed distribution—for example, house prices or response times where a small number of very large values may raise the mean. In a sorted list, the median is the central value, or the midpoint of the two central values when the list has an even number of observations.
With observations in B2:B20, enter =MEDIAN(B2:B20) to find the middle value. To find the most frequently occurring numeric value instead, use MODE.SNGL: with the same data in B2:B20, enter =MODE.SNGL(B2:B20). The mode can return an error if there is no repeated number. These measures answer different questions; none is automatically the best summary in every context.
For a useful report, it can help to show the mean alongside the median, the number of observations, and a measure of spread. Averages without context can hide variation: two groups can have the same mean but very different ranges of values. Also state how missing entries and zero values were handled so readers can understand what the calculation represents.
Filtered rows, formatting, and common problems
A common surprise is that AVERAGE includes rows hidden by a filter or manually hidden. If the intended calculation should respond to filtering, use SUBTOTAL instead. With values in B2:B10, enter =SUBTOTAL(1,B2:B10) to average visible rows while including manually hidden rows; enter =SUBTOTAL(101,B2:B10) to exclude both filtered-out and manually hidden rows. These formulas assume the range contains the values to summarize. The function number 1 specifies average, while 101 specifies average with manually hidden rows excluded.
If an average looks wrong, check these common causes:
- Zeros are included. Confirm whether zero is a real observation or a placeholder for missing data.
- Numbers are stored as text. Convert or re-enter them as numeric values; merely changing the cell format may not convert text into numbers.
- The selected range is incomplete or too broad. Check the first and last row, and make sure headings or totals are not included by mistake.
- Errors are present. Find and resolve error values in the range rather than assuming Excel will skip them.
- The display is rounded. Increase the displayed decimal places if needed, but distinguish formatting from the stored result.
- Rows are hidden or filtered. Decide whether the calculation should include all records or only the visible subset.
If AVERAGE returns #DIV/0!, Excel has no numeric values to divide by in the supplied references. Check whether the range is empty, contains only text, or points to the wrong cells. If the formula returns another error, inspect the source cells and the formula’s range references. These checks are usually more useful than changing the display format, because formatting cannot repair an incorrect input range.
Frequently Asked Questions
What is the Excel formula for average?
Use AVERAGE followed by the cell range in parentheses. For values in B2:B10, enter =AVERAGE(B2:B10). Excel returns the arithmetic mean of numeric entries in that range.
Does Excel include blank cells or zeros in an average?
AVERAGE ignores blank cells but includes zeros. A zero therefore lowers the mean when other values are positive. Decide whether zero represents a real value or missing information before calculating.
How do I average a column while ignoring zeros?
Use AVERAGEIF with a not-equal-to-zero condition. With values in B2:B10, enter =AVERAGEIF(B2:B10,"<>0"). This excludes zeros, but it does not answer whether excluding them is appropriate for your data.
How do I average only matching rows?
Use AVERAGEIF for one condition or AVERAGEIFS for multiple conditions. Set the criteria range or ranges to the columns that identify the rows, and set the average range to the numeric values you want summarized.
Why does AVERAGE return an error?
#DIV/0! usually means there are no numeric values in the referenced cells. Other errors may come from error values within the range or an invalid reference. Check the data and range before changing the formula.
Make the result meaningful
The basic answer is simple: AVERAGE calculates the arithmetic mean of numeric values in a range. The important decision is defining which values belong in that range and whether they should count equally. Use AVERAGE for an ordinary mean, AVERAGEIF or AVERAGEIFS to apply conditions, and a weighted calculation when observations carry different weights. When outliers, hidden rows, blanks, or zeros affect interpretation, make those choices explicit. That gives readers a result they can both reproduce and understand.
Related
- Excel NOT ISBLANK: How to Check for Nonblank Cells
- Excel Portfolio Analysis: Track Risk and Returns
- How to Hide a Column in Excel: Steps and Shortcuts
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel WEEKDAY vs WORKDAY: What Each Function Does in ExcelExcel’s WEEKDAY function tells you which day of the week a date falls on, as a number; WORKDAY…
- Midpoint of Two Dates in Excel: Formula and ExamplesTo find the midpoint of two dates in Excel, enter the start date in A2 and the end…
- CHAR Excel Usage Examples: Formulas, Codes, and TipsIn Excel, CHAR converts a number from the computer’s character set into a character. For example, with no…
- COUNTA and COUNTIF in Excel: How to Use Them TogetherTo find what share of filled status cells say Complete, with statuses in B2:B11, use =COUNTIF(B2:B11,"Complete")/COUNTA(B2:B11). If five…
- Find a Name in Excel: Search, Match, and TroubleshootTo find a name in an Excel spreadsheet, press Ctrl+F on Windows, type the name, and select Find…
- Cash Flow Formula Excel Template and SetupA basic cash flow Excel template tracks cash received and cash paid, then calculates net cash flow as…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.