If your values are in A2:A10, the Excel formula for their general arithmetic average is =AVERAGE(A2:A10). It adds the numeric values in that range and divides by how many numeric values it finds.
What “general average” means in Excel
In an Excel question, “general average” usually means the ordinary arithmetic mean: add a set of numbers, then divide the total by the number of numbers. It gives each observation equal influence. For example, if you average several quiz scores and each quiz counts equally, the arithmetic mean is usually the relevant starting point.
This is different from a weighted average, where some observations count more than others, and from a median, which is the middle value after sorting. The average can also be pulled up or down by unusually large or small values. Before choosing a formula, check what the numbers represent and whether they should have equal weight.
In Excel, the built-in AVERAGE function is generally clearer and easier to maintain than writing a manual sum divided by a count. It also adjusts naturally when you change values in the referenced cells. If you mean a specialist use of “general average,” such as a term from shipping, that is not the same calculation as an ordinary spreadsheet mean.
How to enter the basic formula
Put the observations in a continuous column or row, select the cell where you want the answer, and enter the formula. With values in A2 through A10, use =AVERAGE(A2:A10). Press Enter to calculate it. The colon in A2:A10 means “from A2 through A10,” including both end cells.
You can type the formula directly into a cell or into the formula bar. You can also start typing =AVERAGE(, select the range with the mouse, type the closing parenthesis, and press Enter. In Microsoft 365 for Windows, formulas normally use commas between separate arguments. Regional settings can affect the separator shown by Excel, so a workbook configured for a different list separator may display semicolons instead.
The range reference is usually preferable to listing every value individually. If you later replace a score or add a value inside the selected range, Excel recalculates the result. If you add new rows below the range, however, the formula will not necessarily include them; expand the reference or use an Excel Table if the data is expected to grow.
Worked example: average a set of scores
Suppose A2:A7 contains the scores 72, 85, 90, 68, 95, and 80. With those six scores in A2:A7, enter =AVERAGE(A2:A7). Their sum is 490, and 490 divided by 6 is 81.6667, so Excel displays about 81.67 if the result is formatted to two decimal places.
The displayed precision does not change the underlying result. Excel retains the calculated value and only rounds what appears on screen when you apply a number format. That distinction matters if you later use the average in another calculation. Avoid rounding the source values merely to make the display look tidy unless rounding is part of the calculation you actually need.
You can average more than one range in a single formula. With one group of numbers in B2:B6 and another in D2:D6, use =AVERAGE(B2:B6,D2:D6). This treats all numeric cells in both ranges as one combined set. It does not calculate the two group averages and then give those two averages equal weight; when the groups have different numbers of observations, those methods can produce different answers.
How blanks, text, and zeros affect the result
When a cell reference is passed to AVERAGE, empty cells and text entries in the referenced cells are ignored. Numeric values are included, and zero is a numeric value, so it counts in the calculation. This is one of the most important details to check when an average seems unexpectedly high or low.
For example, if B2:B5 contains 12, 0, 18, and 30, the ordinary average is 15: the total of 60 is divided by four. If the zero means “not measured” rather than a real result, including it may not reflect the intended analysis. With those values in B2:B5, use =AVERAGEIF(B2:B5,"<>0") to average the nonzero values; the result is 20.
Do not exclude zeros automatically. A zero may be a legitimate score, a count, or a measured value, in which case omitting it would distort the result. Decide what a blank or zero means in the source data, and document the rule if other people will use the workbook. A number stored as text can also be ignored by AVERAGE, so a cell that looks like 25 may not be treated as the number 25 if it was imported or entered as text.
Choose between an average, a weighted average, and a median
Use a plain average when each observation should count equally. Use a weighted average when each observation represents a different share, quantity, or importance. For example, a course grade might combine categories worth different percentages; averaging the category scores equally would be misleading if the syllabus assigns different weights.
With scores in B2:B4 of 80, 90, and 70, and corresponding weights in C2:C4 of 20%, 30%, and 50%, the weighted result is 78. Enter =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). The calculation is 80 × 20% + 90 × 30% + 70 × 50% = 78. The weights should be numeric and should add to 100% for this example; dividing by their sum also handles weights expressed as 20, 30, and 50 rather than percentages.
A median may be more representative when a few extreme values would skew the mean, such as a set of incomes or response times. The mean and median answer different questions, so there is no universally correct choice. If the request specifically asks for a general or arithmetic average, use AVERAGE unless the data instructions explain that weighting or another measure is required.
Average only values that meet a condition
Sometimes the question is not “What is the average of every row?” but “What is the average for one category?” AVERAGEIF calculates the average of values that match one condition. Suppose A2:A7 contains region names and B2:B7 contains the corresponding sales amounts. If the East rows have sales of 80, 90, and 70, their average is 80.
With region labels in A2:A7 and sales figures in B2:B7, use =AVERAGEIF(A2:A7,"East",B2:B7). The first range is checked for the condition, and the third argument supplies the values to average. If you leave out the third argument, AVERAGEIF averages the cells in the criteria range that meet the condition, which is useful when the condition and values are in the same column.
For more than one condition, Excel has AVERAGEIFS, which can filter by multiple criteria, such as a region and a date period. Make sure the criteria ranges and average range cover corresponding rows. A mismatch in range sizes can cause an error or an unintended calculation, so use ranges with the same row boundaries.
Average visible rows after filtering
A normal AVERAGE formula includes values in rows hidden by an AutoFilter. If you want a result that changes to reflect only the records currently visible after filtering, use SUBTOTAL. With values in B2:B5 of 10, 20, 30, and 40, and a filter that hides the row containing 10, use =SUBTOTAL(1,B2:B5). The visible values are 20, 30, and 40, so the result is 30.
SUBTOTAL with function number 1 calculates an average and excludes rows removed by a filter. It still includes rows hidden manually; function number 101 is the average option that also excludes manually hidden rows. Choose based on how the rows are hidden, and remember that a filter changes which records are visible, not the underlying data.
Common problems and how to fix them
- The result is too low: Check whether zeros are included, whether a large range contains values that should not be part of the calculation, and whether the formula includes the intended rows.
- The result is too high: Look for missing low values, numbers stored as text, or a formula range that stops before the final observations.
- Excel returns #DIV/0!: AVERAGE has no numeric values to divide by in the referenced cells. Check for an empty range, text-formatted numbers, or cells that contain only labels.
- The formula returns another error: An error value within the range can cause AVERAGE to return an error. Find and resolve the source error rather than assuming the average formula is at fault.
- The formula appears as text: Check whether the cell is formatted as Text or whether an apostrophe was entered before the equals sign. Change the cell to General, then re-enter the formula.
Also verify that the range begins and ends where you intend. A formula can be syntactically valid and still produce the wrong answer if it includes a heading, omits a final record, or combines data from different groups. Selecting the range in the formula bar is a quick way to inspect which cells Excel is using.
Display and interpret the answer
To show a sensible number of decimal places, select the result cell and use the number-format controls on the Home tab. Increasing or decreasing displayed decimals changes the presentation, not the stored average. For percentages, format the result as Percentage only when the source values and intended interpretation support that format; formatting a number as a percentage multiplies its displayed appearance by 100.
Finally, treat the average as a summary, not a complete description of the data. Two groups can have the same mean but very different spreads, and a mean may not match any actual observation. For important comparisons, consider showing the number of observations and the range or distribution alongside the average.
Frequently Asked Questions
What is the simple Excel formula for an average?
Use AVERAGE with the cells you want to include. If the values are in A2:A10, enter =AVERAGE(A2:A10).
Does Excel include zero in an average?
Yes. A zero in a referenced cell is counted as a numeric value. Use a conditional average only if zero represents data you intentionally want to exclude.
Does AVERAGE count blank cells?
No. Blank cells in a referenced range are ignored. Text entries are also ignored, while numeric cells—including zeros—are included.
How do I average a column without counting zeros?
If the values are in B2:B100, use =AVERAGEIF(B2:B100,"<>0"). This excludes zero values; use it only when zero should not count as an observation.
Why does my average show #DIV/0!?
Excel has found no numeric values to average in the referenced range. Check that the range is correct and that any values that look like numbers are stored as numbers rather than text.
Related
- Text Split in Excel: Polish Menus and Methods
- Text Split in Excel: How to Separate Data into Columns
- Rate Quotation Format in Excel: A Practical Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- VLOOKUP Excel: Extended Uses, Examples, and LimitsVLOOKUP can do more than retrieve one value from a simple list: it can use exact or approximate…
- Excel Timesheet Calculator Formula: Examples and TipsFor an Excel timesheet with start time in B2, end time in C2, and an unpaid break duration…
- Conditional Formatting If Statements in Excel: A GuideIn Excel, an “if statement” for conditional formatting is usually a formula-based rule that returns TRUE or FALSE;…
- Excel HLOOKUP: Return a Column NumberHLOOKUP does not return the number of the column where it finds a match; it returns a value…
- Concatenate an Excel List: Join Values With DelimitersTo concatenate an Excel list into one cell, use TEXTJOIN: with the values in A2:A6, enter =TEXTJOIN(", ",TRUE,A2:A6).…
- Salesforce Row-Level Formula Examples for ReportsSalesforce row-level formulas calculate a value separately for each record in a report; useful examples include discount percentage,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.