How to Limit a Percentage Value Between 0 and 100 in Google Sheets

In some cases, you may need to limit a percentage value between 0 and 100 in Google Sheets to ensure accurate calculations. Fortunately, there are several formula-based approaches to achieve this.

Why Limit a Percentage Value Between 0 and 100?

When calculating the elapsed time between two dates (a start date and an end date) in Google Sheets, you may encounter issues:

If the start date is in the future, the percentage returned is negative.
If the end date is before the evaluation date, the percentage exceeds 100%.

This can create problems when using a SPARKLINE in-cell bar chart, as shown below.

Why capping the percentage at 100 is necessary

Reason to Cap the Percentage at 100

In the example, Task 5 (cell F8) should not display a bar since the task has not yet started, meaning the current date is earlier than the task start date.

To fix this, we need to cap the percentage at 100 in cell E5 and limit the percentage between 0 and 100 in cell E8.

Note: The following SPARKLINE formula is used in cell F4 and dragged down:

=SPARKLINE(E4, {"charttype","bar";"max",1})

Even though the value is negative in cell E8, SPARKLINE interprets it as a relative proportion within the defined scale (0 to 1). As a result, the bar is displayed as a partial bar, where the length represents the absolute value of the negative percentage relative to the maximum value.

Formulas to Limit a Percentage Value Between 0 and 100 in Google Sheets

While we’ll discuss the elapsed time percentage formula and SPARKLINE chart in another tutorial, here we’ll focus on formulas that cap percentages at 100.

The formulas below are applied to the following percentage values in E3:E7:

76.27%
103.16%
53.85%
100.00%
-55.38%

1. Using the IF Function

=TO_PERCENT(IF(E3<0%, 0%, IF(E3>100%, 100%, E3)))

Enter this formula in F3 and copy it down.
Ensures that any percentage below 0% is capped at 0% and any value above 100% is capped at 100%.

2. Array Formula with IF Logical Test

=ArrayFormula(TO_PERCENT(IF(E3:E<0%, 0%, IF(E3:E>100%, 100%, E3:E))))

This automatically applies to all rows.
It ignores blank cells and limits percentages between 0% and 100% across the entire column.

Array formula to limit a percentage value between 0 and 100

3. Using MIN and MAX

=TO_PERCENT(MAX(MIN(N(E3), 100%), 0%))

A simple alternative to cap percentages between 0 and 100 without an IF function.
Ensures values are always within the 0%–100% range.

4. Using the MEDIAN Function

=TO_PERCENT(MEDIAN(0%, 100%, N(E3)))

The MEDIAN function selects the middle value from three options:
- 0%
- The percentage in E3
- 100%
Guarantees that the output is always within 0% and 100%.

Which Formula Should You Use?

For a single cell formula, use MIN and MAX.
For an expandable formula, the Array Formula IF approach is best.
For a simple alternative, use MEDIAN.

Each method effectively caps percentages between 0 and 100 in Google Sheets, ensuring accurate calculations.

Formula Explanations

1. Using IF to Limit a Percentage Value Between 0 and 100

The IF function checks three conditions:

If the percentage is less than 0%, return 0%.
If the percentage is greater than 100%, return 100%.
Otherwise, return the original value.

Using TO_PERCENT ensures the result stays formatted as a percentage rather than a decimal.

2. Using an Array Formula with IF

This formula expands across the column and adds an additional condition:

It follows the same logic as the basic IF formula.

3. Using MIN and MAX to Cap Percentage Values

MIN(N(E3), 100%): Ensures the percentage does not exceed 100%. The N function returns 0 if the cell is empty, preventing the formula from incorrectly returning 100% for blank cells.
MAX(MIN(N(E3), 100%), 0%): Ensures the result is at least 0%, preventing negative percentage values.
TO_PERCENT(MAX(MIN(N(E3), 100%), 0%)): Converts the final result into percentage format.

4. Using MEDIAN to Cap Percentages

MEDIAN(0%, N(E3), 100%) selects the middle value from the three numbers:
- If E3 is below 0%, it returns 0%.
- If E3 is above 100%, it returns 100%.
- Otherwise, it returns E3.
A simple one-liner solution to limit a percentage value between 0 and 100.

Conclusion

To cap a percentage between 0 and 100 in Google Sheets, you can use IF statements, MIN and MAX, or MEDIAN functions. Each method ensures that percentage values remain within a valid range, preventing calculation errors in financial models, SPARKLINE charts, and progress tracking.