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 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 "first in, last out" times from timesheet data, you can use the MAXIFS and MINIFS functions, or an array formula as described below. In the example shown, the formula in H6 is:
=MINIFS(times,names,H5,actions,...

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

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

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 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 find the maximum value on a given weekday (i.e. Monday, Tuesday, Wednesday, etc.) you can use a simple array formula based on the MAX, IF, and TEXT functions. In the example shown, the formula in cell F5 is:
=MAX(...

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 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:
=MAX(INDEX(data,0,MATCH(J4,header,0)))...

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