# How to average percentages correctly in Excel

Don't average the rates; divide the totals: `=SUM(C2:C6)/SUM(B2:B6)` gives the true overall rate.

**Excel:** `=SUM(C2:C6)/SUM(B2:B6)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Channel | Visitors | Conversions | Rate |  | Overall rate |
| Email | 1200 | 96 | =C2/B2 |  |  |
| Search | 8500 | 340 | =C3/B3 |  |  |
| Social | 400 | 36 | =C4/B4 |  |  |
| Display | 15000 | 150 | =C5/B5 |  |  |
| Referral | 900 | 54 | =C6/B6 |  |  |

Formula in G1: `=SUM(C2:C6)/SUM(B2:B6)`

Result: 2.6%

## 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%.

## Other ways

- `=SUMPRODUCT(D2:D6,B2:B6)/SUM(B2:B6)`: 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)`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/average-percentages-correctly