UDF for Cumulative Sum

Back in my newsgroup days, about once a month someone would ask how to have a formula that accumulated entries from another cell. For example, put a formula in C3 that refers to A3. Every time a value is entered in A3, have the value in C3 change by that amount. Impossible right?

The standard answer is that you can’t and that you should use a Worksheet_Change event to monitor A3. When it changes, change the value in C3. Then I read Charles Williams’ comments about the Text property

When used inside a UDF you can use .Text to get the formatted value of the calling cell as it was before the UDF was called (you can’t do this with .Value or .Value 2 without causing a circular reference).

I had no idea that was true. Now I can write a UDF like this:

Function UpDown(dChange As Double) As Double

Dim sOld As String

Application.Volatile True

sOld = Application.Caller.Text

UpDown = Val(sOld) + dChange

End Function

In C3, I put =updown(A3). Whenever I change A3, C3 changes by that amount. I’m sure there are all kinds of problems with this – dates and errors to name two – but I thought it was interesting nonetheless. Apologies if the entire Excel universe knows this already, but it’s new to me.

15 thoughts on “UDF for Cumulative Sum

  1. That’s interesting. I didn’t know that either. Curious about the volatility in your function though.

    This approach, if I understand it correctly, will have a problem when the workbook is closed and reopened, because C3 will be recalculated then, even though A3 hasn’t changed. Changing the volatility to False seems to fix that, but it will still cause a wrong result if forcing a full recalc of the workbook.

  2. Very interesting…I got all sorts of weird results initially – C3 changes by the amount in A3 even when a cell other than A3 is being changed. I changed to this:
    Function UpDown(Change As Range) As Double

    Dim dOld As Double
    dOld = Val(Application.Caller.Text)

    On Error Resume Next
    If IsError(CDbl(Change)) Then
    UpDown = dOld
    Else
    UpDown = dOld + Change
    End If
    End Function
    It still doesn't recognize a change if you input the same number twice in a row, otherwise seems to work.

  3. Hello,

    Can you make a circular reference to =A3+C3 after you change in Excels option to allow for iterative calculations and only allow 1. Then when you change A3 it adds to C3. Is there a down side to doing it in this fashion?

    Thanks,

    Bryan B

  4. There is one problem for sure…. lets say you start with 100, then put 2, 3, and 5. This will update in C3…

    Post this, if you keep on putting 5 and press enter…nothing changes…basically the function is not called again unless the new value is different from the previous one…

    So, 100, 1,2,3 will work…

    100, 1, 2,2,2, will not work.

    Not sure why..

    ~VijaySharma

  5. Good points about volatility. I originally had it non-volatile, but as Vijay points out typing in the same number doesn’t force a recalc. So I made it volatile. I guess the right answer is not to do this. :)

  6. This will work even when you don’t change the actual value.

    So you can enter:

    100, 1,2,3
    100, 1, 2,2,2

    and both work.


    Public Function UpDown(Change As Range) As Double

    Application.Volatile True

    Dim dOld As Double
    Dim rSelection As Range

    dOld = Val(Application.Caller.Text)
    Set rSelection = Selection

    On Error Resume Next
    If IsError(CDbl(Change)) Then
    UpDown = dOld
    ElseIf rSelection.Address = Change.Address Then
    UpDown = dOld + Change
    Else
    UpDown = dOld
    End If

    End Function

  7. Let me try this one more time:

    This will work even when you don’t change the actual value.

    So you can enter:

    100, 1,2,3
    100, 1, 2,2,2

    and both work.


    Public Function UpDown(Change As Range) As Double

    Application.Volatile True

    Dim dOld As Double
    Dim rSelection As Range

    dOld = Val(Application.Caller.Text)
    Set rSelection = Selection

    On Error Resume Next
    If IsError(CDbl(Change)) Then
    UpDown = dOld
    ElseIf rSelection.Address = Change.Address Then
    UpDown = dOld + Change
    Else
    UpDown = dOld
    End If

    End Function

  8. One more addition to what I wrote in my code. Instead of using .Address, it would probably be smarter to us .Address(,,,true), that way you don’t change some cell on a different sheet and it ends up updated you code when you shouldn’t.

  9. OK, here’s a round about way to store your previous value so when you open your workbook it doesn’t recalculate to 0. Don’t know if there is a good solution to that one, it would be interesting to see one.


    Public Function UpDown(Change As Range, dPreviousValue As Double) As Double

    Application.Volatile True

    Dim bContinue As Boolean
    Dim dOld As Double
    Dim rSelection As Range

    bContinue = Application.Caller.Text <> "#NAME?"

    On Error Resume Next
    If bContinue Then
    dOld = Val(Application.Caller.Text)
    Set rSelection = Selection
    If IsError(CDbl(Change)) Then
    UpDown = dOld
    ElseIf rSelection.Address(, , , True) = Change.Address(, , , True) Then
    UpDown = dOld + Change
    Else
    UpDown = dOld
    End If
    Else
    UpDown = dPreviousValue
    End If

    End Function

  10. Keeping the values straight when you open a file is a real headache. This is where event handlers do a better job. Still, it’s a PITA to put all the logic into VBA if you need several different accumulators.

    One way to handle this is to use an additional worksheet set up as a 2-column table. The first column has worksheet!cell addresses for cells to be accumulated, and the second column has the current accumulated value. You can make the addresses in the first column sensitive to move (CUT and paste) operations by using =CELL(“Address”,cell_ref). Use SheetCalculate to update values in the second column of the table where the changed cell’s (Target) address appears in the first column. Use a UDF like the following to fetch accumulated values from the table.

    Function accum(r As Range) As Variant
    Dim k As Long

    With ThisWorkbook.Names("TblAccumulators").RefersToRange
    On Error Resume Next
    k = Application.Match(r.Address(1, 1, xlA1, 1), .Columns(1), 0)
    On Error GoTo 0
    If k > 0 Then
    accum = .Cells(k, 2)
    Else
    Err.Clear
    accum = CVErr(xlErrNA)
    End If
    End With
    End Function

  11. Keeping the values straight when you open a file is a real headache. This is where event handlers do a better job. Still, it’s a PITA to put all the logic into VBA if you need several different accumulators.

    One way to handle this is to use an additional worksheet set up as a 2-column table. The first column has worksheet!cell addresses for cells to be accumulated, and the second column has the current accumulated value. You can make the addresses in the first column sensitive to move (CUT and paste) operations by using =CELL(“Address”,cell_ref). Use SheetCalculate to update values in the second column of the table where the changed cell’s (Target) address appears in the first column. Use a UDF like the following to fetch accumulated values from the table.

    Function accum(r As Range) As Variant
    Dim k As Long

    With ThisWorkbook.Names("TblAccumulators").RefersToRange
    On Error Resume Next
    k = Application.Match(r.Address(1, 1, xlA1, 1), .Columns(1), 0)
    On Error GoTo 0
    If k > 0 Then
    accum = .Cells(k, 2)
    Else
    Err.Clear
    accum = CVErr(xlErrNA)
    End If
    End With
    End Function

  12. Sorry. Forgot the code tag syntax. The udf takes a range ref argument to the cell being accumulated, uses its address to look up into the table, and returns the accumulated value if the address is found or #N/A otherwise.

  13. Interesting, I didn’t know about the .Text property.

    Now for extra credit, how would you do the same thing without any code? Impossible right?

    A trick to bypass circular references is to use data tables with the following setup:

    A3 Value    B3 =A3+C3    C3 {=TABLE(B2,)}

    Where C3 is populated by selecting B2:C3 and choosing Data Table… with row input cell: B2.

    Changing values in A3 gave the same results as the Updown() udf in tests, you can reset values by clearing B3 and undoing. The same technique can be utilised to create a list of all previous cell values down column C. To do this enter down column B from B3: =A3 =C3 =C4 =C5 =C6 … and extend the data table down.

Leave a Reply

Your email address will not be published. Required fields are marked *