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

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.

Reason to cap the percentage at 100

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.

Limit a Percentage Value Between 0 and 100 - Array Formula

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:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.