# How to cap a percentage at 100% in Excel

Wrap the ratio in MIN with 1, since 100% is 1: `=MIN(B2/C2,1)`.

**Excel:** `=MIN(B2/C2,1)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Rep | Sales | Target | Attainment (capped) |
| Ana | 48200 | 50000 |  |
| Ben | 61750 | 55000 |  |
| Cara | 39900 | 60000 |  |
| Dev | 50000 | 50000 |  |
| Eli | 72000 | 60000 |  |

Formula in D2, filled to D6: `=MIN(B2/C2,1)`

Result: 96.4%; 100.0%; 66.5%; 100.0%; 100.0%

## How it works

100% is stored as the number 1. MIN returns the smaller of the ratio and 1, so anything above target becomes exactly 100% and anything below stays as it is. Ben (112%) and Eli (120%) are capped; Ana stays at 96.4%.

## Other ways

- `=IF(B2/C2>1,1,B2/C2)`: The same cap written as IF; easier to read for some, but it calculates the ratio twice.
- `=B2/C2`: The uncapped attainment. (Shows Ben at 112.3% and Eli at 120%.)

## Common mistakes

- `=MIN(B2/C2,100)` doesn't cap anything: 100 is 10,000%. Use 1 or 100%.
- Capping changes averages: the average of capped attainments understates how far the team beat its targets. Keep an uncapped column for analysis.

## Questions

**How do I keep a percentage between 0% and 100%?**

Use MAX and MIN together: `=MAX(0,MIN(B2/C2,1))`.

**How do I cap at 120% instead?**

Use 1.2: `=MIN(B2/C2,120%)`.

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/cap-a-percentage-at-100