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

[Tigereye08]

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 listed down the rows, with averages etc at the end. We would like to calculate the maximum loss from the previous highest value for each item. Obviously we can find the highest value via the MAX formula. However, I’m not sure how to exclude those before that cell, to find the minimum after that.

For example, if row 2 is for item 1, and cell F2 is the maximum in that row, B2 might appear to be the minimum for the whole row, but as that occurred at an earlier date earlier I want to exclude it from my loss calculations and only find the minimum after. Obviously I could drag to include only the cells after the highest, but it could get tiresome to do for every row.

Is there a way to do this automatically with formulas or no?

I’m initially attempting to try it out on my excel 2016 at home, but the idea is to eventually set it up as a template for work projects where we use the Microsoft 365 version of excel, hence having it work automatically via formulas as multiple people input numbers each day would be more ideal.

[#2]

@Tigereye08 

Sub lossfromhighestvalue()

Dim i As Integer
Dim y As Integer
Dim rng As Range
Dim max As Double
Dim rngmin As Range
Dim min As Double

For i = 2 To 19

Set rng = Tabelle1.Range(Cells(i, 2), Cells(i, 6))

max = Application.WorksheetFunction.max(rng)

y = Application.WorksheetFunction.Match(max, Range(Cells(i, 1), Cells(i, 6)), 0)

Set rngmin = Tabelle1.Range(Cells(i, y), Cells(i, 6))

min = Application.WorksheetFunction.min(rngmin)

Cells(i, 10).Value = max - min

Next i

End Sub

Maybe with these lines of code. In the attached file you can click the button in cell H2 to start the macro.

[#3 · Accepted solution]

@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. 

[#4]

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!