Sometimes, you need to limit the percentage values between 0 and 100 in Excel. That means if you have a percentage over 100 or less than 0, it will take the percentage under this range. This article will show you how to cap percentage values between 0 and 100 in Excel using some effective Excel functions. I hope you find this article really interesting and gain lots of knowledge regarding the topic.
What Is Cap Percentage?
Cap percentage can be defined as a percentage limit in which your dataset percentage will be present. To be precise, by using a cap percentage, you can limit your given percentage to a certain level. For example, you want to cap percentage values between 10 and 50. So, if your value is between 10 and 50, then it will show the original percentage. Otherwise, it will show the highest and lowest value. If you have a percentage of 0 in your dataset, the cap percentage will show it as 10 because that’s what you define in the cap percentage. Again, if you have a percentage of 100 in your dataset, it will also show as 50 in the cap percentage section. Because this is the highest cap percentage.
What Is Capping Formula in Excel?
The capping formula can be defined as a formula that restricts the cell value to a particular value. Sometimes, we need to restrict our limit to a particular value. At that time, you can utilize the capping formula in order to solve the issue. For example, you get $50 per hour of work. But, if you work extra time, you will get $80 per hour. But we need to restrict the payment of one day up to $500.
First, take a dataset that contains several employees and their working hours’ details. We need to calculate the total income and also restrict the income to a limit of $500. Select a cell and write down the following formula.
=MIN(C6*$H$4+D6*$H$5,500)
After that, press Enter to apply the formula. Here, the final income is $500. This is because we restrict the values up to $500. If we calculate accurately, the income of Noah’s on that certain day will be $560.
Finally, drag the Fill Handle down the column. You will find in every case, the highest limit of income is $500.
How to Cap Percentage Values Between 0 and 100 in Excel: 3 Suitable Methods
We have found 3 suitable methods to cap percentage values between 0 and 100 in Excel. All of these methods are fairly easy to understand. We would like to utilize several Excel functions such as MAX, MIN, IF, and MEDIAN. All of these functions provide us with effective results. In order to get the desired result, we have to use the combination of MAX and MIX functions.
1. Combination of MAX and MIN Functions
Our first method is based on the combination of MAX and MIN functions to cap percentage values between 0 and 100 in Excel. Here, the MIN function can be utilized to cap the percentage values which are greater than 100%, providing 100%. While the MAX function can be capitalized to cap the percentage values which are less than 0%, providing 0%. We take a dataset that contains several input percentage values. To understand the process, follow the steps carefully.
Steps
- Now, we want to create output values of corresponding input values by using the cap percentage between 0 and 100.
- Select cell C5.
- Then, write down the following formula.
=MAX(0,MIN(B5,1))
- After that, press Enter to apply the formula.
- Then, drag the Fill Handle icon down the column.
- As result, you will the percentage values which are less than 0% and greater than 100%, they will become 0% and 100% In this way, you make a percentage equal to 100 in Excel when the value is greater than 100.
- Subsequently, we can use the Excel formula not to exceed a certain value.
🔎 Breakdown of the Formula
MAX(0,MIN(B5,1)): Here, the MAX function can be capitalized to cap the percentage value in cell B5 if it is less than 0%, providing 0%. Then, the MIN function can be utilized to cap the percentage value in cell B5 if it is greater than 100%, providing 100%. You can utilize the formula for any other cell values.
2. Utilizing IF function
Our next method is based on the IF function to cap percentage values between 0 and 100 in Excel. Here, we utilize the IF function to limit the percentage values greater than 100%, returning 100%. Also, we use the IF function to limit the lowest percentage to 0% if there is a percentage value less than 0%. We take a dataset that contains several input percentage values. To understand the process, follow the steps carefully.
Steps
- Now, we want to create output values of corresponding input values by using the cap percentage between 0 and 100.
- Select cell C5.
- Then, write down the following formula.
=IF(B5>1,1,IF(B5<0,0,B5))
- After that, press Enter to apply the formula.
- Then, drag the Fill Handle icon down the column.
- As result, you will the percentage values which are less than 0% and greater than 100%, they will become 0% and 100% In this way, you make a percentage equal to 100 in Excel when the value is greater than 100.
- Thus, we can use the Excel formula not to exceed a certain value.
🔎 Breakdown of the Formula
IF(B5>1,1,IF(B5<0,0,B5)): Here, the IF function checks whether cell B5 is greater than 100% or not, then, it will set 100% as the highest value. Otherwise, it will go to another IF function. In that IF function, it checks whether the cell value B5 is less than 0% or not. For the case of less than 0%, it will return 0%. Otherwise, it will return the cell value B5.
3. Use of MEDIAN Function
Our next method is based on using the MEDIAN function to cap percentage values between 0 and 100. This function helps us to limit the percentage values between 0 and 100. So, if there is a value less than 0 or greater than 100, it will show 0% and 100% respectively. We take a dataset that contains several input percentage values. To understand the process, follow the steps carefully.
Steps
- Now, we want to create output values of corresponding input values by using the cap percentage between 0 and 100.
- Select cell C5.
- Then, write down the following formula.
=MEDIAN(0,1,B5)
- After that, press Enter to apply the formula.
- Then, drag the Fill Handle icon down the column.
- As result, you will the percentage values which are less than 0% and greater than 100%, they will become 0% and 100% In this way, you make a percentage equal to 100 in Excel when the value is greater than 100.
- Subsequently, we can use the Excel formula not to exceed a certain value.
🔎 Breakdown of the Formula
MEDIAN(0,1,B5): The MEDIAN function returns the median or the number in the middle of the set of given numbers. Here, we denote the range of 0% to 100% for cell B5. As the cell value is 25%, the function returns 25% because it is in between the given range, For other cases where the values are less than or greater than 0% and 100%, it will return 0% and 100% respectively as the highest value.
💬 Things to Remember
- As we use the nested IF functions to cap percentage values between 0 and 100, so, you need to be careful while using it and also provide effective brackets.
- In order to get an effective result, you need to consider the cap percentage range first.
Download Practice Workbook
Download the practice workbook below.
Conclusion
To cap the percentage between 0 and 100 in Excel, we have shown 3differenent methods through which you can do the work. In this article, we utilized several Excel functions to create an efficient cap percentage between 0 and 100 in Excel. I hope we covered all possible areas of this topic. If you have any questions, feel free to ask in the comment box.
Related Articles
- Excel MIN and MAX in Same Formula
- How to Set a Minimum and Maximum Value in Excel
- Find Max Value and Corresponding Cell in Excel
- How to Find Maximum Value in Excel with Condition
- How to Find Max Value in Range with Excel Formula
<< Go Back to Excel MAX Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
