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.

**Method 1**

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

**Method 2**

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

**Method 3**

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

**Method 4**

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

**Method 5**

### 5. Using Data Validation

In this section, we will learn how to use **Data Validation **to set a minimum and maximum value in Excel.

**Data Validation**

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

**Data Validation**

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