How to cap a percentage at 100%
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Progress bars and completion metrics that shouldn't read 137%.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =MIN(1,A2/B2) | A2=actual, B2=target; format as %. MIN picks the smaller of the ratio and 100%. |
| Google Sheets | =MIN(1,A2/B2) | Identical. |
| LibreOffice Calc | =MIN(1,A2/B2) | Identical. |
How it works
MIN(1, ratio) lets normal progress through untouched and flattens anything past the target to exactly 100%: 137/100 caps at 1. The mirror MAX(0,...) floors negatives, and both together — MAX(0,MIN(1,A2/B2)) — is the standard progress-bar clamp. Cap the DISPLAY only (keep 137% in the data, show 100% in the bar) by clamping in the chart's helper column, not the source.
Verified, not just documented
We ran =MIN(1,A2/B2) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 1 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 1 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
MIN — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to calculate overtime pay
- How to calculate the range (max minus min)
- How to clamp (limit) a value between a minimum and maximum
- How to normalize data to a 0-1 scale