## Explanation

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:

- 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.
- To return the
*minimum*change, change the 3rd argument in SORT to 1 to sort in*ascending*order