Editor choice

How to Set a Minimum and Maximum Value in Excel – 4 Methods

This is an overview.

The dataset below showcases the Sizes and Prices of several shirt samples.

Method 1 – Using the Data Validation Feature

1.1. Using Value Range

Steps:

• Select the range Size. Go to the Data Tab> Data Tools.
• Click Data Validation.

• A window is displayed. Choose Settings.
• In Allow, select Whole Number >> In Data, between >> 22 in Minimum >> 30 in Maximum

• Go to Error Alert.
• In Style, select Warning >>In Title, choose Out_of_Range  >> In Error message, Please enter a value between 22-30.

• Click OK. Enter values. If values are entered out of the range (22-30), the Warning Message will be displyed.

• Click OK. This is the output.

1.2. Using a Data Validation Custom Formula

Steps:

• Select the range Size: Data Tab >> Data Tools >>Data Validation
• In the Data Validation window: in Allow,choose Custom  >> Enter the formula below.
`=AND(C5>=22,C5<=30)`

Repeat the procedure in 1.1 for Error Message.

• Select the range Price and apply the following formula.

`=AND(D5>=100,D5<=200)`

• Click OK.

This is the output.

This is the final output.

Method 2 – Applying the RANDBETWEEN Function

Steps

• Click C8. Enter the Formula
`=RANDBETWEEN(\$F\$5,\$E\$5)`

Here,

• F5 = Bottom Value
• E5 = Top Value

Make sure you lock the references by pressing F4.

• Press ENTER. Drag the Fill Handle down to Autofill the formula. You will get random values between 22-30.

Method 3- Using the MAX and MIN Functions

Steps:

• Select F5. Enter the Formula.
`=MAX(D5:D13)`

It extracts the largest value in D5:D13.

• Click G5. Enter the Formula

`=MIN(D5:D13)`

It extracts the minimum value in D5:D13.

Method 4 – Utilizing the SMALL and LARGE Functions

To find the first largest and the first smallest value:

Steps:

• To extract the maximum and minimum values, select F5.
• Enter the Formula.

`=LARGE(D5:D13,1)`

It extracts the largest value in D5:D13 (the second argument k => 1 is used)

To find the smallest value, enter the formula.

`=SMALL(D5:D13,1)`

It extracts the smallest value in D5:D13.

Related Articles

<< Go Back to Excel MAX Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

1. Hi, I enjoyed your explanations and thank you for that. But what I’m trying to do is a bit different. I want to give my students grades that must not exceed 100. Each student has an exam grade and some extra credits. So the main formula is ExamGrade+(ExtraCredit*20%100) but some do get more than 100. How can I prevent this or correct this? Thank you

• You can simply do that using any of the above methods that use the MIN function or the IF function.

1. MIN Function: The formula will be …

2. IF Function: The formula will be …

Assume the Exam_Grade is in cell B2 and the Extra_Credit is in cell C2. Then apply any of the following formulas in cell D2 to get the Total_Grade with a maximum of 100.
`=MIN(B2+C2*0.2,100)`
`=IF(B2+C2*0.2>100,100,B2+C2*0.2)`

I have also emailed you an Excel document for this. Please check.

Thanks for reaching out to us.

Regards,
Md. Shamim Reza (ExcelDemy Team)

Advanced Excel Exercises with Solutions PDF