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

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 calculate the total number of hours between a start and end time that overlap a period of interest with a given start and end time, you can use a formula based on the IF, MAX, and MIN functions. In the example...

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

To test if a numeric value falls between two numbers, you can use the AND function with two logical tests. In the example shown, the formula in E5 is:
=AND(D5>MIN(B5,C5),D5<MAX(B5,C5))
How this formula works...

To get the maximum value in a data set based on more than one criteria, 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=G6,IF(item=H6,price...

To calculate the number of days that overlap in two date ranges, you can use basic date arithmetic, together with the the MIN and MAX functions.
In the example shown, the formula in D6 is:
=MAX(MIN(end,C6)-MAX(start,...

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

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

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 calculate gas (MPG) based on odometer readings you can build a table with a few simple formulas. In the example shown, the formulas in E5 and F5 are:
=[@Mileage]-SUM(C4) // E5 calculate mileage
=[@Distance]/[@...

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. In the example, the formula in G6 is:
{=MAX(C5:C12-D5:D12)}
Note: this is an array formula...

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