Forum Discussion
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...
=MAX(B2:F2)-MIN(INDEX(B2:F2,MATCH(MAX(B2:F2),B2:F2,0)):INDEX(B2:F2,5))An alternative could be this formula.
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 SubMaybe with these lines of code. In the attached file you can click the button in cell H2 to start the macro.