How to calculate each value as a percent of the maximum in Excel
Formula (Excel and Google Sheets)
=B2/MAX($B$2:$B$6)Divide each value by the largest one, locked: =B2/MAX($B$2:$B$6). The top value becomes 100%.
- Result on the example
- one result per row in
C2, filled down toC6 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in C2, filled down to C6. Change any input and the result updates, or edit the formula to experiment.
How it works
MAX finds the best result, 120,000, and every value is divided by it, so the leader shows 100% and the others show how close they came. The range is locked with $ so the maximum stays the same in every row.
=B2/MAX($B$2:$B$6)Result0.7
B2$84,000The value in B2.
$B$2:$B$65 rows × 1 col: $84,000, $120,000, $96,000, $60,000, …A range: 5 cells in one column.
MAX($B$2:$B$6)120000MAX: Largest number.- number1
$B$2:$B$65 rows × 1 col: $84,000, $120,000, $96,000, $60,000, …
- number1
B2/MAX($B$2:$B$6)0.7The / operator divides.
In Google Sheets
The same formula works in Google Sheets: =B2/MAX($B$2:$B$6). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =B2/LARGE($B$2:$B$6,1) | 70%, 100%, 80%… | LARGE with 1 is the maximum; change it to 2 to compare with the runner-up. |
Common mistakes
- If the largest value is negative or zero, the percentages are meaningless. Use this for positive measures.
- Percent of maximum is not percent of total: the column doesn't add up to 100%.
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.
Calculate each value as a percent of the maximum
C2, calculate each rep's sales as a percentage of the best rep's sales, then fill down to C6.Questions
How do I scale so the lowest is 0% and the highest 100%?
Use min-max scaling: =(B2-MIN($B$2:$B$6))/(MAX($B$2:$B$6)-MIN($B$2:$B$6)).
How do I compare with a fixed best score, like 50 points?
Divide by that number: =B2/50.
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.