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 value with variable column

Related functions .

To retrieve the max value in a set of data, where the column is variable , you can use INDEX and MATCH together with the MAX function . In the example shown the formula in J5 is:

where data (B5:F15) and header (B4:F4) are named ranges .

Generic formula

Explanation .

Note: If you are new to INDEX and MATCH, see: How to use INDEX and MATCH

In a standard configuration, the INDEX function retrieves a value at a given row and column. For example, to get the value at row 2 and column 3 in a given range:

However, INDEX has a special trick – the ability to retrieve entire columns and rows. The syntax involves supplying zero for the "other" argument. If you want an entire column, you supply row as zero. If you want an entire row, you supply column as zero:

In the example shown, we want to find the maximum value in a given column. The twist is that the column needs to be variable so it can be easily changed. In F5, the formula is:

Working from the inside out, we first use the MATCH function to get the "index" of the column requested in cell J4:

With "Green" in J4, the MATCH function returns 3, since Green is the third value in the named range header . After MATCH returns a result, the formula can be simplified to this:

With zero provided as the row_number , INDEX returns all values in column 3 of the named range data. The result is returned to the MAX function in an array like this:

And MAX returns the final result, 93.

Minimum value

To get the minimum value with a variable column, you can simply replace the MAX function with the MIN function. The formula in J6 is:

With FILTER

The new FILTER function can also be used to solve this problem, since FILTER can filter data by row or by column. The trick is to construct a logical filter that will exclude other columns. COUNTIF works well in this case, but it must be configured "backwards", with J4 as the range , and header for criteria :

After COUNTIF runs, we have:

And FILTER delivers the 3rd column to MAX, same as the INDEX function above.

As an alternative to COUNTIF, you can use ISNUMBER + MATCH instead:

The MATCH function is again set up "backwards", so that we get an array with 5 values that will serve as the logical filter. After ISNUMBER and MATCH run, we have:

And FILTER again delivers the 3rd column to MAX.

Related formulas

• Look up entire row

• Sum entire column

• Look up entire column

Related functions

• INDEX Function

The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column numbers....

• MATCH Function

MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, MATCH is combined with the...

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

• FILTER Function

The Excel FILTER function is used to extract matching values from data based on one or more conditions. The output from FILTER is dynamic. If source data or criteria change, FILTER will return a new set of results. This makes FILTER a flexible way to isolate and inspect data without altering the...

• COUNTIF Function

The Excel COUNTIF function returns the count of cells in a range that meet a single condition. The generic syntax is COUNTIF(range, criteria), where "range" contains the cells to count, and "criteria" is a condition that must be true for a cell to be counted. COUNTIF can be used to count cells...

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.

Statistics Made Easy

Excel: How to Find Max Value by Group

Often you may want to find the max value of some dataset in Excel based on a category or group.

For example, suppose we have the following dataset and we’d like to find the max value of “points” for each team:

The following step-by-step example shows how to do so.

Step 1: Enter the Data

First, enter the data values into Excel:

Step 2: Find the Unique Groups

Next, we need to use the =UNIQUE() function to produce a list of unique team names.

In our example, we’ll type the following formula in cell D2 :

This will produce a list of unique teams:

Step 3: Find the Max Value by Group

Next, we will use the following formula to find the max points scored by each team:

We will type this formula into cell E2 and then copy and paste it down to the remaining cells in column E:

This tells us:

• The max points scored by players on the Mavs is 26 .
• The max points scored by players on the Warriors is 19 .
• The max points scored by players on the Lakers is 33 .
• The max points scored by players on the Heat is 19 .
• The max points scored by players on the Celtics is 29 .

Note : To calculate the minimum points scored by each team, simply replace the MAX in the formula with MIN .

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Count by Group in Excel How to Calculate the Sum by Group in Excel How to Calculate a Five Number Summary in Excel

Featured Posts

Hey there. My name is Zach Bobbitt. I have a Masters of Science degree in Applied Statistics and I’ve worked on machine learning algorithms for professional businesses in both healthcare and retail. I’m passionate about statistics, machine learning, and data visualization and I created Statology to be a resource for both students and teachers alike.  My goal with this site is to help you learn statistics through using simple terms, plenty of real-world examples, and helpful illustrations.

One Reply to “Excel: How to Find Max Value by Group”

The formula “=MAX(IF(A:A=D2,B:B))” only works like that when you enter it holding “Ctrl + Shift + Enter.” Otherwise it’s only giving you the MAX in B:B and that’s it. With this trick you tell excel it’s an arrayformula… works in google sheets, too.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Join the Statology Community

Sign up to receive Statology's exclusive study resource: 100 practice problems with step-by-step solutions. Plus, get our latest insights, tutorials, and data analysis tips straight to your inbox!

By subscribing you accept Statology's Privacy Policy.

• Ablebits blog
• Excel formulas

Basic Excel formulas & functions with examples

The tutorial provides a list of Excel basic formulas and functions with examples and links to related in-depth tutorials.

Being primarily designed as a spreadsheet program, Microsoft Excel is extremely powerful and versatile when it comes to calculating numbers or solving math and engineering problems. It enables you to total or average a column of numbers in the blink of an eye. Apart from that, you can compute a compound interest and weighted average, get the optimal budget for your advertising campaign, minimize the shipment costs or make the optimal work schedule for your employees. All this is done by entering formulas in cells.

This tutorial aims to teach you the essentials of Excel functions and show how to use basic formulas in Excel.

The basics of Excel formulas

Before providing the basic Excel formulas list, let's define the key terms just to make sure we are on the same page. So, what do we call an Excel formula and Excel function?

• Function is a predefined formula already available in Excel. Functions perform specific calculations in a particular order based on the specified values, called arguments, or parameters.

For example, instead of specifying each value to be summed like in the above formula, you can use the SUM function to add up a range of cells: =SUM(A2:A4)

Clicking the function's name will turn it into a blue hyperlink, which will open the Help topic for that function.

Tip. You don't necessarily have to type a function name in all caps, Microsoft Excel will automatically capitalize it once you finish typing the formula and press the Enter key to complete it.

10 Excel basic functions you should definitely know

What follows below is a list of 10 simple yet really helpful functions that are a necessary skill for everyone who wishes to turn from an Excel novice to an Excel professional.

The first Excel function you should be familiar with is the one that performs the basic arithmetic operation of addition:

In the syntax of all Excel functions, an argument enclosed in [square brackets] is optional, other arguments are required. Meaning, your Sum formula should include at least 1 number, reference to a cell or a range of cells. For example:

=SUM(B2:B6) - adds up values in cells B2 through B6.

=SUM(B2, B6) - adds up values in cells B2 and B6.

If necessary, you can perform other calculations within a single formula, for example, add up values in cells B2 through B6, and then divide the sum by 5:

=SUM(B2:B6)/5

To sum with conditions, use the SUMIF function: in the 1st argument, you enter the range of cells to be tested against the criteria (A2:A6), in the 2nd argument - the criteria itself (D2), and in the last argument - the cells to sum (B2:B6):

=SUMIF(A2:A6, D2, B2:B6)

Useful resources:

• Excel Sum formula examples - formulas to total a column, rows, only filtered (visible) cells, or sum across sheets.
• Excel AutoSum - the fastest way to sum a column or row of numbers.
• SUMIF in Excel - formula examples to conditionally sum cells.
• SUMIFS in Excel - formula examples to sum cells based on multiple criteria.

The Excel AVERAGE function does exactly what its name suggests, i.e. finds an average, or arithmetic mean, of numbers. Its syntax is similar to SUM's:

Having a closer look at the formula from the previous section ( =SUM(B2:B6)/5 ), what does it actually do? Sums values in cells B2 through B6, and then divides the result by 5. And what do you call adding up a group of numbers and then dividing the sum by the count of those numbers? Yep, an average!

The Excel AVERAGE function performs these calculations behind the scenes. So, instead of dividing sum by count, you can simply put this formula in a cell:

=AVERAGE(B2:B6)

To average cells based on condition, use the following AVERAGEIF formula, where A2:A6 is the criteria range, D3 is he criteria, and B2:B6 are the cells to average:

• Excel AVERAGE - average cells with numbers.
• Excel AVERAGEA - find an average of cells with any data (numbers, Boolean and text values).
• Excel AVERAGEIF - average cells based on one criterion.
• Excel AVERAGEIFS - average cells based on multiple criteria.
• How to calculate weighted average in Excel
• How to find moving average in Excel

MAX & MIN

The MAX and MIN formulas in Excel get the largest and smallest value in a set of numbers, respectively. For our sample data set, the formulas will be as simple as:

=MAX(B2:B6)

• MAX function - find the highest value.
• MAX IF formula - get the highest number with conditions.
• MAXIFS function - get the largest value based on multiple criteria.
• MIN function - return the smallest value in a data set.
• MINIFS function - find the smallest number based on one or several conditions.

COUNT & COUNTA

If you are curious to know how many cells in a given range contain numeric values (numbers or dates), don't waste your time counting them by hand. The Excel COUNT function will bring you the count in a heartbeat:

While the COUNT function deals only with those cells that contain numbers, the COUNTA function counts all cells that are not blank , whether they contain numbers, dates, times, text, logical values of TRUE and FALSE, errors or empty text strings (""):

For example, to find out how many cells in column B contain numbers, use this formula:

=COUNT(B:B)

To count all non-empty cells in column B, go with this one:

=COUNTA(B:B)

In both formulas, you use the so-called "whole column reference" (B:B) that refers to all the cells within column B.

• Excel COUNT function - a quick way to count cells with numbers.
• Excel COUNTA function - count cells with any values (non-empty cells).
• Excel COUNTIF function - count cells that meet one condition.
• Excel COUNTIFS function - count cells with several criteria.

Judging by the number of IF-related comments on our blog, it's the most popular function in Excel. In simple terms, you use an IF formula to ask Excel to test a certain condition and return one value or perform one calculation if the condition is met, and another value or calculation if the condition is not met:

For example, the following IF statement checks if the order is completed (i.e. there is a value in column C) or not. To test if a cell is not blank, you use the "not equal to" operator ( <>) in combination with an empty string (""). As the result, if cell C2 is not empty, the formula returns "Yes", otherwise "No":

• IF function in Excel with formula examples
• How to use nested IFs in Excel
• IF formulas with multiple AND/OR conditions

If your obviously correct Excel formulas return just a bunch of errors, one of the first things to check is extra spaces in the referenced cells (You may be surprised to know how many leading, trailing and in-between spaces lurk unnoticed in your sheets just until something goes wrong!).

There are several ways to remove unwanted spaces in Excel, with the TRIM function being the easiest one:

For example, to trim extra spaces in column A, enter the following formula in cell A1, and then copy it down the column:

• Excel TRIM function with formula examples
• How to delete line breaks and non-printing characters
• How to remove non-breaking spaces (&nbsp;)
• How to delete a specific non-printing character

Whenever you want to know the number of characters in a certain cell, LEN is the function to use:

Wish to find out how many characters are in cell A2? Just type the below formula into another cell:

Want to get the total count of characters in a range or cells or count only specific characters? Please check out the following resources.

• Excel LEN formulas to count characters in a cell
• Count the number of characters in cells and ranges

AND & OR

These are the two most popular logical functions to check multiple criteria. The difference is how they do this:

• AND returns TRUE if all conditions are met, FALSE otherwise.
• OR returns TRUE if any condition is met, FALSE otherwise.

While rarely used on their own, these functions come in very handy as part of bigger formulas.

For example, to check the test results in columns B and C and return "Pass" if both are greater than 60, "Fail" otherwise, use the following IF formula with an embedded AND statement:

=IF(AND(B2>60, B2>60), "Pass", "Fail")

If it's sufficient to have just one test score greater than 60 (either test 1 or test 2), embed the OR statement:

• Excel AND function with formula examples
• Excel OR function with formula examples

CONCATENATE

In case you want to take values from two or more cells and combine them into one cell, use the concatenate operator (&) or the CONCATENATE function:

For example, to combine the values from cells A2 and B2, just enter the following formula in a different cell:

=CONCATENATE(A2, B2)

To separate the combined values with a space, type the space character (" ") in the arguments list:

• How to concatenate in Excel - formula examples to combine text strings, cells and columns.
• CONCAT function - newer and improved function to combine the contents of multiple cells into one cell.

TODAY & NOW

To see the current date and time whenever you open your worksheet without having to manually update it on a daily basis, use either:

=TODAY() to insert the today's date in a cell.

=NOW() to insert the current date and time in a cell.

• How to insert today's date in Excel - different ways to enter the current date in Excel: as an unchangeable time stamp or automatically updatable date and time.
• Excel date functions with formula examples - formulas to convert date to text and vice versa, extract a day, month or year from a date, calculate the difference between two dates, and a lot more.

Best practices for writing Excel formulas

Now that you are familiar with the basic Excel formulas, these tips will give you some guidance on how to use them most effectively and avoid common formula errors.

Do not enclose numbers in double quotes

Any text included in your Excel formulas should be enclosed in "quotation marks". However, you should never do that to numbers, unless you want Excel to treat them as text values.

For example, to check the value in cell B2 and return 1 for "Passed", 0 otherwise, you put the following formula, say, in C2:

=IF(B2="pass", 1, 0)

Copy the formula down to other cells and you will have a column of 1's and 0's that can be calculated without a hitch.

Now, see what happens if you double quote the numbers:

=IF(B2="pass", "1", "0")

Don't format numbers in Excel formulas

Match all opening and closing parentheses, copy the same formula to other cells instead of re-typing it.

Note. After copying the formula, make sure that all cell references are correct. Cell references may change depending on whether they are absolute (do not change) or relative (change).

How to delete formula, but keep calculated value

When you remove a formula by pressing the Delete key, a calculated value is also deleted. However, you can delete only the formula and keep the resulting value in the cell. Here's how:

• Select all cells with your formulas.
• Press Ctrl + C to copy the selected cells.
• Right-click the selection, and then click Paste Values > Values to paste the calculated values back to the selected cells. Or, press the Paste Special shortcut: Shift+F10 and then V .

Make sure Calculation Options are set to Automatic

If all of a sudden your Excel formulas have stopped recalculating automatically, most likely the Calculation Options somehow switched to Manual . To fix this, go to the Formulas tab > Calculation group, click the Calculation Options button, and select Automatic .

This is how you make and manage basic formulas in Excel. I how you will find this information helpful. Anyway, I thank you for reading and hope to see you on our blog next week.

You may also be interested in

• How to make formulas in Excel
• Microsoft Excel formulas with examples
• Excel formulas not working, not updating, not calculating

Table of contents

409 comments

I need the Excel formula number combination

One hundred and ten units (HTF) : 345,345,678,890,567

Summary of analysis results from 5 data If there is no incorrect information 036-037-046-047-056-057-058-158-258-358 368-369-378-379-458-468-469-478-479-558 568-569-578-579-588-589 (26 sets / total 26 sets)

Sorry, I have no idea exactly what the task is. It's hard to tell exactly what you're asking for as it's currently written.

For me personally It's clear that actually "seeing" something written down is easier to understand and remember than "hearing" it, but both together is even better.

Post a comment

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

• Post published: November 8, 2019

Explanation

MAX function is used when we want to find the maximum value within a range of cells.

Practice MAX here

The syntax of the MAX function is as follows:

=MAX(value1,value2,value3…)

We can insert into the MAX function a range of cells, for example:

=MAX(A1:A4)

The function above will look within the cells in range A1 to A4, and return the highest value.

Practice MAX function – Execise 1

Now, let’s try to find the maximum value using MAX function:

Practice MAX function – Execise 2

Enter our  Excel Exercises Page to learn and practice MAX and other common Excel formulas!

Having an issue with the formulas' language? check out this post

• Yes, that would be helpful!
• I don't know
• Intermediate

Terms and Conditions - Privacy Policy

• 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
• About Us Our Team

Feature Tutorials

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

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!

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

7 ways to speed up your excel spreadsheets.

Don't twiddle your thumbs waiting for Excel to respond.

Quick Links

• Don’t Over-Format Your Sheet
• Compress (and Limit) Your Images
• Simplify Your Formulas
• Avoid Blank Rows and Columns
• Limit Changing Values
• Clear Up Named Items
• Check Performance

Busy Excel spreadsheets can—frustratingly—grind to a halt, especially as you use the many functions and formatting options. The last thing you want is your worksheet to pause while executing a simple calculation, so check out these tips to keep your Excel spreadsheet running smoothly.

We recommend using the 64-bit version of Excel (which is installed automatically unless you manually select the 32-bit version), as it works better with large data sets, add-ins, and many other modern-day Excel features.

1. Don’t Over-Format Your Sheet

While you might think that adding formatting can spruce up your Excel worksheet, not only can it make your spreadsheet more difficult to read , but it can also slow it down. Any time you add formatting, whether that be using a fancy font, various font sizes, a range of colors, or different borders, you're adding to the volume of the file. There are a couple of ways around this.

If you look at your spreadsheet and see that it does have lots of formatting, and you want to start afresh, press Ctrl+A to select all cells. Then, in the Home tab on the ribbon, head to the Editing group and click the "Clear" drop-down option. From there, click "Clear Formats."

Alternatively, if you would prefer to keep certain formatting but remove other elements, after pressing Ctrl+A, work through the Font group in the Home tab to choose which formatting you want to remove.

Finally, you might have hidden formatting in the form of conditional formatting. Reducing the use of conditional formatting in your spreadsheet by managing the conditional formatting rules will help you to reduce the slugishness of your file.

2. Compress (and Limit) Your Images

High-resolution and large images and graphics within your spreadsheet will massively increase your file size. The best way to avoid this is to reduce the number of images and graphics in your workbook altogether, but if they are essential, use Excel's in-built compression tool.

Select any image within your workbook and, in the Picture Format tab on the ribbon, click "Compress Pictures." In the resultant dialog box, you can check the "Apply Only To This Picture" box if you only want to compress this image, or uncheck it if you want to apply the settings to all images in the file. Then, choose the best resolution that will work for you. If you have lots of images, choose the lowest resolution (96 pixels per inch). Finally, click "OK."

3. Simplify Your Formulas

It's inevitable that you'll have formulas within your Excel workbook—after all, one of Excel's biggest strengths is being able to perform dynamic calculations. However, complex formulas with lots of cell references and nested functions can be a huge contributor to a sluggish worksheet.

There are several ways to simplify your formulas. The first is to use named ranges within your formulas. Instead of referencing individual cells or ranges of cells within your formulas, referencing a named range means that Excel can more quickly identify and scan the specified data without having to work through a complex formula with lots of different cell references. What's more, as well as speeding up your worksheet's processing time, using named ranges makes your formulas easier to write, review, amend, and duplicate. The first step is to assign a name to a range of cells , and then use this name within your formula. For example, if we had a set of data in cells A1, B3, C5, D2, E9, F3, and G4, and we wanted to use these numbers to calculate an average, rather than typing

=AVERAGE(A1,B3,C5,D2,E9,F3,G4)

if we were to create a name—such as Results—to these cells, the formula would instead read

=AVERAGE(results)

Another way to simplify your formulas is to break down complex calculations into smaller ones. For example, if you wanted to work out the annual profit made by several employees for the past five years, rather than involving dozens of cell references, you could break down their profits per quarter, and then create an overall sum at the end of your row or bottom of your column.

Not only does this reduce the complexity of your formulas, but it also makes for easier analysis of your numbers.

Finally, instead of using complicated, nested IF formulas, you could use alternative functions. Depending on what you want to calculate, you could try using the VLOOKUP , CHOOSE, or LET functions, which require fewer arguments.

4. Avoid Blank Rows and Columns

Instead of having blank rows and columns to separate your data, use borders and colors. Having blank rows and columns increases your workbook's file size and can delay Excel's calculation processes. This will not significantly impact smaller workbooks, but having many blank rows and columns in between larger sets of data will. Along with the other tips in this article, removing blank rows and columns will definitely help to tidy up your Excel workbook and improve its performance.

5. Limit Changing Values

Certain functions in Excel change the value they produce any time Excel recalculates in your spreadsheet. For example, the =RAND function produces a new random number each time you make any changes to your worksheet, and =TODAY will update the cell value to reflect the current date. These are called volatile functions.

Because Excel has to continually work to update the values in the cells where you have volatile functions, it may cause a lag in your worksheet's response time, and the more volatile functions you have, the lengthier the lag.

Other examples of volatile functions include =NOW, =RANDBETWEEN, =RANDARRAY, =OFFSET, and =INDIRECT.

The obvious way to limit the processing impact of volatile functions is to reduce their use altogether. For example, if you want Excel to produce a random list of numbers using =RANDBETWEEN, once the calculation has been made by Excel, consider making these values permanent. To do this, highlight all the numbers created by the calculation and press Ctrl+C.

Then, select the top-left cell of the random array. In the Home tab on the ribbon, click the paste drop-down arrow, and choose the "Values" icon.

You will see that the numbers change within your parameters one more time (as they were still volatile right up to the point when you pasted their values), but they are now in your spreadsheet as true numbers and will not change when Excel recalculates.

Another way to prevent volatile functions from slowing down your spreadsheet is to turn off automatic calculations. In the Calculation group of the Formulas tab on the ribbon, click "Calculation Options" and check "Manual" to tell Excel not to update volatile functions automatically. Then, when you're ready for your volatile functions to update, click "Calculate Sheet."

This means that they will only potentially slow down your spreadsheet when you're prepared for this to happen, rather than with every change you make.

6. Clear Up Named Items

As we mentioned in tip 3, using named ranges can help you to simplify your formulas and generally tidy up your workbook, but it's important to make sure you don't have redundant names or multiple names for the same array of data. Doing so will add an extra layer of processing that Excel has to address when you're opening up, working in, and closing your workbook.

To clear up all the named items in your workbook, in the Formulas tab on the ribbon, click "Name Manager." When the dialog box opens, you'll see all the named ranges in your workbook. Review these manually, clicking "Delete" to remove any unused named ranges and clicking "Edit" to tidy up their names.

7. Check Performance

If you have exhausted all the tips above and find that your workbook is still under-performing, you can force Excel to run through a check-up on your file. Originally introduced to Excel for the web in 2022 and extended to the Windows Excel app in 2024, Excel's Check Performance tool looks for excess formatting, unneeded metadata, unused styles, and other issues that might affect the speed of your workbook.

In the Review tab on the ribbon, head to the Performance group and click "Check Performance."

A sidebar will then open on the right-hand side of your window containing guidance about what you can do to improve your workbook's performance.

If you've run through these tips and find that Excel is still working slowly, maybe the cause of the issue is not the Microsoft 365 app—you may need to increase your overall PC performance instead .

Success! Subscription added.

Success! Subscription removed.

Sorry, you must verify to complete this action. Please click the verification link in your email. You may re-send via your profile .

• Intel Community
• Product Support Forums
• Programmable Devices

Max 10 pin assignment issue

• Subscribe to RSS Feed
• Mark Topic as New
• Mark Topic as Read
• Float this Topic for Current User
• Printer Friendly Page

• Mark as New
• Report Inappropriate Content

Programming (CPLD|Config Dev)

View solution in original post

• All forum topics
• Previous topic

Link Copied

Community support is provided during standard business hours (Monday to Friday 7AM - 5PM PST). Other contact methods are available here .

Intel does not verify all solutions, including but not limited to any file transfers that may appear in this community. Accordingly, Intel disclaims all express and implied warranties, including without limitation, the implied warranties of merchantability, fitness for a particular purpose, and non-infringement, as well as any warranty arising from course of performance, course of dealing, or usage in trade.

For more complete information about compiler optimizations, see our Optimization Notice .

• ©Intel Corporation
• Terms of Use
• *Trademarks
• Supply Chain Transparency

How to Calculate Average, Minimum And Maximum in Excel

Calculating Average, Minimum and Maximum is a common and simple task in Excel. There are several methods to do it. In this article, you will learn how to calculate average, minimum, and maximum in Excel with 4 easy methods.

Before going to the next part, let’s have a quick look at the briefs of the methods we are using here and the relevant output from them.

How to Calculate Average, Minimum And Maximum in Excel: 4 Easy Methods

Let’s get introduced to our dataset first. I have made a dataset that represents some foods’ prices.

Let’s see how we can calculate average, minimum, and maximum in Excel considering the price of the dataset.

Method 1: Use Functions to Calculate Average, Minimum And Maximum in Excel

First of all, we will see the use of different Excel functions for serving our purpose.

The AVERAGE function calculates the average (arithmetic mean) of a group of numbers.

The MIN function returns the smallest value from the numbers provided.

The MAX function returns the highest value in a set of data.

Let’s utilize these functions.

➦ Firstly, in Cell C13 type the formula given below-

➦ Then, hit the ENTER button and the average will be evaluated.

➦ Afterward, activate Cell C14 and write the formula-

➦ Then, press the ENTER button now to get the minimum value.

➦ Further, type the formula in Cell C15 which is given below-

➦ Then, just hit the ENTER button to find the maximum value.

Read More: How to Calculate Average of Top 5 Values in Excel

Method 2: Applying AutoSum Tool

Here, we’ll do the same operation using the Excel AutoSum tool from the Home ribbon. First, I’ll show how to find the average.

➦ Firstly, activate Cell C13 .

➦ Then, Click Home > Editing > AutoSum > Average .

➦ Afterward, select the data range and click the Enter button.

Now, to find the minimum and maximum-

➦ Simply, click HOME > Editing > AutoSum > Min/Max, and then the other steps are the same.

Read More: How to Average Values Greater Than Zero in Excel

Method 3: Utilizing Auto-Stats Feature

It is one of the simplest ways to find the average, minimum, and maximum.

➦ Here, all you have to do is => just select the data range and Excel will automatically show the Average , Min, and Max values in the status bar of your sheet.

Method 4: Create Pivot Table to Calculate Average Minimum And Maximum

In our last method, we’ll calculate the average, minimum, and maximum by using the Pivot Table . A pivot table in Excel is an extraction or resumé of your original table with source data. A pivot table can provide quick answers to questions about your table.

➦ Firstly, click as follows: Insert > PivotTable .

➦ Consecutively, a dialog box will open up.

➦ Afterward, select where you will create the table and set the location. I have selected Existing Worksheet and Cell E4 as the location.

➦ Then, press OK .

➦ Instantly, a dialog box named ‘ PivotTable Fields ’ will appear.

➦ Further, mark on Products and Price option in the field.

➦ Now, click Sum of Price > Value Field Settings .

➦ Value Field Settings dialog box will appear.

➦ Then, select Average from the ‘ Summarize Values By’ option and press OK .

See the image below and the average price is now shown in the table.

➦ Now, to find the minimum or maximum just select Max/Min from the ‘ Summarize Values By ’ option and press OK .

Read More: How to Average Negative and Positive Numbers in Excel

How to Find Average, Minimum and Maximum Value with Condition in Excel

Let’s say, we are in need to calculate average, minimum, and maximum values after meeting a certain criterion. We have modified our data. The product: “ Milk ” has occurred a few times and we want to calculate the average, minimum and maximum price of “ Milk ” only.

For this purpose, we will successfully apply the AVERAGEIF function , and combine IF with MAX and MIN functions.

➦ Firstly, in Cell F5 type the formula given below-

=AVERAGEIF(B5:B14,B6,C5:C14)

=MIN(IF(B5:B14=F4,C5:C14))

 💡  Formula Breakdown

The IF function looks for the cell value of F4 in the range B5:B14 and returns the corresponding value of F4 from the range C5:C14 .

So, Output => {FALSE;15;FALSE;20;FALSE;25;FALSE;30;FALSE;40}

Finally, the MIN function returns the minimum value from the array.

MIN(IF(B5:B14=F4,C5:C14)) = MIN({FALSE;15;FALSE;20;FALSE;25;FALSE;30;FALSE;40}) = 15

So, final Output => 15

=MAX(IF(B5:B14=F4,C5:C14))

Read More: How to Average Filtered Data in Excel

Download Practice Book

You can download the free Excel template from here and practice on your own.

I hope all of the methods described above will be useful enough to calculate the average, minimum, and maximum in Excel. Feel free to ask any questions in the comment section and please give me feedback.

Related Articles

• Average Attendance Formula in Excel
• How to Calculate Average of Averages in Excel
• How to Calculate Average True Range in Excel
• How to Calculate Average Percentage in Excel
• How to Calculate Average Percentage of Marks in Excel
• How to Calculate Class Average in Excel
• How to Calculate Average Revenue in Excel
• How to Calculate Average Quarterly Revenue in Excel
• How to Calculate Average Share Price in Excel
• How to Calculate Average Length of Stay in Excel
• How to Calculate Average Price in Excel

<< Go Back to Calculate Average in Excel | How to Calculate in Excel  | Learn Excel

What is ExcelDemy?

Tags: How to Calculate Average in Excel

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

I’m confused. I download the excel file and send a copy to my drive. Maybe anyday you delete this excel file which you share here & i download it then send my drive & save in excel when you delete this file can i there’s a chance to lost my saved this file

Hello Mahedi, thanks for your feedback. When you download the file then there’s no connection between your downloaded file and our uploaded file. So, no worries, your file won’t lose.

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

The Colorado Sun

Telling stories that matter in a dynamic, evolving state.

2nd teen pleads guilty in Jeffco rock-throwing spree that killed 20-year-old Alexa Bartell

Share this:

• Click to share on Facebook (Opens in new window)
• Click to share on Reddit (Opens in new window)
• Click to share on X (Opens in new window)
• Click to email a link to a friend (Opens in new window)
• Original Reporting

The Trust Project

A second teen accused of throwing landscaping rocks at a string of cars, killing a 20-year-old woman and injuring several others, pleaded guilty to murder charges Wednesday.

Nicholas Karol-Chik, 19, pleaded guilty to second-degree murder in the death of Alexa Bartell, and criminal attempt to commit first-degree murder in attacks that targeted nine other victims, according to the First Judicial District Attorney’s Office. He also pleaded guilty to a sentence enhancer of criminal attempt to commit first-degree murder.

Under a plea deal, 15 other charges were dropped, the office said. Karol-Chik faces a minimum sentence of 35 years and a maximum of 72 years in prison. 

His sentencing date is scheduled for Sept. 10.

Karol-Chik, Zachary Kwak and Joseph Koenig — who were all 18 at the time — were arrested and accused of hurling large rocks April 19 at seven cars , including the one that killed Bartell as she drove on Indiana Street in Jefferson County. 

When investigators found Bartell’s car, there was a large hole in the front windshield on the driver’s side and the rear window was completely broken out. Bartell appeared to have a wound to her head and was pronounced dead at the scene, according to an arrest affidavit. 

The three men were traveling at 80 mph when a 4-to-6 inch landscaping rock struck Bartell’s car, a detective with the Jefferson County Sheriff’s Office testified in a previous hearing. After the attack, all three men returned to her car stranded hundred of feet off the road in a field to take a photo as a memento . None called police before returning to their suburban Denver homes, he said. 

Seven cars were damaged and three drivers, not including Bartell, were injured. Some rocks narrowly missed the head of one driver and flung shattered glass into the eyes of others, according to previous court testimony. 

☀️ READ MORE

Xcel may shut off power more often to avoid wildfires. regulators have 8 questions about how that might work., colorado lawmakers passed 10 new water measures this year. these are the biggest ones., bad child protection caseworkers can hop from county to county in colorado undetected. new rules are coming..

In accepting the plea, Karol-Chik’s attorneys said he was in the front passenger seat when he gave Koenig, who was driving, the large landscaping rock that Koenig threw at Bartell, killing her. Karol-Chik also threw a rock and concrete on his own at another driver. 

Karol-Chik “knowingly engaged in conduct which created a grave risk of death, under circumstances evidencing an attitude of universal malice manifesting extreme indifference to the value of human life,” the First Judicial District Attorney’s Office said in a news release Wednesday. 

Kwak, 19, pleaded guilty May 10 to first-degree assault, second-degree assault and criminal attempt to commit second-degree assault, according to online court records.

Koenig is expected in court July 3 and his jury trial is scheduled to begin July 19, records show.

Type of Story: News

Based on facts, either observed and verified directly by the reporter, or reported and verified from knowledgeable sources.

Olivia Prentzel General Assignment Reporter

Olivia Prentzel covers breaking news and a wide range of other important issues impacting Coloradans for The Colorado Sun, where she has been a staff writer since 2021. At The Sun, she has covered wildfires, criminal justice, the environment,... More by Olivia Prentzel

Posted May 16, 2024

At 4:34 PM UTC

MoviePass, MovieCrash — director Muta’Ali’s documentary chronicling how two growth-obsessed executives ran the innovative theater subscription service into the ground — made a spash at this year’s SXSW . And now the movie’s slated to make its streaming debut on Max later this month.

iPhone owners say the latest iOS update is resurfacing deleted nudes

Google opens up its smart home to everyone and will make google tvs home hubs, someone finally made a heat pump that looks good inside your home, microsoft announces the proteus controller, a gamepad for xbox gamers with disabilities, google sheets’ new formatting feature has excel switchers excited, more from this stream the struggles of moviepass, the film subscription service, moviepass subscribers can finally get tickets online., the execs behind the moviepass debacle are now facing criminal charges, you can now sign up for whatever the new moviepass is going to be, moviepass is officially coming back.