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.

**Table of Contents**Expand

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