In a dataset sometimes we need to input entries between ranges. In that case, entries between the minimum and maximum values represent a whole dataset. This article will demonstrate how to set a minimum and maximum value in Excel.
Before starting the article. Let’s have a look at the methods we are using and the outputs from the corresponding methods.
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. So, let’s start.
Download Practice Workbook
4 Easy Ways to Set a Minimum and Maximum Value in Excel
Let’s say, we have a data set depicting the Sizes and Prices of various samples of shirts. The dataset looks mostly like the picture below.
We can manage the data set in a certain way so that we can not put any entries out of the range. In this article, we use Data Validation feature, MAX, MIN, SMALL, LARGE, and RANDBETWEEN functions to set maximum and minimum values.
Method 1: Using Data Validation Feature
Let’s say, we need to fill the Size and Price column of our dataset.
Check how you can fill this range with the Data Validation feature.
1.1. Using 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.
- Select the range Size. Go to Data Tab> Data Validation drop-down menu (In Data Tools Section).
- Click Data Validation.
- A window pops up. In the Settings section, select Whole Number (In Validation Criteria Allow ) >> between (In Data) >> 22 (Minimum) >> 30(Maximum)
- 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)
- Click OK. Begin entering values. Check out if we input values out of range (22-30), the Warning Message we set pops up.
- Repeat these steps for range Price & Set the values between 100-200.
- Click OK. Inputting the values we will come up with an image similar to the image below.
1.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.
- Select the range Size. Follow the sequence, Data Tab >> Data Validation drop-down menu (In the Data Tools Section), and open the Data Validation dialog box.
- Select Data Validation. A window pops up. Below the Settings Section, Select Custom (In Allow drop-down menu) >> Type the formula below in the Formula box:
Now, repeat the Error Message setting from the previous method.
- Again select Range Price. Repeat the same step and apply the following formula
- Click OK.
- You can check the workability of the Formula by inputting any values out of the range, and the warning window pops up.
And after entering all the entries the dataset looks like the below picture.
Method 2: Applying 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).
- Click on any cell C8. Type the Formula
- F5 = Bottom Value
- E5 = Top Value
Make sure you lock the references by pressing F4.
- Press ENTER. Drag the Fill Handle as down to Autofill the formula. You will get random values between 22-30 like in the picture below.
This value will change every time we trigger an operation. Though it provides the luxury to create a dummy dataset quickly.
Method 3: Using MAX and MIN Functions
To extract the maximum and minimum value from our dataset, let’s follow the steps below
- Select any cell F5. Type the Formula
This formula will extract the largest value in the range D5:D13.
- Click on any cell G5. Insert the Formula
This formula will extract the minimum value in the range D5:D13.
Method 4: Utilizing SMALL and LARGE Functions
The LARGE function returns the k-th largest value (i.e. 5th largest) and the SMALL function returns the k-th smallest value in a dataset. So, if we want to find the first largest and the first smallest value, we have to use the value of k => 1.
To extract the maximum and minimum values from our dataset, let’s follow the steps below: Select any cell F5. Type the Formula
This formula will extract the largest value in the range D5:D13 as we have used the second argument k => 1
For finding the smallest value, insert the Formula
This formula will extract the smallest value in the range D5:D13.
So, these are the methods you can follow to set a minimum and maximum value in Excel. 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.