How to Use Excel Formula Not to Exceed a Certain Value

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

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 set a limit 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 they remain 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 to exceed. 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.

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

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

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

• 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 to exceed. For generating values randomly between a maximum and minimum value, we can use the RANDBETWEEN function. It 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.

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

Download Practice Workbook

You can download the practice workbook from here.

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

Related Articles

• How to Make a Data Validation List from Table in Excel
• Excel Data Validation Drop-Down List
• Excel Data Validation Drop Down List with Filter
• How to Use Data Validation List from Another Sheet

<< Go Back to Data Validation in Excel | Learn Excel