Hi - Dave here.
How can you calculate the maximum change between two sets of values with a formula, when the change itself is not part of the data?
This is a classic array formula problem. The solution is simply to perform the change calculation
inside the MAX function. For example, in the worksheet below, the formula to calculate the maximum change between high and low values in cell F5 is:
=MAX(data[High]-data[Low])
[
Download the workbook and read the full explanation]
The tricky part of this formula is that it needs to be entered in a special way in older versions of Excel, with
control + shift + enter. In the dynamic array version of Excel, it "just works". Click the link above to read the article, which includes a full explanation, video links, and the workbook itself.
Excel formulas
We maintain a list of over 500 working formulas
here.
We also offer
video training with practice worksheets.
Have a great weekend!
Dave
