MAX function in Excel

Excel TEXT functions return a number in a specified number format

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… read more »

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… read more »

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… read more »

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… read more »

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… read more »

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… read more »

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… read more »

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… read more »

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… read more »

Sidebar