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


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

Dataset to Use Excel Formula Not to Exceed a Certain Value


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.

Using Data Validation option to Use Excel Formula Not to Exceed a Certain Value

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

Data validation dialog box to Use Excel Formula Not to Exceed a Certain Value

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

Setting Error Alert to Use Excel Formula Not to Exceed a Certain Value

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

Warning is working to Use Excel Formula Not to Exceed a Certain Value

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

Result to Use Excel Formula Not to Exceed a Certain Value


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)

Inserting Formula to Use Excel Formula Not to Exceed a Certain Value

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

Result to Use Excel Formula Not to Exceed a Certain Value


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)

Inserting Formula to Use Excel Formula Not to Exceed a Certain Value

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

Inserting Formula to Use Excel Formula Not to Exceed a Certain Value

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

Inserting Formula to Use Excel Formula Not to Exceed a Certain Value

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

Final Result After Inserting Formula to Use Excel Formula Not to Exceed a Certain Value


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)

Inserting Formula to Use Excel Formula Not to Exceed a Certain Value

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

Showing Result After Inserting Formula to Use Excel Formula Not to Exceed a Certain Value


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.

Zehad Rian Jim

Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo