How to Calculate Average in Excel: AVERAGE, AVERAGEIF, Weighted and Moving Average

shape
shape
shape
shape
shape
shape
shape
shape
How to Calculate Average in Excel: AVERAGE, AVERAGEIF, Weighted and Moving Average - featured image

The average, also called the mean, is the sum of the values divided by the number of values. It is the most common way to summarize a list of numbers, such as test scores, daily sales or monthly costs. In Excel you can calculate it with the AVERAGE function in one step, and you can use related functions to average only the values that meet a condition, ignore zeros and blanks, give some values more weight, or smooth a trend with a moving average. This guide explains each method with a worked example that you can check by hand, shows how Excel treats empty cells and text, and lists the mistakes that cause wrong averages.

Updated November 2026

In this guide

  1. What the average means
  2. Method 1: the AVERAGE function
  3. How Excel treats blanks, zeros and text
  4. Method 2: AVERAGEIF for one condition
  5. Method 3: AVERAGEIFS for several conditions
  6. Ignore zeros in an average
  7. Weighted average
  8. Moving average
  9. Average of visible cells only
  10. Trimmed mean to reduce outliers
  11. Average of separate cells and other sheets
  12. Format the result
  13. Common mistakes
  14. Practice problems
  15. Quick recap

What the average means

To find the average by hand, add up the numbers and divide by how many there are. For the scores 70, 85, 90, 65 and 80, the sum is 390 and there are 5 scores, so the average is 390 divided by 5, which is 78. The average is a single number that represents the typical value. It is affected by very large or very small values, so it is often shown together with the median. Our mean, median and mode guide explains the differences.

Average Calculator screenshot used in the guide: How to Calculate Average in Excel
Check your answer with our free Average Calculator.

Method 1: the AVERAGE function

Type the scores in A2:A6. In another cell, type =AVERAGE(A2:A6) and press Enter. The result is 78. The function accepts a range, single cells, numbers or a mix, such as =AVERAGE(A2:A6,100). In the last case, the sum is 490 and there are 6 values, so the result is 81.67. AVERAGE accepts up to 255 arguments. You can also use the AutoSum drop-down on the Home tab, and choose Average, which inserts the formula for you.

How Excel treats blanks, zeros and text

AVERAGE ignores empty cells and cells that contain text, but it counts zeros. This difference is important. If the scores are 70, 0, 90 and 80, with 0 meaning the student scored zero, the average is 240 divided by 4, which is 60. If the zero means that the student did not take the test, the average should be 240 divided by 3, which is 80. Decide what a zero means before you calculate. Empty cells are skipped, so the cell with no data does not change the average. Numbers stored as text are also skipped, which can give a wrong result, so check the data type.

Method 2: AVERAGEIF for one condition

AVERAGEIF averages the cells that meet a condition. The syntax is AVERAGEIF(range, criteria, average_range). Suppose column A holds the class, either A or B, and column B holds the scores of 70, 85, 90, 65 and 80 for the classes A, B, A, B and A in that order. The average for class A uses =AVERAGEIF(A2:A6,"A",B2:B6). The class A scores are 70, 90 and 80, which total 240, so the result is 80. For class B, the scores are 85 and 65, which give 75. The criteria can also be a comparison, such as “>70”, to average only the scores above 70.

Method 3: AVERAGEIFS for several conditions

AVERAGEIFS uses more than one condition. The syntax puts the average range first: AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2). For example, to average the scores of class A that are above 70, use =AVERAGEIFS(B2:B6,A2:A6,"A",B2:B6,">70"). The class A scores above 70 are 90 and 80, so the average is 85. All the conditions must be true for a value to be included. This function is a good companion for our SUMIFS guide.

Ignore zeros in an average

To average only the non-zero values, use =AVERAGEIF(A2:A5,"<>0"). For 70, 0, 90 and 80, it returns 80. In this formula, the range is also the average range, so you can leave out the third argument. This is useful for sales per day when some days have no sales because the shop was closed. If every value is zero, the formula returns a #DIV/0! error, since there is nothing to average, so wrap it with IFERROR, as in our IFERROR guide.

Weighted average

A weighted average gives some values more importance. If a course has an exam worth 60 percent with a score of 70, and coursework worth 40 percent with a score of 90, the weighted average is 70 times 0.6 plus 90 times 0.4, which is 42 plus 36, so 78. In Excel use =SUMPRODUCT(B2:B3,C2:C3)/SUM(C2:C3), with the scores in B and the weights in C. Note that the simple average of 70 and 90 is 80, which is different. Our SUMPRODUCT guide and our weighted average guide explain this in more detail.

Moving average

A moving average smooths short-term changes, so that a trend is easier to see. For monthly sales of 100, 110, 120, 130 and 140, a three-month moving average starts in the third month with =AVERAGE(B2:B4), which gives 110. Fill it down, and the next values are 120 and 130. The moving average rises steadily, as the data does. You can also add it to a chart as a trendline of type Moving Average, as described in our line graph guide. A longer window gives a smoother line but reacts more slowly.

Average of visible cells only

If you filter a list, AVERAGE still includes the hidden rows. To average only the visible rows, use SUBTOTAL with the function number 101: =SUBTOTAL(101,B2:B100). The number 101 means average and ignore hidden rows. If you have filtered the list to show only class A, the formula returns the average of the class A rows. Our SUBTOTAL guide lists the other function numbers. This is a quick way to get an average that follows the filter.

Trimmed mean to reduce outliers

A few very large or small values can pull the average away from the typical value. TRIMMEAN removes a percentage of the values from the top and bottom before it averages. For 10 values, =TRIMMEAN(A2:A11,0.2) drops 20 percent of the data points, which is 2 values, one from each end. If the values are 10, 12, 13, 14, 15, 15, 16, 17, 18 and 90, the plain average is 22, but the trimmed mean is the average of the middle eight, which is 15. Use it when the data has clear outliers.

Average of separate cells and other sheets

AVERAGE does not need the cells to be next to each other. Separate the ranges with commas, such as =AVERAGE(A2:A6,C2:C6), or click the cells while holding Ctrl. To average the same cell on several sheets, use a 3D reference such as =AVERAGE(Jan:Dec!B2), which averages cell B2 on every sheet from Jan to Dec. This is a quick way to find the yearly average of a monthly report. Make sure that the sheets in between have the same layout, or the result will include the wrong cells, and keep the first and last sheets in the right order.

Format the result

Use the Increase Decimal and Decrease Decimal buttons to show the number of decimals you need. An average of 81.6666 can be shown as 81.67. The cell still holds the full value. If you want to round the value itself, use ROUND, as in =ROUND(AVERAGE(A2:A6),1). For money, use the currency format. Do not round the data before averaging, because that can change the result slightly. Round only at the end, when you present the number.

Common mistakes

The most common mistake is to include a total or a subtotal in the range, which inflates the average. Another is to forget that zeros count and blanks do not. A third is to average percentages without weights, when the groups have different sizes. For example, averaging 50 percent of 10 people and 80 percent of 100 people with a simple average gives 65 percent, but the weighted result is about 77 percent. A fourth is to average numbers stored as text. A fifth is to use a range that includes the header.

Practice problems

Try these. Problem 1: the average of 4, 8 and 12 is 8. Problem 2: the average of 70, 85, 90, 65 and 80 is 78. Problem 3: the average of the class B scores of 85 and 65 is 75. Problem 4: the average of 70, 0, 90 and 80 ignoring zeros is 80. Problem 5: the three-month moving average for 100, 110 and 120 is 110. Problem 6: the weighted average of 70 at 60 percent and 90 at 40 percent is 78.

Quick recap

Use AVERAGE for a simple mean, AVERAGEIF and AVERAGEIFS to average by conditions, and the criterion “<>0” to ignore zeros. Use SUMPRODUCT divided by SUM for a weighted average, and AVERAGE over a sliding range for a moving average. Use SUBTOTAL 101 for filtered lists and TRIMMEAN to reduce the effect of outliers. Decide what zeros and blanks mean before you calculate, and check one result by hand. These functions cover almost every kind of average you will need.

Frequently asked questions

What is the formula for average in Excel?

=AVERAGE(range).

Does AVERAGE ignore zeros?

No, it counts zeros but ignores blank cells and text.

How do I average only values that meet a condition?

Use AVERAGEIF or AVERAGEIFS.

How do I calculate a weighted average?

Divide the SUMPRODUCT of values and weights by the sum of the weights.

How do I average only visible cells?

Use SUBTOTAL with function number 101.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *