Editor choice

# 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Â

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

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)

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)

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.

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

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

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

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.

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

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

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.

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

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

Step 2: Hit ENTER.Â

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.

Â

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

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

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

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

Step 2: Press ENTER.Â

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

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

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

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.

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