Accumulating values in one Cell

[ilsm]

Is it possible for a Cell to accumulate the values resulting from repetitive calculations?

For example, Cell A1 has successive values entered as data and each time Cell A2 adds that value to its existing value.

A formula such as =A2+A1 obviously won’t work, but it gives the idea of what I want to do.

ilsm

[anon73440385]

Hello,

from my perspective this can be done only by using macros, which are assigned to events.

• one macro storing initial values on opening the document (e.g. EventDocOpen() in example file assigned to event Open Document)
• one macro calculating the new value in the cell designed to hold the “Accumulated Value” (e.g. EventSheetChanged() in example file assigned to sheet event Content changed)

Here is the sample file: AccumulatingValue.ods

Here are the macros:

Global CurValAccum
Global CurValInput

Sub EventSheetChanged()

  Dim NewValInput
  Dim NewValAccum
  
  NewValInput = ThisComponent.Sheets(0).getCellRangeByName("A2").Value
  
  If NewValInput <> CurValInput Then
  
     CurValInput = NewValInput
     CurValAccum = CurValAccum + NewValInput  
     ThisComponent.Sheets(0).getCellRangeByName("B2").Value = CurValAccum
     
  End If
  
End Sub

Sub EventDocOpen()
  CurValInput = ThisComponent.Sheets(0).getCellRangeByName("A2").Value
  CurValAccum = ThisComponent.Sheets(0).getCellRangeByName("B2").Value
End Sub

Notes

• The sample file is an outline only and not a solution (the macros lack any error handling, input type checking or some such)
• The macros work only for cell A2 (Input Value) and B2 (Accumulated Value) - The cell names are hard coded.
• There is no reset function (hence if you want to start over, you need to set 0 into B2, save the file and re-open the file - as said: It is just an outline to present the idea
• The Accumulation only works if input cell changes. In other words: Entering the same value does not add the value once more to the Accumulated value (To achieve this: Enter 0 and enter the previous value again). This decision was made to prevent an accumulation of cell A2 on every recalculation of the document, regardless of the cell(s) being changed (reminder: event Content changed triggers macro execution)

Assignment of Macros

Sheet events: Assignment of macros to sheet events can be done through Right click on sheet tab -> Sheet event..

Document events: Assignment of macros to document events can be done through Tools -> Customize -> Tab: Events

Hope that helps.

[ilsm]

Thank-you. The example file certainly seems to do the job, so I shall try to incorporate your suggestion. I was afraid someone would suggest a macro, so I must now learn how to deal with them!
Onwards and upwards …
ilsm

[anon73440385]

Personally, I’d absolutely try to avoid such solution. But since you did not describe why you need that for which purpose, I’ve provided the idea of a solution. However every input of a new value in A2 makes you lose information (what was there just right before, or 5 days before, last year…), while one purpose of spreadsheets is to store information (quite generally speaking).