Deciphering a Complicated Formula to Calculate Peak to Trough Change

always best to break down into component parts, and where possible to work in from out when evaluating

{=MIN(IFERROR($A$3:$A$63/SUBTOTAL(4,OFFSET($A$3:$A$63,-1,,ROW(INDIRECT("1:"&ROWS($A$3:$A$63))),))-1,""))}

- the ROWS call will return 60, so coupled with the "1:"& this will creates a string --> "1:60"
- the above string is then applied to the outer INDIRECT to, effectively, cast the String to a Range
- the outer ROW call is made against the range

so, at this point, you have a 60 element Array from 1 through 60 - i.e. {1;2;3;4;5;....;60}

this array is used within the OFFSET to generate 60 different ranges relative to the outer range A3:A63

OFFSET(reference,rows,cols,[height],[width])

the [ ] denote non-mandatory

so your OFFSET range reference is A3:A63
- this is initially offset / moved up a row (-1) to become A2:A62
- no change is applied to the columns (i.e. just , ... I don't like this and would use 0 as easier to read-back)
- the optional height variable is where your 1-60 array comes in as it generates 60 ranges of increasing height (from 1 cell to 60 high)
- the optional column variable is left untouched (again done via , I would use explicit value 1 as easier to read-back - i.e. 1 column wide)

so, at this point you now have 60 ranges, A2:A2, A2:A3, A2:A4 etc... through A2:A62

the SUBTOTAL is then applied to each of these 60 ranges, used with option 4 this will return the MAX value from each.

so, at this point, you have 60 values with each value representing the MAX value from each of the 60 ranges

the calc is then dividing each of the 60 MAX values by the respective value in A3:A63, i.e.

- A3 is divided by MAX value from Range 1 (item 1 in the SUBTOTAL array)
- A4 is divisied by MAX value from Range 2 (item 2 in the SUBTOTAL array)
...
- A63 divided by MAX value from Range 60 (item 60 in the SUBTOTAL array)

the outer IFERROR is there to discount any errors that arise c/o division (i.e. if MAX were 0 or error), and replaces these values in the Array with a Null String

so, you know have 60 values in the final array against which you will apply the MIN, these values are either Null strings, or legitimate results of the earlier division

HTH

That's a great answer, thank you very much!!!