What is MAX function in Excel? MAX function is one of Statistical functions in Microsoft Excel that returns the largest value in a set of values. Syntax of MAX function 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…
Tag: MAX function
Calculate cap percentage at specific amount in Excel
This tutorial shows how to calculate a percentage based value capped at a specific amount using MIN function. See illustration below: Formula =MIN(A1*percent,1000) Explanation In the example shown, the formula in D6 is: =MIN(C6*10%,1000) How this formula works This formula takes advantage of the fact that the MIN function works fine with small sets of data, even two values. Inside MIN,…
Calculate max or min change in a set of data in Excel
To calculate the max or min change in a set of data as shown, without using a helper column, you can use an array formula. See basic array formula example below: Formula {=MAX(range1-range2)} Explanation In the example, the formula in G6 is: {=MAX(C5:C12-D5:D12)} Note: this is an array formula and myst be entered with control + shift + enter. How this formula works…
Split numbers from units of measure in Excel
To split a number from a unit value, you need to determine the position of the last number. If you add 1 to that position, you have the start of the unit text. Note: these is an experimental formula that uses a hard coded array constant, set down here for reference and comment. Casually tested only, so take care if…
How to extract word containing specific text in Excel
To extract a word that contains specific text,you can use a formula based on several functions, including TRIM, LEFT, SUBSTITUTE, MID, MAX, and REPT. You can use this formula to extract things like email addresses, or other substrings with a unique id. Formula =TRIM(MID(SUBSTITUTE(A1,” “,REPT(” “,99)), MAX(1,FIND(“@”,SUBSTITUTE (A1,” “,REPT(” “,99)))-50),99)) Explanation In the example shown, the formula in C5 is:…
Two ways to sum time over 30 minutes in Excel
To sum the total amount of time over 30 minutes, given a set of times that represent duration, you can use the SUMPRODUCT and TIME functions. Alternatively, use SUMIFS and COUNTIFS functions. Formula =SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0))) Explanation In the example shown, the formula in G5 is: =SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0))) where “times” is the named range C5:C14. How this formula works This formula uses the SUMPRODUCT function to…
Calculate total hours that fall between two times in Excel
Case study You have a start time and an end time, and you want to know how many of the hours in between these two times overlap another time period in the day. For example, you want to know how many hours fall between 8:00 AM and 5:00 PM, or 7:00 PM to 7:00 AM, etc. You might need this information…
Calculate date overlap in days in Excel
If you need to calculate the number of days that overlap in two date ranges, then this tutorials is for you. You can use basic date arithmetic, together with the the MIN and MAX functions. See example below; Formula =MAX(MIN(end1,end2)-MAX(start1,start2)+1,0) Explanation In the example shown, the formula in D6 is: =MAX(MIN(end,C6)-MAX(start,B6)+1,0) How this formula works Excel dates are just serial numbers, so…
Force negative numbers to zero in Excel
This tutorial shows how to Force negative numbers to zero in Excel using the example below; Formula =MAX(value,0) Explanation To force negative numbers to zero without affecting positive numbers, you can use a formula based on the MAX function. In the example shown, the formula in D5 is: =MAX(C5-B5,0) How this formula works This formula takes advantage of the fact…
Position of max value in list in Excel
This tutorial shows how to calculate Position of max value in list in Excel using the example below; Formula =MATCH(MAX(range),range,0) Explanation To get the position of the maximum value in a range (i.e. a list, table, or row), you can use the MAX function together with the MATCH function. In the example shown, the formula in I5 is: =MATCH(MAX(C3:C11),C3:C11,0) Which returns…
Max if criteria match in Excel
This tutorial shows how to calculate Max if criteria match in Excel using the example below; Formula {=MAX(IF(criteria_range=criteria,value_range))} Explanation To look up the maximum value in a range based on specific critieria, you can use a basic array formula based on the IF function. Example data and problem In the example shown, we have almost 10,000 rows of data. The…
Get location of value in 2D array in Excel
This tutorial shows how to Get location of value in 2D array in Excel using the example below; Formula =SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 Explanation To locate the position of a value in a 2D array, you can use the SUMPRODUCT function. In the example shown, the formulas used to locate the row and column numbers of the max value in the array are: =SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1…
Get information corresponding to max value in Excel
This tutorial shows how to Get information corresponding to max value in Excel using the example below; Formula =INDEX(range1,MATCH(MAX(range2),range2,0) Explanation To lookup information related to the maximum value in a range, you can use a formula that combines the MAX, INDEX, and MATCH functions. In the example shown, the formula in I8 is: =INDEX(F3:F11,MATCH(MAX(C3:C11),C3:C11,0)) Which returns the number 3920, representing…
Find longest string with criteria in Excel
This tutorial shows how to Find longest string with criteria in Excel using the example below; Formula {=INDEX(range1,MATCH(MAX(LEN(range1)*(range2=criteria)),LEN(range1)*(range2=criteria),0))} Explanation To find the longest string in a range with criteria, you can use an array formula based on INDEX, MATCH, LEN and MAX. In the example shown, the formula in F6 is: {=INDEX(names,MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0))} Where “names” is the named range C5:C14, and…
Find longest string in column in Excel
This tutorial shows how to Find longest string in column in Excel using the example below; Formula {=INDEX(range,MATCH(MAX(LEN(range)),LEN(range),0))} Explanation To find the longest string (name, word, etc.) in a column, you can use an array formula based on INDEX and MATCH, together with LEN and MAX. In the example shown, the formula in F6 is: {=INDEX(names,MATCH(MAX(LEN(names)),LEN(names),0))} Where “names” is the named…
Extract data with helper column in Excel
This tutorial shows how to Extract data with helper column in Excel using the example below; Formula =IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),””) Explanation One way to extract data in Excel is to use INDEX and MATCH with a helper column that marks matching data. This avoids the complexity of a more advanced array formula. In the example shown, the formula in H6 is: =IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),””)…
Calculate shipping cost with VLOOKUP in Excel
This tutorial shows how to Calculate shipping cost with VLOOKUP in Excel using the example below; Formula =VLOOKUP(weight,table,column,1)*weight Explanation To calculate shipping cost based on weight, you can use the VLOOKUP function. In the example shown, the formula in F8 is: =VLOOKUP(F7,B6:C10,2,1)*F7 This formula uses the weight to find the correct “cost per kg” then calculates the final cost. How…
nth largest value with duplicates in Excel
This tutorial shows how to calculate nth largest value with duplicates in Excel using the example below; Formula =MAX(IF(range<A2,range)) Explanation To get the nth largest value in a data set with duplicates, you can use an array formula based on the MAX and IF functions. Note: the LARGE function will easily return nth values, but LARGE will return duplicates when…
nth largest value in Excel
This tutorial shows how to calculate nth largest value in Excel using the example below; Formula =LARGE(range,nth) Explanation To get the 2nd largest value, 3rd largest value, 4th largest value, and so on, from a set of data, you can use the LARGE function. In the example shown, the formula in J5 is: =LARGE($C5:$G5,2) How this formula works The LARGE…
Maximum value if in Excel
This tutorial shows how to calculate Maximum value if in Excel using the example below; Formula {=MAX(IF(range=criteria,values))} Explanation To get a maximum value based on criteria, you can use the MAX function together with the IF function in an array formula. In the example shown, the formula in cell G6 is: {=MAX(IF(names=F6,times))} Where names is the named range B6:B17, and times is the…
Maximum value in Excel
This tutorial shows how to calculate Maximum value in Excel using the example below; Formula =MAX(range) Explanation To get the maximum value from a set of numbers, use the MAX function. In the example shown, the formula in cell I6 is: =MAX(C6:G6) The MAX function is fully automatic. It accepts one or more arguments, which can be numbers or cell…
Larger of two values in Excel
This tutorial shows how to calculate Larger of two values in Excel using the example below; Formula =MAX(value1,value2) Explanation To get the larger, or greater, of two values, you can use the MAX function. In the example shown, the formula in D5 is: =MAX(B5,C5) How this formula works The MAX function is fully automatic – it returns the largest value…
Max of every nth column in Excel
This tutorial shows how to calculate Max of every nth column in Excel using the example below; Formula {=MAX(IF(MOD(COLUMN(range)-COLUMN(range.first)+1,n)=0,range))} Explanation To get the max of every nth column, you can use an array formula based on the MAX, MOD, and COLUMN functions. In the example shown, the formula in M5 is: =MAX(IF(MOD(COLUMN(B5:K5)-COLUMN(B5)+1,L5)=0,B5:K5)) Note: this is an array formula and must be entered with control…
Excel MAX, MATCH and ADDRESS function illustration
Locate Maximum Value in Excel This example teaches you how to find the cell address of the maximum value in a column. 1. First, we use the MAX function to find the maximum value in column A. 2. Second, we use the MATCH function to find the row number of the maximum value. Explanation: the MATCH function reduces to =MATCH(12,A:A,0), 7. The MATCH function…
Convert Negative Numbers to Zero in Excel
A clever Excel trick to change negative numbers to zero but leave positive numbers unchanged. Below you can find two columns with numbers. To subtract the numbers in column B from the numbers in column A, execute the following steps. 1. Select cell C1. 2. Enter the formula =A1-B1 3. Click the lower right corner of cell C1 and drag the formula down. 4.…
How To Use AGGREGATE function to sum a range with errors in Excel
Excel functions such as SUM, COUNT, LARGE and MAX don’t work if a range includes errors. However, you can easily use the AGGREGATE function to fix this. 1. For example, Excel returns an error if you use the SUM function to sum a range with errors. 2. Use the AGGREGATE function to sum a range with errors. Explanation: the first argument (9) tells…
With vs Without Array Formula in Excel
Array Formulas: Without Array Formula, With Array Formula and F9 Key This chapter helps you understand array formulas in Excel. Single cell array formulas perform multiple calculations in one cell. With Array Formula We don’t need to store the range in column D. Excel can store this range in its memory. A range stored in Excel’s memory is called an array constant. 1. We already…