You may want to limit a percentage value between 0 and 100 in your Spreadsheet to solve some problems. In Google Sheets, you can use several formulas for this.

When I calculate the elapsed time between two dates (a start date and an end date) in Google Sheets, I encountered one issue.

What’s that issue?

When the start date is a future date, the percentage returned is a minus value.

On the other hand, if the end date is less than the evaluation date, the percentage returned is >100%.

It may cause issues if you want to create the SPARKLINE in-cell Bar chart, as shown below.

See task 5 sparkline in F8. Actually, we don’t want a bar there as the task is yet to be started.

In the above example, we may require to cap the percentage at 100 in cell E5.

In cell E8, we may want to limit the percentage between 0 and 100.

Let’s see how to cap/limit a percentage between 0 and 100 in Google Sheets.

Formulas to Limit Percentage between 0 and 100 in Google Sheets

We will see the time elapsed percentage formula and sparkline in another tutorial.

Here, to cap the percentage, we will only consider the percentage values in column E, not the underlying date formulas.

Here are the formula options.

1. IF Logical Test

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

In cell F3, enter this formula and copy-paste it down.

Note:- I have included the formula explanations as a separate section below.

2. Array Formula IF Logical Test

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

It will cap the percentage values for all the rows down.

3. Min and Max

=max(min(E3,100%),0%)

This formula is also for cell F3, which should be copy and pasted down.

4. Median

=to_percent(median(0%,100%,F3))

Another formula to cap the percentage value is the above Median in cell F3. You should drag/copy this formula downwards.

There are four formulas above to limit percentage values between 0 and 100 in Google Sheets.

I would recommend the second formula as it can expand.

Let’s see how the formulas cap the % values in Google Sheets.

Formula Explanations

I am starting with formulas 1 and 2, which are logical tests.

Using IF Function to Limit the Percentage Value Between 0 and 100

1. IF Logical Test – Explanation

In the first formula, as you can see, I have used the Google Sheets IF function to test whether the % value is between 0% and 100%.

There are two logical tests involved/nested.

a. If % in E2 <0%, return 0%.

b. If % E2 >100%, return 100%.

c. Else return the E2 % value.

With the logical tests, I have used the To_Percent function to retain the percentage format. Otherwise, the result may be in number format.

2. Array Formula IF Logical Test – Explanation

Here, I have additionally used one more logical test for excluding the evaluation in blank rows. Because the range in the formula is open/infinite, and so used the ArrayFormula function.

a. If percentages values in E2:E =””, return blank.

b. If percentages values in E2:E <0%, return 0%.

c. If percentages values in E2:E >100%, return 100%.

d. Else return the E2:E percentage values.

Min and Max Logic

3. Min and Max Formula – Explanation

If you don’t prefer an array formula, the easiest way to limit a percentage value between 0 and 100 is the Min and Max combo, not the logical tests.

Since this combo retains the % formatting, we don’t want to use the To_Percent with it.

The MIN evaluates two values which are the value in E2 and 100%. Then return the min value.

It ensures that the returned % is always less than or equal to 100%. This way, we can cap the percentage at 100 in Google Sheets.

What’s the role of the MAX then?

Here also, there are two values to evaluate – The Min output and 0%.

The Max will return the E2 value if it is greater than or equal to 0%, else 0%.

Median to Cap the Percentage Values in Google Sheets

4. Median – Formula Explanation

The Median function takes a numerical dataset to return the middle number in it.

Here the numerical dataset contains only three percentage values, and they are 0%, the % value in E2, and 100%.

So the median will always return a percentage value between 0% and 100%.

The above four are the formula options to limit a percentage value between 0 and 100 in Google Sheets.

Resources:

• How to Create Percentage Progress Bar in Google Sheets.
• How to Use Percentage Value in Logical IF in Google Sheets.
• Query to Filter a Column Contains Percentage Values in Google Sheets.
• How to Use the UNARY_PERCENT Function in Google Sheets.
• Calculating the Percentage of Total in Google Sheets [How To].
• Percent Distribution of Grand Total in Google Sheets Query.
• Percentage Change Array Formula in Google Sheets.
• How to Round Percentage Values in Google Sheets.
• How to Get the Top N Percent Scores From Each Group in Google Sheets.