Need Function to get previous calculated value of a cell

Question

New to the Community and VBA so please bear with me. I'm using Microsoft 365 Excel v16.82 on a Mac.

I'd like to be able to use a previous cell's calculated value to compare with its new value and update it if it exceed the previous maximum value. I have been able to do so with a macro SUB, but would like to implement as a FUNCTION to make it more portable throughout the spreadsheet.

B1 and B2 reference calculated values from other cells and are updated periodically. They are summed in B3. If the sum exceeds a previous maximum in B4, the new max value should replace B4.

Is this possible? Any help is greatly appreciated.

rodrigo_ · Answer

Hello&nbsp;johnnynaunny&nbsp;&nbsp;You can create a custom function in VBA to achieve this. Here’s an example of how you might write this function:&nbsp;Function UpdateMax(currentValue As Double, previousMax As Double) As Double
    If currentValue &gt; previousMax Then
        UpdateMax = currentValue
    Else
        UpdateMax = previousMax
    End If
End Function&nbsp;&nbsp;Then use the created function in your cell B4&nbsp;=UpdateMax(B3, B4)&nbsp;&nbsp;

johnnynaunny · Answer

Thanks for the quick reply. Unfortunately using B4 as am input and output creates a circular ref.

Forum Discussion

Need Function to get previous calculated value of a cell

2 Replies

Resources