How to Set a Minimum and Maximum Value in Excel (6 Easy Methods)

In a dataset sometimes we need to input entries between ranges. In that case, entries between one minimum and one maximum value represent a whole dataset. In order to do so, we can manage the data set in a certain way that we can not put any entries out of the range. In this article, we use Data Validation, MAX, MIN, RANDBETWEEN, and IF functions to set a maximum and minimum value.

Let say, we have a data set depicting Sizes and Prices of various samples of shirts. The dataset looks like the picture below 

dataset

Dataset for Download

6 Easy Ways to Set a Minimum and Maximum Value in Excel

Method 1: Using Data Validation Value Range

Data Validation feature offers to insert a certain value between a specific range (Minimum & Maximum) in a cell or a range of cells. We can make a custom Warning window pop up in case the user does not comply. 

Step 1: Select the range Size. Go to Data Tab> Data Validation drop-down menu (In Data Tools Section)

data validation

Step 2: Click Data Validation. A window pops up. In Settings Section,Select Whole Number (In Validation Criteria Allow ) >> between (In Data)>> 22 (Minimum)>>30(Maximum)

data validation

Step 3: In Error Alert Section, Select Warning (In Style drop down menu)>> Out_of_Range(In Title)>> Please Enter a value between 22-30 (In Error message)

data validation

Step 4: Click OK. Begin entering values. Check out if we input values out of range (22-30), the Warning Message we set pops up.

warning data validation

Step 5: Repeat steps 1-4 for range Price & Set the values between 100-200.

data validation values

Step 6: Click OK. Inputting the values we will come up with an image similar to the image below

final data validation

Method 2: Using Data Validation Custom Formula

Data Validation Feature offers Custom formulas to maintain a certain range of values. ADD combines two arguments and results as one. In Validation Criteria Custom, we use ADD to insert two arguments; the first one is minimum the other one is maximum.

Step 1: Again select the range Size. Follow the sequence, Data Tab>> Data Validation drop-down menu (In Data Tools Section)

data validation formula

Step 2: Select Data Validation. A window pops up. Below the Settings Section, Select Custom (In Allow drop-down menu)>> Type

=AND(C4>=22,C4<=30)
(In Formula box) & Repeat the Error Message setting from the previous method.

data validation custom

Step 3: Again Select Range Price. Repeat step 2 but Apply the formula

=AND(D4>=100,D4<=200)

data validation custom feature

Step 4: Click OK. You can check the workability of the Formula by inputting any values out of the range, the warning window pops up.

warning window in data validation formula

And after entering all the entries the dataset looks like below picture

final data validation formula

Method 3: Using MAX Function

In the dataset, in case we want the minimum price offering a discount of 20%, is 90. Then we can use the MAX function to set a minimum price tag. MAX shows the minimum value (in this case 90) in the case of smaller outcomes and shows resultant value in the case of larger outcomes.

Step 1: Select any cell E4. Type the Formula

=MAX(0.8*D4,90)
.

max function

Step 2: Hit ENTER

max function

The discounted price shows up. If the discounted price is smaller than 90 then it shows 90 otherwise the calculated values.  

Step 3: Drag the Fill Handle, the minimum values appear.

max function result

 

Method 4: Using MIN Function

Alternatively, in the case of the dataset, we want the maximum price offering a discount of 20%, which is 140. Then we can use the MIN Function to set a maximum price tag. MIN results in the maximum value (in this case 140) in the case of greater outcomes and shows resultant value in the case of smaller outcomes.

Step 1: Click on any cell E4. Insert the Formula

=MIN(0.8*D4,140)
.

min function

Step 2: Press ENTER. The outcome will be similar to the image below

min function

The resultant price shows up as it’s less than the maximum discounted price, 140, we’ve set in the formula. 

Step 3: Drag the Fill Handle. The Executions of the steps results similar to the picture below

min function result

Method 5: Using IF Function

We desire to restrict the values from viewing in a cell or range out of criteria. Therefore, we can use the IF function to insert two tests. 

The first test is that it judges the number whether it is following the condition or not. If the answer is YES it results in the number, on other incidents it results in 0. For this purpose, we change some entries to see whether 0 is shown or not via this Formula. And we want to insert values equal to or larger than 22/100 and equal or smaller than 30/200

Step 1: Click on any cell E4 or F4. Type the Formula

=IF(C4<22,0,IF(C4>30,0,C4))
 
=IF(D4<100,0,IF(D4>200,0,D4))

if function

Step 2: Press ENTER

if function

Step 3: Drag the Fill Handle & we can see the consequences of these steps from the image below

if function drag

After doing the same step for the Price column, we get the results as the image below

final if function

Method 6: Using RANDBETWEEN Function

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

Step 1: Click on any cell C8.Type the Formula

=RANDBETWEEN($C$4,$C$3)
Make sure you lock the references by pressing F4.

randbetween function

Step 2: Press ENTER. Drag the Fill Handle as down as you want. You will get the random values between 22-30 like in the picture below.

final randbetween

This value will change every time we trigger an operation. Though it provides such the luxury to create a dummy dataset quickly.   

Conclusion

In many cases, maintaining entries between a maximum and minimum value is necessary. As shown in the above article we can achieve these criteria using functions like MAX, MIN, and IF also features like Data validation

Data Validation provides instant restriction from entering data out of the range. MAX, MIN, and IF Functions show the referenced data considering a maximum and minimum value. RANDBETWEEN generates random data within a bottom and top value to make sample datasets. Hope you find the sequence of the above steps lucid to understand and super easy to follow. Comment if this article helps you and saves your time.

Maruf Islam

I, Maruf Islam, an engineer, content writer. I completed my BSc from Bangladesh University Of Engineering & Technology, want to pursue a career in content writing & development.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo