# How to Cap Percentage Values Between 0 and 100 in Excel (3 Methods)

## 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.

• 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.

You can download the practice workbook from here:

## Related Articles

<< Go Back toÂ Excel MAX Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF