max assignment in excel

MAX function

This article describes the formula syntax and usage of the MAX  function in Microsoft Excel.

Description

Returns the largest value in a set of values.

MAX(number1, [number2], ...)

The MAX function syntax has the following arguments:

Number1, number2, ...     Number1 is required, subsequent numbers are optional. 1 to 255 numbers for which you want to find the maximum value.

Arguments can either be numbers or names, arrays, or references that contain numbers.

Logical values and text representations of numbers that you type directly into the list of arguments are counted.

If an argument is an array or reference, only numbers in that array or reference are used. Empty cells, logical values, or text in the array or reference are ignored.

If the arguments contain no numbers, MAX returns 0 (zero).

Arguments that are error values or text that cannot be translated into numbers cause errors.

If you want to include logical values and text representations of numbers in a reference as part of the calculation, use the MAXA function.

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

How to Find the Largest Value in Excel

Finding the largest value from the given values is important when making decisions about things like what is the highest score in the class or what is the highest gross profit margin %, etc.

When there are so many values, it is difficult to do it manually.

In Excel, we have many simple and easy methods to find the largest value accurately.

In this article, I will show you some simple methods that you can use to find the largest value in Excel .

Table of Contents

Method 1: Use the MAX Function to Find the Largest Value in Excel

Sometimes we are given a range of values, and we have to figure out which value is the largest or highest. In that case, we simply use the Excel MAX function to find the largest number in the range that is given.

Below I have a data set where Column A shows the group name, Column B shows the student name, and Column C shows the score of each student.

Now, I want to find what is the highest score of these data in Cell F1.

I can use the below formula to find the highest score of the students.

The syntax of the MAX function is MAX(number1, [number2], …). For the MAX function, we have to enter at least one number.

In this case, I have entered the scores of all students as a range. So, I have selected cells C2 to C6 as arguments of the MAX function.

Then, Excel returns the largest value from the selected range. In this case, I got 91 as the highest score as it is the largest value in cells C2 to C6.

The MAX function ignores empty cells, text values, and logical values (Such as TRUE, which Excel evaluates as 1)  from arrays or references.

If you want to consider those as part of your calculation, you have to either directly enter them to list the arguments of the MAX function or use the MAXA function. The syntax of the MAXA function is MAXA(value1,[value2],…).

Method 2: Use the LARGE Function to Find the Largest Value in Excel

Sometimes, you need to find the second largest, third largest value and so on. In that case, you cannot use the MAX function.

Because in the MAX function, you can find only the largest value of the selected values.

Excel has a solution for that. You can use the Excel LARGE function. You can use this function to find the largest value as well as any position from the largest value (such as second largest, third largest etc.).

I can use the below formula to find the highest score.

The syntax of the LARGE function is LARGE(array, k). The first argument of the function is the array.

I have selected cells C2 to C6 as the first argument of the function. In this scenario, I want to find the largest value.

In other words, the value that gets position 1 from the largest values. So, I have entered 1 for the second argument. Then, I get the largest value from the selected range. In this case, it is 91.

If you want to find the second largest value, then you have to enter 2 for the second argument of the above function.

Method 3: Use the SUBTOTAL Function to Find the Largest Value in Excel

Sometimes, we apply filters to our data set and we want to find the largest value from the filtered data.

If we apply the MAX function or LARGE function, Excel returns the largest value from all the selected data for the array.

To get the largest value from the filtered data, you can apply the Excel SUBTOTAL function.

Sometimes I use the filter option to filter data. You can see that I have filtered students of group A.

Now, I want to find what is the highest score from the filtered data in Cell F1.

I can apply the below formula to find the largest value from the filtered data.

The syntax of the SUBTOTAL function is SUBTOTAL(function_num,ref1,[ref2],…).

As the first argument of this function, I have to enter the function number. The function number for the MAX function is 4.

Then,  for the second argument, I have to select the array. I have selected cells A2 to A6 for the second argument.

Now, Excel returns the largest number from the filtered data. So, in this case, Excel returns the largest value from the group A students. That is 88.

Note : If you want to ignore the manually hidden rows also when finding the largest value, then use the function number 104.

Method 4: Use the AGGREGATE Function to Find the Largest Value in Excel

As you have learned in the previous method, you can use the SUBTOTAL function to find the largest value from the filtered data.

But, when you want to find the second largest, third largest value and so on from the filtered data, you can’t use the SUBTOTAL function.

The reason is that you don’t have a function number for the LARGE function in the SUBTOTAL function. In such a situation, you can use the Excel AGGREGATE function.

The syntax of the AGGREGATE function is AGGREGATE(function_num, options, array, [k]).

The first argument of the AGGREGATE function is the function number. I have entered 14 for the first argument as I want to get the LARGE function.

Then, for the second argument, you have to select one of the options from the below.

I have selected option 7 and entered 7 for the second argument. You can even double-click and select an option from the options list.

Then, I have to select the array for the third argument. I have selected cells C2 to C6 for the third argument.

For the final argument, I have to enter the position of the largest value. As I want to find the largest value, I have entered 1 for the last argument.

If you want to find the second largest value, you have to enter 2 for that argument.

Then, Excel returns the largest value from the filtered data. So, here I got 88 as the highest score of group A students.

Note : If you want to find only the largest value (without the second largest, third largest, etc.) from the filtered data, you can use function number 4 for the second argument and ignore the last argument of the function. Function number 4 is for the MAX function.

Method 5: Use the MAX Function to Find the Largest Value Based on Conditions in Excel

Sometimes you need to find the largest value based on some conditions.

You can use the MAX function itself to find the largest value based on some conditions.

Now, I want to find the highest score group A students in Cell F1. Please note that I don’t want to apply filters for my data set.

I can use the below formula to find the highest score of group A students.

First I applied the MAX function. The syntax of the MAX function is MAX(number1, [number2], …).

For the MAX function, we have to enter at least one number. In this case, I have entered the scores of all students as a range. So, I have selected cells C2 to C6 as arguments of the MAX function.

But, I want to find the largest value of group A students. So, I have to multiply the range with the logical condition.

The condition that I have applied is (A2:A6=F1). Then, Excel evaluates this logical condition and considers only the TRUE values for the MAX calculation.

If the output of the logical condition for any cell is FALSE, the MAX function ignores that cell.

So, in this case, Excel returns 88 as the highest score of group A students. (Even Though 91 is the highest score from the entire list, the student who obtained that (Ian) is from group B. So, Excel ignores 91).

Method 6: Use the MAXIFS Function to Find the Largest Value Based on Conditions in Excel

In the previous method, you have learned how to insert conditions inside the MAX function to find the largest value based on conditions.

But, if you are not confident about applying conditions inside formulas, you can use the MAXIFS function to find the largest value based on conditions.

Compared to the previous method, this method is user-friendly and minimizes errors.

The syntax of the MAXIFS function is MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …).

For the first argument of this function, I have selected the range in which I want to find the largest or maximum value. In this case, I want to find the highest score.

So, I have selected cells that contain scores which are cells C2 to C6. Then, I have to enter the range of cells to evaluate the first criteria.

I want to find group A from the group column. So, I have selected cells A2 to A6 for the second argument.

Next, I have to enter the criteria for the selected range. I want to get the group name that is given in cell F1.

So, I have selected cell F1. But, I can also enter A within quotes for the third argument. You can enter up to 127 pairs of such conditions for the MAXIFS function. But, in this case, I have used only one condition pair.

So, Excel returns the highest score from group A students, which is 88.

You now know six different methods to find the largest value in Excel. Learn about all of the methods and choose the one that best meets your needs.

Other Excel articles you may also like:

• How to Fill Blank Cells with Value above in Excel?
• Count the Number of Yes in Excel (Using COUNTIF)
• Check If Value is in List in Excel
• Find the Position of a Character in a String in Excel

Steve Scott

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment Cancel reply

Save my name, email, and website in this browser for the next time I comment.

How to use the MIN and MAX Functions in Excel: 2024 Guide

MIN and MAX functions are simple yet essential Excel functions that you need to know. 😊

The MIN function allows you to find the minimum (lowest) value while the MAX function allows you to find the maximum (highest) value in any given range of values.

Whether you want to find the lowest scores in a class or find the highest sales in your department, the MIN and MAX functions will help you do that easier and faster! 🔍

Let’s dive in! 🤿

You can use this free practice workbook as you follow the steps. Download here .

Table of Contents

How to find the highest value with MAX

How to find the lowest value with min, max with criteria: the maxifs function.

To find the highest or maximum value in a set of values, use the Excel MAX function.

The syntax of the MAX function is: =MAX(number1, [number2], …) with the following arguments:

• Number1, number2, … Number1 is required, subsequent numbers are optional.

Where the numbers can be a list of numeric values or an array of constants. It could be a range of cells, also called an array of cells.

Here’s how to use the MAX function in Excel with a list of numeric values. 👇

Say, you want to find the maximum value of this list of numeric values: 1, 2, 3, 4, 5.

• Type the MAX function in a cell.
• The first argument of the MAX function is Number1 . So type in the numbers 1, 2, 3, 4, and 5. Then close the MAX function with a right parenthesis.

=MAX(1, 2, 3, 4, 5)

• Press Enter .

The MAX value in the given list of numeric values is 5.

Well, that was easy! You can even tell the MAX value by just looking at the numbers themselves.

But for long lists of numeric values and more data sets, this MAX function in Excel will be a lifesaver! 🙌

Use the MAX function with cell references

If you already have the range of values in your worksheet, you don’t have to type them in one by one as literal values in the MAX function. You just need to highlight the range of values in your data table as the argument for the MAX function.

Here’s how to use the MAX function in excel in a given range of cells. 👇

Open your practice workbook to put this into action.

You will see 2 sets of student test scores. The scores are already in the data table. The cells containing the scores will become the cell reference to find the max value using the MAX function. Let’s find the highest score in Test 1.

• In cell F3, type the MAX function :
• The first argument is Number1 . Select the entire cell range B2:B16. The formula bar should look like this:

=MAX(B2:B16

Close the MAX function with a right parenthesis.

The MAX function returns the largest value in a set of values. Simply put, you get the highest value or number in a specified range.

In our example, the highest score is 30. A perfect score! 👏

You can even check the data table to confirm that you got the highest score right. ✔️

Now, it’s your turn. 💪 Find the highest score in Test 2 using the MAX function.

💡 Reminders from Kasper:

• Arguments in the MAX function can either be numbers or names, ranges, or references that contain numbers .
• If an argument is a given range or reference, only numbers in that range or reference are used. Empty cells, logical values true and false, or text values in the range or reference are ignored .
• This is TRUE for the MAX function and the MIN function as well.

The MIN function in Excel has the same syntax and argument as the MAX function. Only it will help you find the lowest or minimum value in a set of values.

The syntax of the MIN function is: =MIN(number1, [number2], …) with the same arguments as the MAX function.

Let’s find the minimum value using the list of numeric values in our previous example.

• Type the MIN function in a cell.
• The first argument of the MIN function is Number1 . Type in 1, 2, 3, 4, 5. Then close the MIN function with a right parenthesis. The formula bar should look like this:

=MIN(1, 2, 3, 4, 5)

The MIN value in the list of numeric values is 1.

Of course, you can do that with just one look. But what if you need to find the MIN value in a long list of values and more sets of data? 😨

It’s still possible with the MIN function.

Use the MIN function with cell references

Just like the MAX function, you can find the minimum number in any given range by highlighting the range of values instead of typing them in one by one in the MIN function.

Let’s try it!

Still using the student test scores as our data set, let’s find the lowest score in Test 1 using the MIN function in Excel. 👇

• 1. In cell F9, type the MIN function:
• Select the entire range of cells B2:B16 . Close the MIN function with a right parenthesis. The MIN formula should look like this:

=MIN(B2:B16)

The MIN function returns the smallest value in a set of values. Simply, you get the lowest value or the minimum value in a given range.

In our example, the lowest score is 10. 👍

Now, it’s your turn. 💪 Find the lowest score in Test 2 using the MIN function.

💡 Kasper’s Pro Tip

• You can actually mix cell references and literal values for your arguments in the MAX and MIN functions. It will still provide the same result of either min or max values for the selected cell ranges.
• You can also select multiple ranges for your arguments in the MAX and MIN functions. The function still works even when dealing with data in a contiguous or non-contiguous row.

When you come to think of it, the MAX and MIN functions act like a filter for finding either the highest or lowest value in a given range of values. 🤔

You can maximize its filtering function to get specific results based on specific criteria. Combining the MAX/MIN functions and the IF function, the MAXIFS function and the MINIFS function in Excel are born.

The MAXIFS and MINIFS functions return the maximum or minimum value among cells specified by a given set of conditions or criteria. ✔️

Read more about the MAXIFS and MINIFS functions here .

That’s it – Now what?

Great job! 🥳 You’ve learned two of the simplest but essential functions in Excel.

You can get maximum results with minimum effort with the MAX and MIN functions in Excel.

If you want to save more time and effort, learn about other essential Excel functions like the IF , SUMIF , and VLOOKUP Functions. 🚀

You’ll learn all these when you sign up for my free 30-minute email course !

Other resources

If you’re looking for more filter functions like the MAX and MIN functions above, you may like to read this article: How to Filter in Excel: Full Step-by-Step Guide . You will learn how to filter text values, number values, and even color! 🤩

Want to learn advanced functions in Excel? Try reading about the MATCH function here . It is best paired with the INDEX function too.

I hope this was a helpful read.

Take care! 👋

Advertisements

• Formulas / Functions
• Oracle / PLSQL
• Web Development
• Color Picker
• Programming
• Techie Humor

Lookup/Ref Functions

• ADDRESS (WS)
• CHOOSE (WS, VBA)
• COLUMN (WS)
• COLUMNS (WS)
• HLOOKUP (WS)
• HYPERLINK (WS)
• INDIRECT (WS)
• LOOKUP (WS)
• OFFSET (WS)
• TRANSPOSE (WS)
• VLOOKUP (WS)
• XLOOKUP (WS)

String/Text Functions

• BAHTTEXT (WS)
• CONCAT (WS)
• CONCATENATE (WS)
• CONCATENATE with & (WS, VBA)
• DOLLAR (WS)
• FORMAT STRINGS (VBA)
• INSTR (VBA)
• INSTRREV (VBA)
• LCASE (VBA)
• LEFT (WS, VBA)
• LEN (WS, VBA)
• LTRIM (VBA)
• MID (WS, VBA)
• NUMBERVALUE (WS)
• PROPER (WS)
• REPLACE (WS)
• REPLACE (VBA)
• RIGHT (WS, VBA)
• RTRIM (VBA)
• SEARCH (WS)
• SPACE (VBA)
• SPLIT (VBA)
• STRCOMP (VBA)
• STRCONV (VBA)
• STRREVERSE (VBA)
• SUBSTITUTE (WS)
• TEXTJOIN (WS)
• TRIM (WS, VBA)
• UCASE (VBA)
• UNICHAR (WS)
• UNICODE (WS)

Date/Time Functions

• DATEADD (VBA)
• DATEDIF (WS)
• DATEDIFF (VBA)
• DATEPART (VBA)
• DATESERIAL (VBA)
• DATEVALUE (WS, VBA)
• DAY (WS, VBA)
• DAYS360 (WS)
• EOMONTH (WS)
• FORMAT DATES (VBA)
• HOUR (WS, VBA)
• ISOWEEKNUM (WS)
• MINUTE (WS, VBA)
• MONTH (WS, VBA)
• MONTHNAME (VBA)
• NETWORKDAYS (WS)
• NETWORKDAYS.INTL (WS)
• NOW (WS, VBA)
• SECOND (WS)
• TIMESERIAL (VBA)
• TIMEVALUE (WS, VBA)
• WEEKDAY (WS, VBA)
• WEEKDAYNAME (VBA)
• WEEKNUM (WS)
• WORKDAY (WS)
• WORKDAY.INTL (WS)
• YEAR (WS, VBA)
• YEARFRAC (WS)

Math/Trig Functions

• ABS (WS, VBA)
• AGGREGATE (WS)
• CEILING (WS)
• CEILING.PRECISE (WS)
• COMBIN (WS)
• COMBINA (WS)
• COS (WS, VBA)
• DEGREES (WS)
• EXP (WS, VBA)
• FORMAT NUMBERS (VBA)
• INT (WS, VBA)
• MDETERM (WS)
• MINVERSE (WS)
• PRODUCT (WS)
• RADIANS (WS)
• RANDBETWEEN (WS)
• RANDOMIZE (VBA)
• ROUND (VBA)
• ROUNDDOWN (WS)
• ROUNDUP (WS)
• SIN (WS, VBA)
• SUBTOTAL (WS)
• SUMIFS (WS)
• SUMPRODUCT (WS)
• SUMX2MY2 (WS)
• SUMX2PY2 (WS)
• SUMXMY2 (WS)
• TAN (WS, VBA)

Statistical Functions

• AVEDEV (WS)
• AVERAGE (WS)
• AVERAGEA (WS)
• AVERAGEIF (WS)
• AVERAGEIFS (WS)
• BETA.DIST (WS)
• BETA.INV (WS)
• BETADIST (WS)
• BETAINV (WS)
• BINOM.DIST (WS)
• BINOM.INV (WS)
• BINOMDIST (WS)
• CHIDIST (WS)
• CHIINV (WS)
• CHITEST (WS)
• COUNTA (WS)
• COUNTBLANK (WS)
• COUNTIF (WS)
• COUNTIFS (WS)
• FORECAST (WS)
• FREQUENCY (WS)
• GROWTH (WS)
• INTERCEPT (WS)
• LINEST (WS)
• MAXIFS (WS)
• MEDIAN (WS)
• MINIFS (WS)
• MODE.MULT (WS)
• MODE.SNGL (WS)
• PERCENTILE (WS)
• PERCENTRANK (WS)
• PERMUT (WS)
• QUARTILE (WS)
• STDEVA (WS)
• STDEVP (WS)
• STDEVPA (WS)

Logical Functions

• FOR...NEXT (VBA)
• IF (more than 7) (WS)
• IF (up to 7) (WS)
• IF-THEN-ELSE (VBA)
• IFERROR (WS)
• SWITCH (WS)
• SWITCH (VBA)
• WHILE...WEND (VBA)

Information Functions

• ENVIRON (VBA)
• ERROR.TYPE (WS)
• ISBLANK (WS)
• ISDATE (VBA)
• ISEMPTY (VBA)
• ISERROR (WS, VBA)
• ISLOGICAL (WS)
• ISNONTEXT (WS)
• ISNULL (VBA)
• ISNUMBER (WS)
• ISNUMERIC (VBA)
• ISTEXT (WS)

Financial Functions

• ACCRINT (WS)
• ACCRINTM (WS)
• AMORDEGRC (WS)
• AMORLINC (WS)
• DDB (WS, VBA)
• FV (WS, VBA)
• IPMT (WS, VBA)
• IRR (WS, VBA)
• MIRR (WS, VBA)
• NPER (WS, VBA)
• NPV (WS, VBA)
• PMT (WS, VBA)
• PPMT (WS, VBA)
• PV (WS, VBA)
• RATE (WS, VBA)
• SLN (WS, VBA)
• SYD (WS, VBA)

Database Functions

• DAVERAGE (WS)
• DCOUNT (WS)
• DCOUNTA (WS)
• DPRODUCT (WS)
• DSTDEV (WS)
• DSTDEVP (WS)

Engineering Functions

• BIN2DEC (WS)
• BIN2HEX (WS)
• BIN2OCT (WS)
• COMPLEX (WS)
• CONVERT (WS)

File/Directory Functions

• CHDIR (VBA)
• CHDRIVE (VBA)
• CURDIR (VBA)
• FILEDATETIME (VBA)
• FILELEN (VBA)
• GETATTR (VBA)
• MKDIR (VBA)
• SETATTR (VBA)

Data Type Conv. Functions

• CBOOL (VBA)
• CBYTE (VBA)
• CDATE (VBA)

MS Excel: How to use the MAX Function (WS)

This Excel tutorial explains how to use the Excel MAX function with syntax and examples.

Description

The Microsoft Excel MAX function returns the largest value from the numbers provided.

The MAX function is a built-in function in Excel that is categorized as a Statistical Function . It can be used as a worksheet function (WS) in Excel. As a worksheet function, the MAX function can be entered as part of a formula in a cell of a worksheet.

The syntax for the MAX function in Microsoft Excel is:

Parameters or Arguments

The MAX function returns a numeric value.

• Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel MAX function examples and explore how to use the MAX function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following MAX examples would return:

Frequently Asked Questions

Question: In Microsoft Excel, I want to calculate the largest yearly difference in total between any adjacent year for each company. Below is my spreadsheet with a small sample of 4 years of data.

Right now, I'm using the following formula to calculate the yearly difference:

I am wondering if there is a way to streamline this and not have such a long and complicated formula.

Answer: Yes, it should be possible to simplify your formula and achieve the same results. By using the ABS function , you could remove some of your IF conditions since you are really looking for the yearly differences as "absolute values". Try replacing your formula with the following:

As you can see in the spreadsheet below, this formula is much simpler and returns the same results.

Question: I'm having a problem finding a specific formula in Excel.

Is there a formula that will tell me the Cell with the highest number rather than tell me the highest number? I.E. if K2 has 15 and is the highest, how do I make a formula that says K2 instead of 15?

Answer: You can use the CELL , INDEX , and MATCH functions in combination with the MAX function to return the cell with the highest value.

For example, if you wanted to find the cell with the highest value in the range K1 to K10, you could use the following formula:

This would return the result as an absolute reference such as $K$2, as you stated in your example where the cell K2 contained 15 which was the highest value.

Home | About Us | Contact Us | Testimonials | Donate

While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy .

Copyright © 2003-2024 TechOnTheNet.com. All rights reserved.

Max if criteria match

Related functions .

To find the maximum value in a range with specific criteria, you can use a basic array formula based on the IF function and MAX function. In the example shown, the formula in cell H8 is:

which returns the maximum temperature on the date in H7.

Note: this is an array formula and must be entered with Control + Shift + Enter

Generic formula

Explanation .

The example shown contains almost 10,000 rows of data. The data represents temperature readings taken every 2 minutes over a period of days. For any given date (provided in cell H7), we want to get the maximum temperature on that date.

Inside the IF function , logical test is entered as B5:B9391=H7. Because we're comparing the value in H7 against a range of cells (an array), the result will be an array of results, where each item in the array is either TRUE or FALSE. The TRUE values represent dates that match H7.

For the value if true, we provide the range E5:E9391, which fetches all the full set of temperatures in Fahrenheit. This returns an array of values the same size as the first array.

The IF function acts as a filter. Because we provide IF with an array for the logical test, IF returns an array of results . Where the date matches H7, the array contains a temperature value. In all other cases, the array contains FALSE. In other words, only temperatures associated with the date in H7 survive the trip through the IF function.

The array result from the IF function is delivered directly to the MAX function , which returns the maximum value in the array.

With MAXIFS

In Excel O365 and Excel 2019, the new MAXIFS function can find the maximum value with one or more criteria without the need for an array formula. With MAXIFS, the equivalent formula for the this example is:

Related formulas

• Maximum value if

• Maximum if multiple criteria

• Get next scheduled event

• Find closest match

Related functions

• MAX Function

The Excel MAX function returns the largest numeric value in the data provided. MAX ignores empty cells, the logical values TRUE and FALSE, and text values.

• IF Function

The Excel IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical...

• MAXIFS Function

The Excel MAXIFS function returns the largest numeric value in cells that meet multiple conditions, referred to as criteria. To define criteria, MAXIFS supports logical operators (>,<,<>,=) and wildcards (*,?,~), and can apply conditions to cells that contain dates, numbers, and text...

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Related Information

Get training, quick, clean, and to the point training.

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

Help us improve Exceljet

Your email address is private and not shared.

• Kutools for Excel
• Kutools for Outlook
• Kutools for Word
• Setup Made Simple
• End User License Agreement
• Get 4 Software Bundle
• 60-Day Refund
• Tips & Tricks for Excel (3000+)
• Tips & Tricks for Outlook (1200+)
• Tips & Tricks for Word (300+)
• Excel Functions (498)
• Excel Formulas (350)
• Excel Charts
• Outlook Tutorials
• ExtendOffice GPT ExtendOffice GPT Assistant
• About Us Our Team

Feature Tutorials

• Search Search more
• Retrieve License Lost License?
• Report a Bug Bug Report
• Forum Post in Forum

Explore the new, free version of Kutools for Outlook today!

How to limit formula result to maximum or minimum value in Excel?

Here are some cells needed to be entered, and now I want to use a formula to sum up the cells but limit the result to a maximum value such as 100. In other words, if the summation is smaller than 100, display the summation, otherwise display 100.

Limit formula result to a maximum or minimum value

To handle this task, you only need to apply the Max or Min function in Excel.

Limit formula result to maximum value (100)

Select a cell which you will place the formula at, type this formula =MIN(100,(SUM(A5:A10))) , A5:A10 is the cell range you will sum up, and press Enter . Now, if the summation is greater than 100, it will display 100, if not, display the summation.

Limit formula result to minimum value (20)

Select a cell which you place the formula at, type this =MaX(20,(SUM(A5:A10))) , A5:A10 is the cell range you will sum up, and press Enter . Now, if the summation is smaller than 20, it will display 20; if not, display the summation.

Best Office Productivity Tools

Supports Office/Excel 2007-2021 and 365  |  Available in 44 Languages  |  Easy to Uninstall Completely

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.   Click Here to Get The Feature You Need The Most...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint , Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Student with highest mark

This example shows how to calculate the student with the highest mark from a list of students.

EXCEL EXPLANATION

Example: Student with highest mark

Explanation about how to calculate a student with highest mark

Excel Tutorial

Excel formatting, excel data analysis, table pivot, excel functions, excel how to, guided projects, excel examples, excel references, excel max function, max function.

The MAX function is a premade function in Excel, which finds the highest number in a range.

It is typed =MAX

The function ignores cells with text. It will only work for cells with numbers.

Note: There is another function called MIN , which finds the lowest value in a range, the opposite of MAX .

How to use the =MAX function:

• Select a cell ( G5 )
• Double click the MAX command
• Select a range ( D2:D21 )

Let's have a look at an example!

Find the Pokemon which has the highest total stats in the range D2:D21 :

That's great! The function has successfully found the highest value, 525 . Cloyster has the highest Total stats of the Pokemons in the range D2:D21 .

Let's add text to a cell in the range D2:21 to see what happens. Type Alakazam to D8 .

Nice going. The function ignores the text typed in the range. 510 is returned as the new highest value, since the old one was replaced with text. Poliwrath is now the Pokemon with the highest Total stats in the range D2:D21 .

COLOR PICKER

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail: [email protected]

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail: [email protected]

Top Tutorials

Top references, top examples, get certified.

Practice And Learn Excel Online For Free

Welcome to Excel Practice Online!

Now you can practice Excel everywhere! You can even practice on your mobile phone!

Every function and tool has an explanation followed by an online excel exercise which can be solved within the page itself, no need to download anything – All thanks to the amazing powers of Excel Online!

The tutorials are sorted from beginner level to advanced level. If you like this site please share it with your friends! 🙂

Tip for mobile phone users – tap twice on the cell you want to edit in order to edit it.

• Free Excel Courses and Resources
• Excel Self-Assessment Tool
• Free Excel Online Exercises
• Excel Basics – Zero to Hero
• Excel Tests
• Top 10 formulas and functions in Excel
• Vlookup – Tutorial with Example and Exercise Sheet
• Pivot Tables Tutorial
• Excel Shortcuts – Windows and Mac
• HOT! – Excel Mortgage Calculator – Calculate your mortgage payments and get the payment schedule for the entire period of the loan – Step-by-step tutorial on how to build a Mortgage Calculator in Excel.
• New! Excel Online Cheat Sheet – A Quick Guide To Excel’s Web Version
• Can’t find what you’re looking for? Suggest a tutorial here!
• Excel Basics – Start here if you are new to Excel! Learn how Excel works, how to perform basic calculations, and how to use cell references to save time and increase efficiency!
• Addition (Plus)
• Subtraction (Minus)
• Multiplication
• Excel Shortcuts for Windows – Master Excel Shortcuts to save time and increase efficiency!
• Excel Shortcuts for Mac – Learn how to make the most of Excel on your Mac!

Formulas/Functions

• SUM function – Sum multiple values in Excel
• MAX – find the maximum value in a range
• MIN – find the minimum value in a range
• COUNT – Count numeric values in a range
• COUNTA – Count numeric and textual values
• AVERAGE – Calculate average of a range
• Filtering in Excel – Learn how to filter your data using Excel’s Filter Tool
• Excel Sort – Learn how to sort your data in Excel.
• Flash Fill – Excel’s hidden gem for auto-completing data based on a pattern
• Remove Duplicates – Remove duplicate values in a single column or multiple columns!

Intermediate

Conditional.

• IF function – check if a condition is met
• NESTED IF – Multiple if conditions
• Conditional Formatting – Format Excel Cells based on criteria
• COUNTIF – Count cells in range which meet a certain criteria
• SUMIF – Sum range based on criteria
• AVERAGEIF – Calculate the average of a range based on criteria
• SUMIFS – Sum cells using multiple criteria
• COUNTIFS – Count cells using multiple criteria
• MAXIFS – Find maximum value in a range based on criteria
• MINIFS – Find minimum value in a range based on criteria
• AND/OR – Check if multiple criteria are met (Works great when combined with an IF function!)
• ISBLANK – Check if a cell is blank or not
• VLOOKUP – lookup value and return corresponding value from a table
• HLOOKUP – lookup value and return corresponding value from a table
• Hot!!! XLOOKUP – Excel’s next generation lookup function which combines the best features from VLOOKUP, INDEX MATCH, HLOOKUP and IFERROR/IFNA

Pivot tables

• Pivot Table – Quickly Analyze and Summarize your data using Excel’s most powerful tool!

Text Formulas

• LEFT, MID, RIGHT – Basic Text Functions
• HOT! – TEXTBEFORE & TEXTAFTER – Extract text before or after a delimiter using Excel’s brand new powerful functions!
• HOT! – TEXTSPLIT – Split your text into multiple cells using this super powerful new function!
• TEXTJOIN – Easily combine multiple cells using delimiter
• CONCAT – Combine range of cells without delimiter
• CONCATENATE – Combine two cells or more into one cell
• LEN – Find the length of a cell
• FIND – Find the position of a text within another text (Case-sensitive)
• SEARCH – Find the position of a text within another text (Case-insensitive)
• SUBSTITUTE – Replace text with another text in a cell/expression
• TRIM – Remove extra spaces from the text
• LOWER, UPPER, PROPER – Convert text to lowercase, uppercase and proper case
• VALUE – Convert data stored as text into values
• TEXT – Convert and format numbers into text
• Text to Columns – Quickly split a column into multiple columns using a delimiter. Bonus – Quickly change date formats or convert text to numbers!
• FORMULATEXT – display a formula in another cell as text

Date functions

• DAY, MONTH, YEAR – Extract day, month and year from a date in Excel
• DATE – Create a date from individual values
• WEEKDAY – Return the number of the day of the week
• EOMONTH – Return the date of the last day of the month based on a specific date

Index & Match lookup

• INDEX – Retrieve cell in nth position in a range
• MATCH – Find position of value in a range
• INDEX MATCH – Just like VLOOKUP, only better.

Other advanced tools

• SUMPRODUCT – Sum the products of Excel ranges
• Excel Wildcards – Advanced searching and matching in Excel
• Advanced Filter – Filter by multiple criteria in the same column, or even in different columns!

Power Query

• Combine data from multiple Excel workbooks using Power Query
• Column from Examples tool – Learn the secret to mastering Power Query without any prior knowledge!
• Unpivot columns easily using Power Query

Secret Excel Functions

This section covers Excel functions that are not available in most of Excel’s versions. These functions will unlock a new set of capabilities such as fining only unique values, sorting, and filtering – the tutorials below will help you with mastering Excel’s new functions!

• UNIQUE – Extract unique values from a range
• SORT Function – Sort range dynamically
• SORTBY – Sort range dynamically by using another range
• FILTER Function – Filter range by specific criteria
• RANDARRAY – Create an array of random numbers 
• SEQUENCE – Create a range of sequential values
• LET – Assign values and calculations to names to improve your formula’s ease of use, readability, and performance!
• HOT! – LAMBDA – The mother of all functions that will help you create amazing and powerful custom functions for your own need! 
• VSTACK – Vertically stack arrays/ranges in Excel
• HSTACK – Horizontally stack arrays/ranges in Excel
• CHOOSEROWS – Return specific rows from a range or array
• CHOOSECOLS – Return specific columns from a range or array
• TOROW – Convert a range/array into a single row
• TOCOL – Convert a range/array into a single column

Financial Functions

Learn how to use Excel to make financial calculations!

• Excel Financial Calculator – quickly calculate PV, FV, PMT, NPV, IRR
• PMT – Calculate the periodic payment amount of a loan, mortgage, or another financial instrument
• PPMT & IPMT – Find the Principal and Interest portion of a certain payment
• PV – Find the Present Value of a loan, mortgage, or any other financial instrument 

Excel Macros – VBA (Visual Basic for Applications)

• Start here – How to run your first VBA Macro in Excel without knowing VBA? 

Excel Data Sheets for Practice

Want to do some freestyle practice? Create your own Excel playground with our blank excel Worksheet!

• Excel-Online Blank Worksheet
• Excel Practice Data

How to Calculate in Excel – Excel-Online Calculators

• How to Calculate GPA in Excel
• How to Calculate BMI in Excel
• How to Calculate Density in Excel
• How to Calculate Weighted Average in Excel

Terms and Conditions - Privacy Policy

• Ablebits blog
• Random data

RANDARRAY function - quick way to generate random numbers in Excel

The tutorial shows how to generate random numbers, randomly sort a list, get random selection and randomly assign data to groups. All with a new dynamic array function - RANDARRAY.

As you probably know, Microsoft Excel already has a couple of randomizing functions - RAND and RANDBETWEEN . What is the sense in introducing another one? In a nutshell, because it's far more powerful and can replace both older functions. Apart from setting up your own maximum and minimum values, it lets you specify how many rows and columns to fill and whether to produce random decimals or integers. Used together with other functions, RANDARRAY can even shuffle data and pick a random sample.

Excel RANDARRAY function

• Basic RANDARRAY formula

Generate random numbers between two numbers

Generate random date between two dates.

• Create random workdays in Excel
• Generate random numbers without duplicates
• Random sort in Excel
• Get a random sample
• Select random rows
• Random assignment in Excel
• Randomly assign data to groups

Excel RANDARRAY function not working

The RANDARRAY function in Excel returns an array of random numbers between any two numbers that you specify.

It is one of six new dynamic array functions introduced in Microsoft Excel 365. The result is a dynamic array that spills into the specified number of rows and columns automatically.

The function has the following syntax. Please notice that all the arguments are optional:

Rows (optional) - defines how many rows to fill. If omitted, defaults to 1 row.

Columns (optional) - defines how many columns to fill. If omitted, defaults to 1 column.

Min (optional) - the smallest random number to produce. If not specified, the default 0 value is used.

Max (optional) - the largest random number to create. If not specified, the default 1 value is used.

Whole_number (optional) - determines what kind of values to return:

• TRUE - whole numbers
• FALSE or omitted (default) - decimal numbers

RANDARRAY function - things to remember

To efficiently generate random numbers in your Excel worksheets, there are 6 important points to take notice of:

• The RANDARRAY function is only available in Excel for Microsoft 365 and Excel 2021. In Excel 2019, Excel 2016 and earlier versions the RANDARRAY function is not available.
• If the array returned by RANDARRAY is the final result (output in a cell and not passed to another function), Excel automatically creates a dynamic spill range and populates it with the random numbers. So, be sure you have enough empty cells down and/or to the right of the cell where you enter the formula, otherwise a #SPILL error will occur.
• If none of the arguments is specified, a RANDARRAY() formula returns a single decimal number between 0 and 1.
• If the rows or/and columns arguments are represented by decimal numbers, they will be truncated to the whole integer before the decimal point (e.g. 5.9 will be treated as 5).
• If the min or max argument is not defined, RANDARRAY defaults to 0 and 1, respectively.
• Like other random functions, Excel RANDARRAY is volatile , meaning it generates a new list of random values every time the worksheet is calculated. To prevent this from happening, you can replace formulas with values by using Excel's Paste Special > Values feature.

Basic Excel RANDARRAY formula

And now, let me show you a random Excel formula in its simplest form.

Supposing you want to fill a range consisting of 5 rows and 3 columns with any random numbers. To have it done, set up the first two arguments this way:

• Rows is 5 since we want the results in 5 rows.
• Columns is 3 as we want the results in 3 columns.

All of the other arguments we leave to their default values and get the following formula:

=RANDARRAY(5, 3)

How to randomize in Excel - RANDARRAY formula examples

Below you will find a few advanced formulas that cover typical randomizing scenarios in Excel.

To create a list of random numbers within a specific range, supply the minimum value in the 3 rd argument and the maximum number in the 4 th argument. Depending on whether you need integers or decimals, set the 5 th argument to TRUE or FALSE, respectively.

As an example, let's populate a range of 6 rows and 4 columns with random integers from 1 to 100. For this, we set up the following arguments of the RANDARRAY function:

• Rows is 6 since we want the results in 6 rows.
• Columns is 4 as we want the results in 4 columns.
• Min is 1, which is the minimum value we wish to have.
• Max is 100, which is the maximum value to be generated.
• Whole_number is TRUE because we need integers.

Putting the arguments together, we get this formula:

=RANDARRAY(6, 4, 1, 100, TRUE)

Looking for a random date generator in Excel? The RANDARRAY function is an easy solution! All you have to do is input the earlier date (date 1) and later date (date 2) in predefined cells, and then reference those cells in your formula:

For this example, we have created a list of random dates between the dates in D1 and D2 with this formula:

Of course, nothing prevents you from supplying the min and max dates directly in the formula if you wish to. Just be sure you enter them in the format that Excel can understand:

=RANDARRAY(10, 1, "1/1/2020", "12/31/2020", TRUE)

To prevent mistakes, you can use the DATE function for entering dates:

=RANDARRAY(10, 1, DATE(2020,1,1), DATE(2020,12,31), TRUE)

Generate random workdays in Excel

To produce random working days, embed the RANDARRAY function in the first argument of WORKDAY like this:

RANDARRAY will create an array of random start dates, to which the WORKDAY function will add 1 workday and ensure that all the returned dates are working days.

With date 1 in D1 and date 2 in D2, here's the formula to produce a list of 10 weekdays:

How to generate random numbers without duplicates

Though modern Excel offers 6 new dynamic array functions, unfortunately, there is still no inbuilt function to return random numbers without duplicates.

To build your own unique random number generator in Excel, you will need to chain several functions together like shown below.

Random integers :

Random decimals :

• N is how many values you wish to generate.
• Min is the lowest value.
• Max is the highest value.

For example, to produce 10 random whole numbers with no duplicates, use this formula:

To create a list of 10 unique random decimal numbers , change TRUE to FALSE in the last argument of the RANDARRAY function or simply omit this argument:

Tips and notes:

• The detailed explanation of the formula can be found in How to generate random numbers in Excel without duplicates .
• In Excel 2019 and earlier, the RANDARRAY function is not available. Instead, please check out this solution .

How to randomly sort in Excel

To shuffle data in Excel, use RANDARRAY for the "sort by" array ( by_array argument) of the SORTBY function . The ROWS function will count the number of rows in your data set, indicating how many random numbers to generate:

With this approach, you can randomly sort a list in Excel, whether it contains numbers, dates or text entries:

Also, you can also shuffle rows without mixing your data:

How to get a random selection in Excel

To extract a random sample from a list, here's a generic formula to use:

Where n is the number of random entries you wish to extract.

For example, to randomly select 3 names from the list in A2:A10, use this formula:

=INDEX(A2:A10, RANDARRAY(3, 1, 1, ROWS(A2:A10), TRUE))

Or input the desired sample size in some cell, say C2, and reference that cell:

How this formula works:

At the core of this formula is the RANDARRAY function that creates a random array of integers, with the value in C2 defining how many values to generate. The minimal number is hardcoded (1) and the maximum number corresponds to the number of rows in your data set, which is returned by the ROWS function.

The array of random integers goes directly to the row_num argument of the INDEX function, specifying the positions of the items to return. For the sample in the screenshot above, it is:

=INDEX(A2:A10, {8;7;4})

How to select random rows in Excel

If your data set contains more than one column, then specify which columns to include in the sample. For this, supply an array constant for the last argument ( column_num ) of the INDEX function, like this:

=INDEX(A2:B10, RANDARRAY(D2, 1, 1, ROWS(A2:A10), TRUE), {1,2})

Where A2:B10 is the source data and D2 is the sample size.

How to randomly assign numbers and text in Excel

To do random assignment in Excel, use RANDBETWEEN together with the CHOOSE function in this way:

• Data is a range of your source data to which you want to assign random values.
• N is the total number of values to assign.
• Value1 , value2 , value3 , etc. are the values to be assigned randomly.

For example, to assign numbers from 1 to 3 to participants in A2:A13, use this formula:

For convenience, you can enter the values to assign in separate cells, say from D2 to D4, and reference those cells in your formula (individually, not as a range):

=CHOOSE(RANDARRAY(ROWS(A2:A13), 1, 1, 3, TRUE), D2, D3, D4)

How this formula works

At the heart of this solution is again the RANDARRAY function that produces an array of random integers based on the min and max numbers that you specify (from 1 to 3 in our case). The ROWS function tells RANDARRAY how many random numbers to generate. This array goes to the index_num argument of the CHOOSE function . For example:

=CHOOSE({1;2;1;2;3;2;3;3;1;3;1;2}, D2, D3, D4)

How to randomly assign data to groups

When your task is to randomly assign participants to groups, the above formula may not be suitable because it does not control how many times a given group is chosen. For example, 5 persons could be assigned to group A while only 2 persons to group C. To do random assignment evenly , so that each group has the same number of participants, you need a different solution.

First, you generate a list of random numbers by using this formula:

=RANDARRAY(ROWS(A2:A13))

And then, you assign groups (or anything else) by using this generic formula:

Where n is the group size, i.e. the number of times each value should be assigned.

For example, to randomly assign people to the groups listed in E2:E5, so that each group has 3 participants, use this formula:

=INDEX($E$2:$E$5, ROUNDUP(RANK(B2,$B$2:$B$13)/3,0))

Please notice that it's a regular formula (not a dynamic array formula!), so you need to lock the ranges with absolute references like in the above formula.

Please remember that the RANDARRAY function is volatile. To prevent generating new random values every time you change something in the worksheet, replace formulas with their values by using the Paste Special feature.

The RANDARRAY formula in the helper column is very simple and hardly requires explanation, so let us focus on the formula in column C.

The RANK function ranks the value in B2 against the array of random numbers in B2:B13. The result is a number between 1 and the total number of participants (12 in our case).

The rank is divided by the group size, (3 in our example), and the ROUNDUP function rounds it up to the nearest integer. The result of this operation is a number between 1 and the total number of groups (4 in this example).

When your RANDARRAY formula returns an error, these are the most obvious reasons to check:

#SPILL error

#value error.

A #VALUE! error may occur in these circumstances:

• If a max value is less than a min value.
• If any of the arguments is non-numeric.

#NAME error

In most cases, a #NAME! error indicates one of the following:

• The function's name is misspelled.
• The function is not available in your Excel version.

#CALC! error

That's how to build a random number generator in Excel with the new RANDARRAY function. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

You may also be interested in.

• How to select random sample in Excel
• How to sort randomly in Excel
• How to generate random numbers in Excel with no repeats
• Flash Fill in Excel with examples

Table of contents

Hello! I am trying to build an Excel formula that will allow me to assign a range of discounts to customer's names. The range is between 10% to 30%. I would like to limit the number of times 30% is returned, 25% is returned, and so on. How do I build this formula? Thank You! Seth

Hi! If you want random percentages between 10% and 30%, use the recommendations from this paragraph in the article above: Generate random numbers between two numbers. For example:

=RANDARRAY(5,1,10%,30%,FALSE)

You can use number rounding functions to get the percentage to a certain accuracy.

=ROUND(RANDARRAY(5,1,10%,30%,FALSE),4)

To create a random value in a single cell, you can also use the RANDBETWEEN function . In the cells with the formula, set the percentage format of the number.

S. No Date Time Shift Day Subject Session Group

S.No Date Time Shift Day Subject Session Group

Formula in excel

Hi! We have a special tutorial that can help to solve your problem: How to convert rows to columns in Excel (transpose data) .

how to random array with the total of each row is 100?

I think you can try to use the custom functions and macros described in this article to create a random array with a specific sum in a row: How to find all combinations of numbers that equal given sum in Excel .

I always get a #name error

Hi, This error most often appears if you specified the function name incorrectly.

Post a comment

#1 Excel tutorial on the net

• Assignment Problem

Formulate the Model | Trial and Error | Solve the Model

Use the solver in Excel to find the assignment of persons to tasks that minimizes the total cost.

Formulate the Model

The model we are going to solve looks as follows in Excel.

1. To formulate this assignment problem , answer the following three questions.

a. What are the decisions to be made? For this problem, we need Excel to find out which person to assign to which task (Yes=1, No=0). For example, if we assign Person 1 to Task 1, cell C10 equals 1. If not, cell C10 equals 0.

b. What are the constraints on these decisions? Each person can only do one task (Supply=1). Each task only needs one person (Demand=1).

c. What is the overall measure of performance for these decisions? The overall measure of performance is the total cost of the assignment, so the objective is to minimize this quantity.

2. To make the model easier to understand, create the following named ranges .

3. Insert the following functions.

Explanation: The SUM functions calculate the number of tasks assigned to a person and the number of persons assigned to a task. Total Cost equals the sumproduct of Cost and Assignment.

Trial and Error

With this formulation, it becomes easy to analyze any trial solution.

For example, if we assign Person 1 to Task 1, Person 2 to task 2 and Person 3 to Task 3, Tasks Assigned equals Supply and Persons Assigned equals Demand. This solution has a total cost of 147.

It is not necessary to use trial and error. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.

Solve the Model

To find the optimal solution, execute the following steps.

1. On the Data tab, in the Analyze group, click Solver.

Note: can't find the Solver button? Click here to load the Solver add-in .

Enter the solver parameters (read on). The result should be consistent with the picture below.

You have the choice of typing the range names or clicking on the cells in the spreadsheet.

2. Enter TotalCost for the Objective.

3. Click Min.

4. Enter Assignment for the Changing Variable Cells.

5. Click Add to enter the following constraint.

Note: binary variables are either 0 or 1.

6. Click Add to enter the following constraint.

7. Click Add to enter the following constraint.

8. Check 'Make Unconstrained Variables Non-Negative' and select 'Simplex LP'.

9. Finally, click Solve.

The optimal solution:

Conclusion: it is optimal to assign Person 1 to task 2, Person 2 to Task 3 and Person 3 to Task 1. This solution gives the minimum cost of 129. All constraints are satisfied.

Learn more, it's easy

• Transportation Problem
• Shortest Path Problem
• Maximum Flow Problem
• Capital Investment
• Sensitivity Analysis
• System of Linear Equations

Download Excel File

• assignment-problem.xlsx

Next Chapter

• Analysis ToolPak

Follow Excel Easy

Become an Excel Pro

• 300 Examples

Assignment Problem • © 2010-2024 Excel is Awesome, we'll show you: Introduction • Basics • Functions • Data Analysis • VBA

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

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

• 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 e nter 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

• Select the range Size : Data Tab >>  Data   Tools >> Data Validation
• In the Data Validation window: in Allow ,choose Custom  >> Enter the formula below.

Repeat the procedure in 1.1 for Error Message .

• Select the range Price and apply the following formula.

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

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

• Click C8 . Enter the Formula
• 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.

• Select F5 . Enter the Formula.

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 .

=LARGE(D5:D13,1)

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

=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

What is ExcelDemy?

Tags: Excel MAX Function

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

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)

Leave a reply Cancel reply

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact  |  Privacy Policy  |  TOS

• User Reviews
• List of Services
• Service Pricing

• Create Basic Excel Pivot Tables
• Excel Formulas and Functions
• Excel Charts and SmartArt Graphics
• Advanced Excel Training
• Data Analysis Excel for Beginners

Advanced Excel Exercises with Solutions PDF

• SI SWIMSUIT
• SI SPORTSBOOK

Dodgers' Max Muncy Making Major Strides in Rehab

Ricardo sandoval | jun 30, 2024.

• Los Angeles Dodgers

Los Angeles Dodgers All-Star infielder Max Muncy is making significant strides in his recovery from his oblique injury. Muncy joined the Dodgers for their three-game series against the San Francisco Giants, took part in swings off a tee, fielded some groundballs, and did some throwing before the first of three games on Friday.

#Dodgers 3B Max Muncy (oblique strain) is in SF and is taking swings off a tee and flips. Also taking ground balls and throwing. — Mike DiGiovanna (@MikeDiGiovanna) June 29, 2024

Max Muncy is here in San Francisco to be in front of the Dodgers’ hitting coaches as his rehab ramps up. He’s started taking swings in the cage and doing some infield work. Still obviously not an option until after the All-Star Break. — Fabian Ardaya (@FabianArdaya) June 29, 2024

Max Muncy sighting at Oracle Park 💪 pic.twitter.com/xwEpcC7yT5 — David Vassegh (@THEREAL_DV) June 28, 2024

Muncy is progressing well from his Grade 1 right oblique strain, which caused him to move to the 60-day IL on June 21. Muncy was initially placed on the 10-day IL on May 17.

The 33-year-old is beginning his ramp-up, and he is on the verge of the next steps of his recovery, which is to take live batting practice before beginning a potential rehab assignment.

While Muncy and the Dodgers are hopeful for a return sooner rather than later, he isn't expected to return to the lineup until after the All-Star break. The lefty slugger last played on May 15 against the Giants and has had a solid start to the 2024 season. Thus far, through 40 games, Muncy has slashed .223/.323/.475 with a .798 OPS, nine home runs, 28 RBIs, 31 hits, and 24 runs in 139 at-bats.

The two-time All-Star takes the Dodgers offense to the next level and is the missing piece to a team that hopes to be the last one standing come late October.

RICARDO SANDOVAL

Ricardo Sandoval | Weekend NewsDesk Manager

Follow @_RicardoSand