How to average percentages correctly in Excel
Formula (Excel and Google Sheets)
=SUM(C2:C6)/SUM(B2:B6)Don't average the rates; divide the totals: =SUM(C2:C6)/SUM(B2:B6) gives the true overall rate.
- Result on the example
- 2.6% in
G1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G1. Change any input and the result updates, or edit the formula to experiment.
How it works
The channel rates are based on very different numbers of visitors. Averaging them gives Social's 400 visitors the same say as Display's 15,000, and returns 5.6%. Dividing total conversions (676) by total visitors (26,000) gives the real overall rate: 2.6%.
=SUM(C2:C6)/SUM(B2:B6)Result0.026
C2:C65 rows × 1 col: 96, 340, 36, 150, …A range: 5 cells in one column.
SUM(C2:C6)676SUM: Adds numbers and ranges. Skips text and blanks.- number1
C2:C65 rows × 1 col: 96, 340, 36, 150, …
- number1
B2:B65 rows × 1 col: 1,200, 8,500, 400, 15,000, …A range: 5 cells in one column.
SUM(B2:B6)26000SUM: Adds numbers and ranges. Skips text and blanks.- number1
B2:B65 rows × 1 col: 1,200, 8,500, 400, 15,000, …
- number1
SUM(C2:C6)/SUM(B2:B6)0.026The / operator divides.
In Google Sheets
The same formula works in Google Sheets: =SUM(C2:C6)/SUM(B2:B6). We ran it in Google Sheets mode on the same data and it returns 2.6%.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(D2:D6,B2:B6)/SUM(B2:B6) | 2.6% | A weighted average of the rates, weighted by visitors. The same answer, for when you only have the rates and the base counts. |
| =AVERAGE(D2:D6) | 5.6%Different result | The simple average of the rates. Returns 5.6%, more than twice the true rate. |
Common mistakes
- The simple average is only right when every rate has the same base. With different bases, weight them.
- Averaging percentage changes over several periods has the same problem; compound growth needs a different calculation.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Average percentages correctly
G1, calculate the overall conversion rate across all channels.Questions
When is a simple average of percentages fine?
When each percentage has the same base, like test scores out of the same maximum, or when you want the typical channel, not the overall rate.
How do I average percentages in a pivot table?
Don't average the rate field; add Conversions and Visitors as sums and create a calculated field that divides them.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.