This tutorial will demonstrate how to use the excel formula not to exceed a certain value. Sometimes, when dealing with lots of data, it becomes very necessary to set a certain range from where the data won’t exceed. Any company or educational institution sets this kind of bar set for measuring excellence level or profit margin. In this article, we will use different formulas like **Data Validation**,** MAX**, **MIN**, **RANDBETWEEN**, and **IF **functions to set a value that we should not exceed.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from here.

## 6 Easy Methods to Use Excel Formula Not to Exceed a Certain Value

We’ll use a sample dataset overview as an example in Excel to understand easily. For instance, we have a dataset of people with their **Shirt Brand Name **in **Column B,** **Size **in **Column C, **and** Price** in** Column D**. If you follow the steps correctly, you should learn how to use the excel formula not to exceed a certain value on your own. The steps are

### 1. Using Data Validation Value Range

In this case, our goal is to use **the Data Validation** option to not exceed a certain value. When we want to limit input to a cell or a range, then we use **Data Validation**. This feature is most useful in times of data collection. This ensures the data is error-free. For example, if we want to record the age of customers in a cell range, then we can set the Data Validation to be numerical values only. Moreover, we can set upper and lower limits for a particular type of data and it will not accept any values beyond that. We can simply return a message to the user giving clues about what is recommended when our data entry is more lenient. Additionally, Excel allows us to enter custom functions to achieve complex conditions using this feature. The steps are:

**Steps:**

- First, select the range Size > go to
**Data**Tab >**Data Validation**options.

- Next, the Data Validation window will pop up on the screen. In
**Settings**section, select**Whole Number**(In**Validation Criteria Allow**) >> between (In**Data**)>>**100**(**Minimum**)>>**200**(**Maximum**) options.

- After that, in
**Error Alert**Section, select**Warning**(In**Style drop-down menu**)>>**Out_of_Range**(In**Title**)>> Please Enter a value between**100**–**200**(In**Error message**) options.

- Then, if you put a value outer the range the following warning will come on the screen.

- Finally, you have to insert the values manually and if it remains in the range, you will get the result and if you repeat the process for all the cells then, you will get the desired result.

### 2. Use of Data Validation Custom Formula

Now, we want to use the **Data Validation Custom formula** to set the desired range that we will not allow exceeding. **Data Validation** Feature offers custom formulas to maintain a certain range of values. **AND** combines two arguments and results as one. In Validation Criteria Custom, we use **AND** to insert two arguments; the first one is minimum the other one is maximum. The steps of this method are as follows.

**Steps:**

- First, like the previous method, select the range Size > go to
**Data**Tab >**Data Validation**options. - Second, the
**Data Validation**window will pop up on the screen. In the**Settings**section, insert the following formula.

`=AND(D5>=100,D5<=200)`

- Last, after clicking
**OK**, you have to insert the values manually and if it remains in the range, you will get the result for this cell, and then repeat the whole process for all the cells and you will get the desired result.

### 3. Utilizing MAX Function

Next, we want to use **the MAX function** to set the desired range that we will not allow to exceed. **The MAX Function** is categorized under **Excel STATISTICAL functions**. This function returns the largest value in a given list of arguments. The steps of this method are as follows.

**Steps:**

- To begin with, arrange the dataset like the below image.

- In addition, insert the following formula in cell
**E5**.

`=MAX(0.8*D5,90)`

- Furthermore, after pressing the Enter button, you will get the result for this cell. Moreover, use the
**Fill Handle**option to use the formula for all the cells.

- Finally, you will get the desired result.

### 4. Applying MIN Function

This time, we want to use** the MIN function** to set the desired range that we will not allow exceeding. In Microsoft Excel, the **MIN function** is generally used to extract the lowest or smallest value from a range of cells containing numerical data only. In this article, you’ll get to learn how you can use this **MIN function** effectively in Excel with appropriate illustrations. The steps of this method are as follows.

**Steps:**

- Firstly, arrange the dataset like the below image.

- Secondly, insert the following formula in cell
**E5**.

`=MIN(0.8*D5,140)`

- Thirdly, after pressing the
**Enter**button, you will get the result for this cell. - Fourthly, use the
**Fill Handle**option to use the formula for all the cells.

- Lastly, you will get the desired result.

### 5. Using the IF Function

We can also **the IF function** to set the desired range that we will not allow to exceed. When you are performing complicated and powerful data analysis, you need to justify various conditions at a single point in time. In Microsoft Excel, **the IF function** acts as a powerful tool to work on conditions. The steps of this method are as follows.

**Steps:**

- To begin with, arrange the dataset like the below image.

- In addition, insert the following formula in cell
**F5**.

`=IF(D5<100,0,IF(D5>200,0,D5))`

- Furthermore, after pressing the
**Enter**button, you will get the result for this cell. Moreover, use the**Fill Handle**option to use the formula for all the cells.

- Finally, you will get the desired result.

### 6. Use of RANDBETWEEN Function

Now, we want to use **the RANDBETWEEN** **function **to set the desired range that we will not allow exceeding. For generating values randomly between a maximum and minimum value, we can use **the RANDBETWEEN** function. **RANDBETWEEN** generates random data from top and bottom values. For this case, let’s say we want random sizes of our shirts between a top and bottom value(22-30). The steps of this method are as follows.

**Steps:**

- Firstly, arrange the dataset like the below image.

- Secondly, insert the following formula in cell
**C8**.

`=RANDBETWEEN($C$5,$C$4)`

- Thirdly, after pressing the Enter button, you will get the result for this cell.
- Fourthly, use the
**Fill Handle**option to use the formula for all the cells.

- Lastly, you will get the desired result.

## Things to Remember

- In the case of using the first two methods, the maximum and the minimum value play the most important roles. When inserting the values try to keep that in mind. If you change the range, the result will be different.
- When using formulas, it is important to insert them with the correct syntax. Otherwise, it won’t give any results.
- We suggest you download the excel file and see it while using the formulas for better understanding.

## Conclusion

Henceforth, follow the above-described methods. Hopefully, these methods will help you to use the excel formula not to exceed a certain value. We will be glad to know if you can execute the task in any other way. Follow the **ExcelDemy** website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.