What Is Cap Percentage?
Cap percentage can be defined as a percentage limit in which your dataset percentage will be contained.
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?
When you need to restrict a cell value to a specific limit, the capping formula comes in handy. Hereâ€™s how you can use it:
 Scenario:
 Imagine you earn $50 per hour of work, but if you work extra hours, you get $80 per hour.
 However, you want to cap your daily payment at $500.
 Example:
 Suppose you have a dataset with employees and their working hours.
 You want to calculate the total income for each employee while ensuring it doesnâ€™t exceed $500.
 Formula:
 In a cell, insert the following formula:
=MIN(C6*$H$4+D6*$H$5,500)

 Here:
 C6Â represents the regular hours worked.
 $H$4Â is the hourly rate ($50 in this case).
 D6Â represents any extra hours worked.
 $H$5 is the extra hourly rate ($80).
 The MINÂ function ensures the result doesnâ€™t exceed $500.
 Here:

 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.
Method 1 Combination of MAX and MIN Functions
 This method allows us to cap percentage values effectively.
 The MIN function restricts values greater than 100% to 100%, while the MAX function ensures values less than 0% become 0%.
Steps
 Hereâ€™s the formula for cell C5:
=MAX(0,MIN(B5,1))
 Press Enter to apply the formula.
 Drag the Fill Handle icon down the column.
 As a result, the percentage values that are less than 0% and greater than 100%, will become 0% and 100%. Using this method, you make a percentage equal to 100 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 used to cap the percentage value in cell B5, if it is less than 0%, providing 0%.
 The MIN function can be used to cap the percentage value in cell B5, if it is greater than 100%, providing 100%.
Method 2 – Utilizing IF function
Another approach involves using the IF function to limit percentages.
We’ll use a dataset that contains several input percentage values.
 In cell C5. insert the following formula:
=IF(B5>1,1,IF(B5<0,0,B5))
 Press Enter to apply the formula.
 Drag the Fill Handle icon down the column.
 As a result, the percentage values that are less than 0% and greater than 100%, became 0% and 100%.Â
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, if it is higher than 100%, it will return 100%.Â Otherwise, it will return the cell value of B5.
 The 2nd IF functionÂ checks whether the cell value B5 is less than 0% or not. If less than 0%, it will return 0%. Otherwise, it will return the cell value of B5.
Method 3 – Using MEDIAN Function
The MEDIAN function can also help cap percentages.
We’ll use a dataset that contains several input percentage values.
 Select cell C5 and insert the following formula:
=MEDIAN(0,1,B5)
 Press Enter to apply the formula.
 Drag the Fill Handle icon down the column.
 As a result, the percentage values that are less than 0% and greater than 100%, became 0% and 100%.
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.
Download Practice Workbook
You can download the practice workbook from here:
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!