CODEWITHSUNDEEP
excelexcel-formulaaggregate
Letokteren
Letokteren

Reputation: 809

Getting the maximum value from an increasing range of cells in Excel

In Excel I store items, and for each item I know how much I used and how much I got at a given time like this:

Item 1  10
Item 2  20
Item 2  -10
Item 2  5
Item 1  10
Item 1  -5

When the value is a positive number, I know that that's a gain, and when it's negative, then that's a loss.

With simply using SUMIF, I could know at the end that how much I have currently from a given item.

However I would like to know what was the maximum amount I ever had. In this example, that number would be 20 for Item 1 for example. I know I could just calculate the partial results in a different column for each item, then use MAX on those, but I would like to find a solution which solves this with one equation.

Upvotes: 1

Views: 273

Answers (2)

user4039065
user4039065

Reputation:

It seems that the newer AGGREGATE¹ function can use its cyclic processing to mimic a 'helper' column of =SUMIFS(B$2:B2, A$2:A2, A2).

In F5 as a standard formula,

=AGGREGATE(14, 6, SUMIFS(OFFSET(B$2, 0, 0, ROW($1:$6), 1), OFFSET(A$2, 0, 0, ROW($1:$6), 1), E5), 1)

aggregate_sumifs

I do not usually proselytize the use of the OFFSET function due to its inherent volatile² nature but reshaping the range to sum demands it.

¹ The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.

² Volatile functions recalculate whenever anything in the entire workbook changes, not just when something that affects their outcome changes. Examples of volatile functions are INDIRECT, OFFSET, TODAY, NOW, RAND and RANDBETWEEN. Some sub-functions of the CELL and INFO worksheet functions will make them volatile as well.

Upvotes: 1

avip
avip

Reputation: 1465

You can add a macro for your sheet with VBA code such as this:

Public itemMax As Integer

Private Sub Worksheet_Activate()
    Set itemMax = -1
End Sub

Private Sub Worksheet_Change(ByVal target As Range)
    If Not (Intersect(target, Range("B1:B100")) Is Nothing) Then
        If IsNumeric(target) Then
            If target > itemMax Then
                itemMax = target
                MsgBox ("New max is: " & CStr(itemMax))
            End If
        End If
    End If
End Sub

In this example, I keep track of the "global max" in itemMax and check if any new item count being added is bigger than purposely-set low max. I assumed you're entering these values in manually, one by one, so YMMV.

Upvotes: 0

Related Questions