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 you use or adapt.
Sometimes you encounter data that mixes...

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

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

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

To get the maximum value in a data set based on more than one criteria (i.e. to get MAX IF), you can use an array formula based on the MAX and IF functions. In the example shown, the formula in I6 is:
{=MAX(IF(color=...

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

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

To calculate the longest winning streak, you can use an array formula based on the frequency function.
Note: FREQUENCY must be entered as an array formula using Control + Shift + Enter
How this formula works
This is...

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

To count consecutive monthly orders, you can use an array formula based on the FREQUENCY function, with help from COLUMN and MAX.
In the example shown, the formula in I5 is:
{=MAX(FREQUENCY(IF(C5:H5>0,COLUMN(C5:H5...

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

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

To get the position of the last match (i.e. last occurrence) of a lookup value, you can use an array formula based on the IF, ROW, INDEX, MATCH, and MAX functions. In the example shown, the formula in H6 is:
{=MAX(IF(...

Context
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:...

To find the position (row) of the last file revision in a table, you can use a formula based on several Excel functions: MAX, IF, ISERROR, ROW, and INDEX.
In the example shown, the formula in cell H6 is:
{=MAX(IF(...