Forum Discussion

Copper Contributor

Jun 02, 2022

Solved

Calculate loss from highest value, excluding cells/dates before the highest

A bit of a complex question I’m struggling to find a solution to. I have a spreadsheet of values against time, so dates are chronological across the top headers, and the items being measured are list...

excel

OliverScheurich
Jun 02, 2022
Tigereye08
=MAX(B2:F2)-MIN(INDEX(B2:F2,MATCH(MAX(B2:F2),B2:F2,0)):INDEX(B2:F2,5))
An alternative could be this formula.

OliverScheurich

Gold Contributor

Jun 02, 2022

Tigereye08

=MAX(B2:F2)-MIN(INDEX(B2:F2,MATCH(MAX(B2:F2),B2:F2,0)):INDEX(B2:F2,5))

An alternative could be this formula.

Tigereye08
Copper Contributor
Jun 08, 2022
Thank you. With some minor tweaking this formula setup worked perfectly. The macro also worked but needed updating when adding more columns to my original dataset, whereas the formula updates automatically.
Thanks!