Keeping a Max Value in a Cell

Eric has a worksheet that updates portfolio values every minute. Say cell B1 is the total value at the current time. This sum updates once per minute as the data on which it is based is updated. Eric would like to have cell B2 show the maximum value that B1 has ever reached. In other words, B2 will only change when the value in B1 goes higher than what is currently in cell B2.

This could be done with a simple iterative formula in cell B2, like this:

=MAX(B1,B2)

If you don't have iterative calculations enabled, then you will get an error when you first enter this formula. For the formula to work, you will need to allow circular references by following these steps:

1. Display the File tab of the ribbon and then click Options. Excel displays the Excel Options dialog box.
2. At the left side of the dialog box, choose Formulas. (See Figure 1.)



Figure 1. The Formulas tab of the Excel Options dialog box.

3. Make sure the Enable Iterative Calculation check box is selected.
4. Set the Maximum Iterations value to something low (such as 1 or 2).
5. Set the Maximum Change value to something large enough to cover whatever maximum change you may expect to see between what is in cell B2 and what is summed in cell B1. (A good, safe value might be something like 1000.)
6. Click OK.

If you are using Excel 2007, the steps are a bit different—choose Tools | Options | Calculation tab and make sure the Iteration check box is selected.

The formula will work just fine, provided cell B1 never displays an error. If you think that, at some point, B1 might contain an error, then you could modify your formula in cell B2 just a bit:

=IF(ISERROR(B1),B2,MAX(B1,B2))

The result is that B2 is updated only if the value in B1 is not an error and it is greater than what is currently in B2.

If you cannot use iterative calculations for some reason (perhaps because of other formulas in your worksheet), then you may want to use a macro to do the updating of cell B2. If your worksheet data is already being updated via macro, then you could simply modify the macro to recalculate the worksheet and then compare what is in B1 to B2. If B1 is greater, then the macro can update the value in B2.

If your data is not being updated by a macro, then you could create an event handler to do the updating. The following should be placed in the worksheet's code window:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$1" Then
        If Target.Value > Range("B2").Value Then
            Application.EnableEvents = False
            Range("B2").Value = Target.Value
            Application.EnableEvents = True
        End If
    End If
End Sub

Every time cell B1 changes, the macro compares B1 to B2. If it is greater, then events are temporarily turned off and the value in B2 is updated.

Note that this event handler will be triggered if cell B1 is changed explicitly (someone enters a new value in B1) or implicitly (the value in B1 is updated when the worksheet is recalculated). It will not be triggered if the value in B1 is updated via macro or if it is updated by virtue of being linked to a source external to the worksheet. In those cases you might need to modify your comparison macro to run via timer or, perhaps, simply use the circular comparison formula described earlier.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13859) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021.