Is it possible to define a cell such that it will always remember the highest (or other desired feature) value of a range of cells?

[Rnkhch]

Hello,

I'm wondering if it would be possible to have a formula in a cell to always remember the highest value in a range of cells even when the numbers in those range decrease.

It would be something like B2=MAX($A$1:$A$100), but the problem with the MAX function would be that as soon as the numbers in

$A$1:$A$100 change, it will figure out and display the new max even if it is lower than the old max. But I want the max in B2 to change only if a higher max becomes available at some point in time. So I guess this kind of cell will act as a memory for the highest max in the range. And if this is possible, I guess it would be customizable to retain other desired features like minimum, etc.

Thanks a lot for any input!
​

 

[Yongle]

No
But simple with VBA

right click on sheet tab \ View Code \ Paste code below into code window \ {alt}{F11} to go back to Excel

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim cel As Range, rng As Range
    Set cel = Range("B2")
    Set rng = Range("A1:A100")

    If Not Intersect(rng, Target) Is Nothing Then Range("B2").Value = WorksheetFunction.Max(rng, cel)
End Sub

File now must be saved as Macro Enabled

 

[Rnkhch]

It's wonderful! Thanks a lot! Do you mind including the code for minimum as well (i.e. a cell that would remember the lowest min of the range). Thanks!

 

[Yongle]

Original procedure was triggered by user selecting a different cell
This one is triggered by a change in value any of the cells
To prevent a VBA change triggering another trigger (which can cause problems) Application.EnableEvents is set to false and reset at the end
Delete B2 or D2 to reset or enter any number as the starting min or max

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim celMin As Range, RngMin As Range, celMax As Range, rngMax As Range
    Set celMax = Range("B2")
    Set rngMax = Range("A1:A100")
    Set celMin = Range("D2")
    Set RngMin = Range("C1:C100")
    Application.EnableEvents = False
    If Not Intersect(rngMax, Target) Is Nothing Or Not Intersect(celMax, Target) Is Nothing Then
        celMax.Value = WorksheetFunction.Max(rngMax, celMax)
    End If
    If Not Intersect(RngMin, Target) Is Nothing Or Not Intersect(celMin, Target) Is Nothing Then
        celMin.Value = WorksheetFunction.Min(RngMin, celMin)
    End If
    Application.EnableEvents = True
End Sub

 

[Rnkhch]

Awesome, thanks!