To calculate an average in Excel, use =AVERAGE(B2:B10) if the numbers are in cells B2 through B10. The formula adds the numeric values and divides by how many numbers there are; Excel also offers an AutoSum option for inserting an average without typing the function.
How the AVERAGE function works
AVERAGE calculates the arithmetic mean: the total of the values divided by their count. For example, if B2:B5 contains 12, 15, 18, and 21, enter =AVERAGE(B2:B5) in another cell. The result is 16.5 because the four values total 66, and 66 divided by 4 is 16.5.
To use it in Microsoft Excel for Microsoft 365 on Windows:
- Select the cell where you want the answer.
- Type an equals sign and the function name, then an opening parenthesis:
=AVERAGE(. - Select or type the range, such as
B2:B10, and close the parenthesis. - Press Enter. Excel displays the result in the selected cell.
A colon between cell references means “through,” so B2:B10 includes every cell from B2 to B10. You can also average separate ranges or individual cells. If the numbers are in B2:B5 and D2, for instance, =AVERAGE(B2:B5,D2) includes those five cells. Keep the references up to date if you add more data beyond the range.
Insert an average without typing the formula
For a quick calculation, select the empty cell where the answer should appear. On the Windows ribbon, choose Home > Editing > AutoSum ▼ > Average, check that Excel has selected the intended cells, and press Enter. The dropdown is useful because AutoSum may otherwise suggest a total rather than an average. Ribbon group placement can vary with window size or ribbon layout.
You can also select a range of numeric cells and look at Excel’s status bar at the bottom of the window. If Average is shown, it gives a quick result without putting a formula in the worksheet. If it is not shown, right-click the status bar and enable Average. This is handy for a one-off check, but a cell formula is better when you need a lasting result that updates as the data changes.
The formulas work the same way in Excel for Mac and Excel for the web. Ribbon labels and placement may differ by platform or layout, but you can enter the formula directly in a cell in either version.
What Excel includes in an average
AVERAGE includes numeric values, including zero. It ignores empty cells, text in referenced cells, and logical values such as TRUE or FALSE stored in the range. This behavior matters: a blank cell does not lower the result, while a cell containing 0 does. If a range contains 10, 20, and a blank, the average is 15; if the third cell contains 0, the average is 10.
Text that looks like a number may still be stored as text, especially after importing data. In a referenced range, AVERAGE generally ignores text rather than treating it as a number. Convert such entries to numeric values if they should count. One way to spot a problem is to compare the visible entries with a count of the numeric cells or inspect whether the values are left-aligned unexpectedly; formatting alone, however, does not prove a value is text.
Excel stores dates and times as numbers, so AVERAGE can calculate their numeric mean too. If you average dates, format the result cell as a date to display it as a calendar date. If you average times, use a time format. For durations that may exceed 24 hours, a format such as [h]:mm can display the full elapsed time instead of wrapping at a day.
Average only values that meet a condition
Use AVERAGEIF when one condition determines which numbers count. Suppose A2:A5 contains North, South, North, South, while B2:B5 contains 100, 80, 140, 120. If D2 contains North, the formula =AVERAGEIF(A2:A5,D2,B2:B5) returns 120: it averages 100 and 140, the sales values on the North rows.
AVERAGEIF takes a criteria range, a condition, and—when the values to average are in a different range—the average range. The criteria can be text, a number, a comparison, or a cell reference containing the criterion. Referencing a cell such as D2 makes it easy to change the condition without editing the formula. Make sure the criteria range and average range cover corresponding rows.
For more than one condition, use AVERAGEIFS. If A2:A6 contains North, North, North, South, South; B2:B6 contains Complete, Complete, Pending, Complete, Pending; and C2:C6 contains 100, 140, 60, 200, 100, then =AVERAGEIFS(C2:C6,A2:A6,"North",B2:B6,"Complete") returns 120. It averages only the 100 and 140 entries, where the region is North and the status is Complete. AVERAGEIFS puts the range to average first, followed by each criteria-range and criteria pair.
Calculate a weighted average
A simple average gives every value equal influence. A weighted average is appropriate when values count differently, such as test scores with different exam weights or unit prices with different quantities. The weights should reflect the intended contribution of each value; if they are percentages, check that they total 100% (or 1).
If B2:B4 contains scores of 80, 90, and 70, and C2:C4 contains weights of 20%, 30%, and 50%, enter =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). It returns 78: the weighted contributions are 16, 27, and 35, which total 78, and the weights total 100%. Dividing by the sum of weights also handles weights that have not been entered as percentages, as long as they are meaningful relative weights.
Do not use a plain AVERAGE for this example: it would return 80 and give the 20%-weighted score the same influence as the 50%-weighted one. If the weights contain blanks or are entered inconsistently, check them before relying on the result. A weighted average is only as meaningful as the weighting scheme.
Average a filtered list
AVERAGE over a range normally includes rows hidden by a filter. If you need the result to reflect only the rows currently visible after filtering, use SUBTOTAL. With 10, 20, and 30 in B2:B4, if a filter hides the row containing 30, =SUBTOTAL(1,B2:B4) returns 15, the average of the two visible values.
In SUBTOTAL, function number 1 means average. It ignores rows excluded by a filter, but it includes manually hidden rows. Function number 101 also means average and excludes manually hidden rows as well as filtered-out rows. Choose based on what “visible” means for your worksheet. SUBTOTAL is designed for summaries of lists and tables; it also avoids counting other SUBTOTAL results inside its reference.
Common problems and how to fix them
- The answer seems too low: Check for zeros. AVERAGE counts zero as a real value, even though it ignores blanks. If zero means “not recorded” in your dataset, decide how to handle those entries rather than assuming Excel will exclude them.
- The answer seems too high or too low because entries are missing: Check for numbers stored as text, and confirm that the selected range includes every intended row. Text in a referenced range is not averaged as a number.
- You see #DIV/0!: The referenced range has no numeric values for AVERAGE to use, or a conditional average found no matching numeric entries. Check the range, criteria, and data types.
- You see another error: An error value within the referenced data can cause AVERAGE to return an error. Find and correct the source error before trusting the summary; concealing it can hide a data-quality problem.
- The result does not update as expected: Verify that new rows fall inside the formula’s range. Consider converting a regular data list into an Excel Table using Insert > Table; formulas that refer to table columns can expand with the table as rows are added.
- The result shows too few decimal places: The displayed value may be rounded by the cell’s number format even when Excel retains more precision. Use Home > Number to choose a suitable format or increase displayed decimal places.
Also distinguish an average from a median. A very large or small outlier can pull the arithmetic mean toward itself; the median is the middle value when data is ordered. For example, in a set of typical transaction amounts with one unusually large transaction, the mean may not describe a typical transaction well. The right summary depends on the question, not just the easiest formula.
Choose the right average method
Use the simplest method that matches the data and the question:
- Use AVERAGE for the arithmetic mean of a numeric range.
- Use AVERAGEIF or AVERAGEIFS when only entries meeting one or more conditions should count.
- Use a weighted calculation when values have different, explicit weights.
- Use SUBTOTAL when a filtered list should average only visible rows.
- Use the status bar for a quick check that does not need to remain in a worksheet cell.
AVERAGEA is another function, but it is not a general fix for missing numbers. Unlike AVERAGE, it can treat text in referenced cells as zero and logical values as numbers (TRUE as 1 and FALSE as 0). That changes the meaning of the result, so use it only when that treatment is actually intended. For many ordinary datasets, AVERAGE is the clearer choice.
Frequently Asked Questions
Does Excel count blank cells when calculating an average?
AVERAGE ignores empty cells in a referenced range. It does count numeric zeros, so a blank and a zero do not have the same effect on the result.
How do I average a column in Excel?
If the values are in B2:B100, enter =AVERAGE(B2:B100) in an empty cell. Change the range to match your data and leave the answer cell outside the range.
How do I average cells that are not next to each other?
List the separate cells or ranges as arguments. For values in B2, B5, and D2, use =AVERAGE(B2,B5,D2). Excel averages the numeric entries among those references.
Why does AVERAGE return #DIV/0!?
There may be no numeric values in the range, or a condition may match no numeric entries. Check that the cells contain numbers rather than text and that the range or criteria are correct.
Can Excel average only visible rows?
Yes. Use =SUBTOTAL(1,B2:B100) to average rows visible after filtering. Use function number 101 instead of 1 if manually hidden rows should also be excluded.
Related
- Bluebeam Quantity Link to Excel: Setup and Limits
- Bluebeam Export to Excel: Steps, Fields, and Fixes
- Blank Checklist Template Excel: Create One That Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Checking If a Date Is Before Another DateTo check whether one date is earlier than another in Excel, use the less-than operator: with the date…
- Microsoft Excel Concatenate Text: Formulas and ExamplesTo concatenate text in Microsoft Excel, join cell contents with the ampersand operator (&), or use CONCAT or…
- Excel Timesheet Hours Formula: Calculate Time WorkedTo calculate a shift in decimal hours in Excel, put the start time in B2 and end time…
- Excel EDATE With Days Instead of Months: What to UseNo. EDATE adds months, not days; if A2 contains 10-Feb-2024 and B2 contains 30, use =A2+B2 to get…
- Excel Named Range Formulas: Uses, Setup, and ExamplesIn Excel, a named range lets you use a meaningful name in a formula instead of a cell…
- Excel LET Function: How to Name and Reuse CalculationsUse Excel’s LET function to give a name to a value or calculation, then use that name elsewhere…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.