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:

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

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

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:

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

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:

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

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