Tracking high (low) values

Hi Gest and welcome to the Forum

You'd need to use VBA (Excel's built-in programming language) to do that, a static IF function won't work.

Suggest you look at a recent answer by top contributor Variatus, here: How to capture additional highest and lowest value for additional Columns

If you just have one value to track, then a simple Worksheet_Change event macro will do it in 2 lines (as follows, commented for your guidance):

Private Sub Worksheet_Change(ByVal Target As Range)

' see if moving cell was changed
If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub

' if more than high cell, make high cell the new value
If Range("B1").Value > Range("B3").Value Then Range("B3").Value = Range("B1").Value

End Sub

Change the cell shaded yellow (B1) and the macro will update B3 in B1 exceeds its current value. You'll need to change the B1 and B3 cell references (one each in bold above) to get it to work for your case. See the first attached file.

If you have a cell which is changed by other cells (as you clarified in the discussion below), the If Intersect.. line can be changed to "see" only the cells used in the foirmula. For example, in the second attached file, there's a simple formula in cell B7:

=B9+B10*D10

and those cells are shaded green. I've modified the macro above so it does what it did (though the code is changed to a block If rather than a single If ..Then Exit...) and tests for changes in the cells used in the formula:

Private Sub Worksheet_Change(ByVal Target As Range)

' see if single cell was changed
If Intersect(Target, Range("B1")) Is Nothing Then
    ' if more than high cell, make high cell the new value
    If Range("B1").Value > Range("B3").Value Then Range("B3").Value = Range("B1").Value
End If

' see if cells used in formula cell have changed
If Intersect(Target, Range("B9,B10,D10")) Is Nothing Then Exit Sub
' if formula is more than formula high cell, make latter cell the new value
If Range("B7").Value > Range("B12").Value Then Range("B12").Value = Range("B7").Value

End Sub

Hope this helps.