Maximum change

In the example shown, the goal is to calculate the maximum difference between the "High" values in column C and the "Low" values in column D. Because the difference between High and Low is not part of the data, the calculation must occur in the formula itself. This is a classic example of an array formula. 

Excel Table

For convenience, all data is in an Excel Table named data in the range B5:D16. Excel Tables are a convenient way to work with data in Excel because they (1) automatically expand to include new data and (2) offer structured references, which let you refer to data by name instead of by address. If you are new to Excel Tables, this article provides an overview. Also see this short video:

Array formula

This is a classic array formula problem. Subtracting the lows from the highs is an array operation that requires special handling in older versions of Excel. In Legacy Excel, you must enter the formula with control + shift + enter. When you enter the formula this way, you will see the formula enclosed in curly braces like this:

{=MAX(data[High]-data[Low])}

Note: This is an array formula and must be entered with control + shift + enter in Legacy Excel. If you open the workbook in an older version of Excel you will see that Excel automatically adds the curly braces. This is done to make sure the formula works properly. If you re-enter the formula without control + shift + enter, you will see an incorrect result. In the current version of Excel, you will not see the curly braces and it is not necessary to enter the formula in a special way.

Maximum change

To calculate the maximum change, the formula in cell F5 is:

=MAX(data[High]-data[Low])

After the table references are evaluated, we have the high and low values in array form:

=MAX({79;77;76;69;72;76;79;83;85;79;82;83}-{68;69;64;55;59;60;64;69;62;60;73;76})

After subtraction, we have a single array inside the MAX function. The values in this array represent change:

=MAX({11;8;12;14;13;16;15;14;23;19;9;7})

The MAX function returns 23, the maximum change between high and low in this set of data. 

Minimum change

To return the minimum change, replace the MAX function with the MIN function:

​=MIN(data[High]-data[Low])

Absolute change

In the table shown, the high values are always greater than low values, which means the change itself will be a positive number. If you are comparing two columns of data where that is not true, the change will sometimes be negative. If you want to ignore the negative sign, you can add the ABS function to the formula like this:

=MAX(ABS(data[High]-data[Low]))

The ABS function returns the absolute value of a number, so it will convert any negative numbers to positive numbers. The MAX function will then return the maximum absolute value change.

Date of max change

You may also want to know the date of the maximum change. In the worksheet shown, cell G5 contains an INDEX and MATCH formula to return the date associated with the max change:

=INDEX(data[Date],MATCH(F5,data[High]-data[Low],0))

Note: This is an array formula and must be entered with control + shift + enter in Legacy Excel.

The gist of this formula is that we are using the maximum change value like a "key" to locate the date. Most of the work is done in the MATCH function, which calculates the matching row number like this:

=MATCH(F5,data[High]-data[Low],0)
=MATCH(23,{11;8;12;14;13;16;15;14;23;19;9;7},0)
=9

Note that we run the same change calculation explained above inside MATCH as the lookup_array. Then we match the value in cell F5 against all changes in lookup_array, which we know contains the max change. MATCH returns 9 to INDEX as row_num, and INDEX returns the 9-Jun as a final result.

=INDEX(data[Date],9) // returns 9-Jun

Note: if the change values contain duplicates, MATCH will match the first occurrence and INDEX will return the date of the first occurrence.

XLOOKUP

If your version of Excel has XLOOKUP, you can write a more compact version of the date lookup like this:

=XLOOKUP(F5,data[High]-data[Low],data[Date])

The logic is the same as with INDEX and MATCH: we look for the max change in F5 inside an array of calculated changes and return the corresponding date. In addition to offering a more streamlined formula, the XLOOKUP function gives you controls to return the first or last match.

All in one formula

In the dynamic array version of Excel, you can use a formula that creates the entire table in one step:

=LET(
dates,data[Date],
change,data[High]-data[Low],
data,HSTACK(change,dates),
result,TAKE(SORT(data,1,-1),1),
VSTACK({"Max","Date"},result)
)

The screen below shows what this formula looks like in the worksheet:

We use the LET function to assign values to four variables: dates, change, data, and result. The values for dates come from the Date column directly:

=LET(
dates,data[Date],

The values for change come from the change calculation explained above:

​=LET(
dates,data[Date],
change,data[High]-data[Low],

Next we assemble the two columns we want in the final result using the HSTACK function to join change to dates:

=LET(
dates,data[Date],
change,data[High]-data[Low],
data,HSTACK(change,dates),

This creates a two-column array with change values in the first column and date values in the second column. The result is assigned to data. Next, we assign a value to result using SORT and TAKE:

=LET(
dates,data[Date],
change,data[High]-data[Low],
data,HSTACK(change,dates),
result,TAKE(SORT(data,1,-1),1),

Inside TAKE, we use the SORT function to sort data by the first column in descending order. Then we use the TAKE function to retrieve just the first row, which (because we sorted in descending order by change) contains the maximum change and date of maximum change. Last, we assemble our table with the VSTACK function, which returns a final result:

=LET(
dates,data[Date],
change,data[High]-data[Low],
data,HSTACK(change,dates),
result,TAKE(SORT(data,1,-1),1),
VSTACK({"Max","Date"},result)
)

The result is the entire table shown in the range F4:G5.

Notes:

1. To return the nth largest values, change the 2nd argument in TAKE to n. For example, to return the top 3 changes, you can ask TAKE for 3 rows instead of 1 row.
2. To return the minimum change, change the 3rd argument in SORT to 1 to sort in ascending order