Forum Discussion

Tigereye08's avatar
Tigereye08
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 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.

excel

3 Replies

    • Tigereye08's avatar
      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!
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jun 02, 2022

    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.

Resources