Cap percentage values between 0 and 100

Cap Percentage between 0 and 100_1

This article will talk about how to cap the percentage values between 0% and 100% in Microsoft Excel Spreadsheet or Google Sheets.

If you are a newbie on Excel or google Sheets, you may be able to do this by manually. For tasks with small amounts of data, this method is possible, but for tasks with large amounts of data, this is not a wise method because it will take a lot of your time.

How to quickly cap a set of percentage values from 0% to 100%? You can now read the following section and we will talk about how to do this with formulas from Microsoft Excel or google sheets.

Limiting percentages by MAX and MIN functions

You can use the MIN function in Google Sheets or Microsoft Excel Spreadsheet in combination with the MAX function to cap a given percentage value between 0% and 100%. Or you can do it with the IF nested functions.

Suppose we have a percentage value of 50%, which is greater than 0 and less than 100%, so it should return that percentage value; if the given percentage value is 120%, it is greater than 100%, so it should return 100%; if the given percentage value is -50%, it is less than 0%, so it should return 0%.

The general formula to achieve this task is as follows.

=MAX(0,MIN(B3,1))

Cap Percentage between 0 and 100_1

By applying this formula, you can always cap the percentage value between 0% and 100%.

Formula explanation

Let’s see how this formula works.

=MIN(B3,1)

MIN function can be used to cap a given percentage value greater than 100%, returning 100%.

Cap Percentage between 0 and 100_1

=MAX(0,B3)

MAX function can be used to limit the given percentage value less than 0, return 0%.

Cap Percentage between 0 and 100_1

Limiting the percentage value by IF nested functions

You can also limit a given percentage value between 0% and 100% by using the IF nested function. The IF nested function is slightly more complex than the MAX/MIN nested function. There are three kinds of logic.

a) limit the value of the percentage does not exceed 100%

by the following IF formula can be used to limit the percentage value greater than 100%, so that the return value of 100%.

=IF(B3>1,1,B3)

Cap Percentage between 0 and 100_1

b) Limit the percentage value not to be less than 0%

The following IF formula can be used to limit the percentage value less than 0, so that its return value is 0%

=IF(B3<0,0,B3)

Cap Percentage between 0 and 100_1

c) Limit the percentage value that is greater than 0 and less than 100% and returns the original percentage value.

The above three selection logics can be satisfied simultaneously by the following nested IF formulas.

=IF(B3>1,1,IF(B3<0,0,B3))

Cap Percentage between 0 and 100_1

From the screenshot above, you can see that the IF nested formula returns the same value as the MAX/MIN formula.

Related Functions

  • Google Sheets IF function
    The Google Sheets IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Google Sheets and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value],
  • Google Sheets MIN function
    The Google Sheets MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Google Sheets and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….
  • Google Sheets MAX function
    The Google Sheets MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.= MAX(num1,[num2,…numn])…

Related Posts

Calculate Cap Percentages to Specific Value

This article will talk about how to limit the cap percentage of a given amount to a given value in Microsoft Excel Spreadsheet or Google Sheets. If you are a newbie on Excel or google Sheets, you may be able ...

Calculate Compound Interest using FV Function in Google Sheets

This article will show you how to calculate compound interest in Google Sheets. It will help you calculate the interest you will accrue on a given amount of money. There are several ways to calculate compound interest Google Sheets. One ...

Count Attendance and Absence with Google Sheets COUNTIF

In our campus life and work life, we usually record everyone's attendance. Today we will introduce you the application of Google Sheets COUNTIF function to count the attendance. Using a week as an example, we use the following example to ...

Find the Closest Data to the Data Provided in Google Sheets

In our daily work, we may encounter such an issue that to find the closest value to a certain value. In fact, google sheets internal functions can help us solve this problem. In today’s article, we will show you how ...

Basic Array Formula With Examples in Google Sheets

Are you searching for an article for getting the basic array formula with different examples for better understanding? Then congratulations because you have just landed on the right article. In this article, you would get to know the basic array ...

Average Of Numbers With Multiple Criteria in Google Sheets

Have you ever come across a task to calculate the average of the numbers with respect to multiple criteria in google sheets? Are you tired of doing this cumbersome task manually? Are you willing to do this task smartly in ...

Calculate Average Of Last 5 Or N Values In Columns in Google Sheets
Average last N values in columns in google sheets1

Suppose you come across a task where you need to calculate the average of the last 2 or 3 numeric values in google sheets, then what would you do? If you are new to google sheets, then your first attempt ...

Find And Retrieve Missing Values in Google Sheets
FIND AND RETRIEVE missing values1

Find and retrieve is a common basic operation in Google Sheets for tables. In out daily work, we may encounter this situation that some data was lost after several operations on a table in Google Sheets. In fact, we can ...

Calculating Average Of The Numbers in Google Sheets

Are you weary of investing a lot of time and effort in manually calculating the average of the numbers by including or excluding 0 and calculating the average of the top 3 scores? Then congratulations because you have just landed ...

Calculate The Average Of The Last 3, 5, Or N Numeric Values In Google Sheets

As an Google sheets user, you might have come across a task in which you need to calculate the average values of the last 2 numeric values, and you might have done this task manually but suppose if the last ...

Sidebar