HomeGoogle DocsSpreadsheetHow to Limit a Percentage Value Between 0 and 100 in Google...

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

Published on

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 KVhttps://infoinspired.com/about-me/
Introducing 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.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here