How to Average Percentages in Excel Correctly

To find an average percentage in Excel, use =AVERAGE(range) only when every percentage comes from a group of the same size. When the groups differ, add up the parts and divide by the total, or use a weighted average with SUMPRODUCT. This guide shows both on one small table, so you can see why the two answers differ and pick the right one in a report or a job test.

How Excel stores percentages#

A percentage in Excel is just a number shown in a different way. When you type 5%, Excel stores 0.05 and displays it with a percent sign. That matters for averages, because AVERAGE works on the stored number, not the label.

• Show a number as a percentage: select the cells and press Ctrl+Shift+% on Windows or Cmd+Shift+% on a Mac, or click Percent Style on the Home tab.
• Show decimals: click Increase Decimal so 0.053 shows as 5.3% rather than 5%.
• Watch for text: a value like "5%" pasted from a website may be text. Text usually sits on the left of the cell, and AVERAGE skips it.

All the examples use this table of online orders and returns in A1:D6. The Return rate column is Returns divided by Orders.

The simple average with AVERAGE#

The quick way to average percentages is AVERAGE on the rate column:

=AVERAGE(D2:D6)

The result is 0.068, or 6.8%: (5 + 12 + 3 + 6 + 8) / 5. This answers the question "what is the return rate of a typical store?" Each store counts once, whatever its size. If that is your question, you are done. The guide to AVERAGE, MIN and MAX covers the function itself, and the AVERAGE function guide is a quick syntax reference.

But it does not answer "what share of all orders came back?" For that, 6.8% is wrong.

Why the simple average of percentages can be wrong#

AVERAGE gives every percentage the same weight, even when the groups behind them are very different sizes. South has the highest rate, 12%, but only 50 orders. East has the lowest rate, 3%, on 400 orders. In the simple average, South's 50 orders count as much as East's 400.

Look at the real totals. The five stores had 1,000 orders and 53 returns. That is 53 / 1,000 = 5.3%, not 6.8%. The small, high-rate store pulled the simple average up.

The rule of thumb:

How to get the true overall percentage with SUM#

When you have the counts, divide the total of the parts by the total of the bases:

=SUM(C2:C6)/SUM(B2:B6)

1. SUM(C2:C6) adds the returns: 53.
2. SUM(B2:B6) adds the orders: 1,000.
3. Dividing gives 0.053. Format it as a percentage to see 5.3%.

This is the safest method because it never touches the rate column at all. It works the same way for pass rates, conversion rates, attendance rates and profit margins: total passed over total taken, total profit over total revenue.

How to average percentages with SUMPRODUCT (weighted average)#

Sometimes you only have the rates and the size of each group, not the counts. Then weight each rate by its group size with SUMPRODUCT:

=SUMPRODUCT(B2:B6,D2:D6)/SUM(B2:B6)

SUMPRODUCT multiplies each pair and adds the results: 200 × 5% + 50 × 12% + 400 × 3% + 150 × 6% + 200 × 8% = 10 + 6 + 12 + 9 + 16 = 53. Dividing by the 1,000 orders gives 5.3% again. That is no accident: a weighted average of rates, weighted by their bases, always equals the total-over-total answer.

Two things to check. The two ranges inside SUMPRODUCT must be the same size, or you get #VALUE!. And divide by the sum of the weights (orders), not by the number of stores. The weighted average guide has more examples, and the SUMPRODUCT function guide explains the syntax.

How to average percentages and skip zeros or blanks#

AVERAGE ignores empty cells and text, but it includes zeros. If a store has no data yet and shows 0%, it will drag the result down. Use AVERAGEIF to leave zeros out:

=AVERAGEIF(D2:D6,">0")

Only do this when a zero really means "no data". A store with a genuine 0% return rate should count. The article on averaging without zeros covers more cases. If a few extreme rates distort the picture, the median may tell a fairer story; see AVERAGE vs MEDIAN.

How to average percentage changes over time#

Growth rates need a different method again. Say sales went from 100 to 150 in year one (+50%) and from 150 to 120 in year two (–20%). AVERAGE of +50% and –20% gives 15% a year. But 100 growing by 15% for two years would be about 132, not 120.

The correct yearly rate is the compound rate:

=(120/100)^(1/2)-1

That gives about 9.5% a year. In a sheet, replace the numbers with cells: end value over start value, raised to 1 divided by the number of years, minus 1. To work out each single-year change first, use =(new-old)/old, which the percent change lesson practises step by step.

Common mistakes when averaging percentages#

• Averaging rates from groups of different sizes when you want the overall rate. Divide the totals instead.
• Dividing SUMPRODUCT by the count of rows. Divide by the total of the weights.
• Mixing text and numbers. Percentages pasted as text are skipped by AVERAGE without warning. Convert them with VALUE or Text to Columns.
• Typing 5 instead of 5%. The cell holds 5, which is 500% once formatted. Type 5% or 0.05.
• Averaging growth rates. Use the compound rate for change over several periods.

Practise it#

Practise AVERAGE, MIN and MAX in a live spreadsheet: you type each formula and it checks your answer instantly. Then try the overall return rate exercise on this page, and work through the percent change lesson to get fast at the percentage formulas that come up in job tests.