Nashid
In this lesson, I’ll demonstrate 5 effective approaches on how to set a minimum and maximum value in Excel. Moreover, these techniques will enable you to solve the problem that led you here. However, you will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.
📁 Download Excel File
The sample worksheet that was used during the discussion is available to download for free right here.
Learn to Set a Minimum and Maximum Value in Excel with These 5 Useful Techniques
Here in this article, we will learn how to set a minimum and maximum value in Excel using different approaches. To be exact, I’ve provided a total of 5 methods down below.
1. Using MAX and IF Functions
Here we will be using IF and MAX functions to determine a minimum and maximum value. For instance, let’s say we have a dataset that says the height of different candidates we want to select with a minimum height of 5.8” and want to find out the tallest candidate.
The MAX function works like the box provided below.
=MAX(Array/Number(1),Array/Number(2),….)
👉 Provides the result of the highest number among Array/Number(1) and Array/Number(2) and …..
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, copy the following formula.
=IF(C6<5.8,”Disqualified”,IF(C6=MAX($C$6:$C$14),”Highest”,C6))
🔨 Formula Breakdown
👉 MAX($C$6:$C$14)
The MAX function finds the highest value in the array $C$6:$C$14.
👉 IF(C6=MAX($C$6:$C$14),”Highest”,C6)
This part says if C6=MAX($C$6:$C$14) is true then return Highest else return its own value C6.
👉 IF(C6<5.8,”Disqualified”,IF(C6=MAX($C$6:$C$14),”Highest”,C6))
If C6<5.8 is true the formula will return Disqualified and if not true then it will run the previous point’s logic.
- Press Enter and copy down along the column for all the results.
2. Using MIN Function
In this section, we will be determining the discounted price for products using the MIN function. For instance here the case is no product will have a price of more than 150$.
The MIN function works like the box provided below.
=MIN(Array/Number(1),Array/Number(2),….)
👉 Provides the result of the smallest number among Array/Number(1) and Array/Number(2) and …..
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, copy the following formula in the formula bar.
=MIN(C6,150)
- Now press Enter to get the results.
- After that, copy down the formula for all the cells
3. Using RANDBETWEEN Function
In this part, we will be determining random numbers between a maximum number and a minimum number using the RANDBETWEEN function. Here we will be determining the possible heights of candidates between 198 cm and 170 cm.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly copy the following formula.
=RANDBETWEEN($F$7,$F$6)
- Press Enter and copy down along the column for all the results.
4. Using IF Function
In this section, we set a minimum and maximum value to calculate the income of employees using the IF function. In this part, we are setting some conditions as the basic work limit is 8 hrs minimum. Working below 8 hrs no one will get any payment and the payment after the working hours will be 20% extra per hour.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, copy the following formula I’ve created for you to the formula bar for the working hour.
=IF(C6<8,0,IF(C6>10,”10Hr+Overtime”,C6))
- After pressing Enter, copy down the formula for the whole column.
- After that, copy the following formula I’ve created for you to the formula bar for the salary.
=IF(D6<80,0,IF(D6>80,D6+D6*0.2,D6))
- Press Enter and copy down along the column for the rest of the results.
5. Using Data Validation
In this section, we will learn how to use Data Validation to set a minimum and maximum value in Excel.
5.1 Using Value Range
Here we will be using the Value Range to set up input criteria. Follow the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select all the cells.
- Then go to the Data Validation option in the Data Validation group from the Data tab of the ribbon. It’s a Data Tool.
- Now a new window will appear where we will be defining a range. First, set the Allow option to Whole number. Then set Minimum and Maximum numbers and go to the Error Alert.
- Here, first, set the Style to Warning. Then set a Title and an Error Message that will be shown if the inserted value is not between 8 to 15.
- Now we can start inserting our data. If we insert data that is not within 8 to 15 an error message will appear as we have set earlier. See here we have an input of 6 which is not in our range so the error message showed up. Now we can either overwrite it by pressing Yes or edit the value by pressing No.
- The same goes for the Price column, we can set a range for this value by the same process.
5.2 Using Custom Formula
Here we will be using a custom formula to set up input criteria. Here we will be using the AND function to define a formula. Follow the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, Select the whole column for data validation.
- Then Go to the Data Validation option in Data Validation from the Data tab of the ribbon. It’s a Data Tool.
- After that, select Custom from the dropdown button of the Allow. Then insert the formula in the Formula option after that go to the Error Alert option. As we want the price to be between 150-200 the formula will be as shown below.
=AND(D6>=150,D6<=250)
- Here First set the Style to Warning. Then set a Title and an Error Message that will be shown if the inserted value is not between 150 to 200.
- Now while inserting data if a value is not between our range an error message will be shown. See here we have an input of 145 which is not in our range so the error message showed up. Now we can either overwrite it by pressing Yes or edit the value by pressing No.
- Thus we have set a Maximum and a Minimum value as the input criteria of a dataset.
📄 Important Notes
🖊️ Use shortcuts to reduce the time for instance F4 for absolute value
🖊️ While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.
🖊️ Be careful while defining criteria to be accurate as the dataset.
📝 Takeaways from This Article
📌 Use of RANDBETWEEN, MIN, IF, and MAX functions.
📌 How to generate random values between two numbers..
📌 Creating formulas using multiple functions.
📌 How to set up multiple criteria with different functions.
📌 How to use Data Validation for defining input criteria.
Conclusion
Firstly, I hope you were able to use the techniques I demonstrated in this How to Set a Minimum and Maximum Value in Excel lesson. As you can see, there are a lot of options on how to do this. However, decide deliberately on the approach that best addresses your circumstance. Most importantly I advise repeating the steps if you become confused in any of the steps if you get stuck. However, practice on your own after taking a look at the Excel file in the practice workbook I’ve provided above because practice makes a man perfect. Above all, I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks. However, if you have any problem regarding Excel spit it in the comment box. The Excelden crew is always available to answer your questions. In conclusion, keep educating yourself by reading. For more Excel-related articles please visit our website Excelden.com.
