This is an overview of the 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.
The dataset below showcases the Sizes and Prices of several shirt samples.
Method 1 – Using the Data Validation Feature
To fill the Size and Price column use the Data Validation feature.
1.1. Using Value Range
Steps:
- 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.
- Repeat the same steps for Price and set the values between 100-200.
- 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
the RANDBETWEEN function generates random data from top and bottom values.
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
The MAX function returns the largest value and the MIN function returns the smallest value in a set of values ignoring logical values and texts.
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
The LARGE function returns the k-th largest value. The SMALL function returns the k-th smallest value. To find the first largest and the first smallest value, use k => 1.
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.
Download Practice Workbook
Related Articles
- Excel MIN and MAX in Same Formula
- How to Cap Percentage Values Between 0 and 100 in Excel
- Find Max Value and Corresponding Cell in Excel
- How to Find Maximum Value in Excel with Condition
- How to Find Max Value in Range with Excel Formula
<< Go Back to Excel MAX Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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 …
Total Grade = MIN(Exam_Grade + Extra_Credit*0.2,100)
2. IF Function: The formula will be …
Total Grade = IF(Exam_Grade + Extra_Credit*0.2>100,100,Exam_Grade + Extra_Credit*0.2)
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)