Frequently asked questions - Calc

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

• Wiki Home
• Development
• Design
• QA
• Events
• Documentation
• Website
• Localization
• Accessibility
• Marketing
• Diversity
• Wiki Help

• Documentation Home
• Official Publications
• Other Resources
• FAQ
• HowTo
• Macros
• Docs Development

• FAQ Home
• General
• Writer
• Calc
• Impress
• Draw
• Math
• Base

How to carry out a cumulative addition using iteration

Context: you want to add up successive values of a calculation result. How do you proceed, knowing that in principle when you modify the "source" values for a calculation, the latter is repeated and does not "remember" the previous value?

In the following example, the result of the calculation will be found in C7:

=C10-C13+C16

Initial context

You would like to capture new values in C10, C13 or C16 and for the result of the operation to be added to the previous result.

In return for certain constraints, this can be achieved by using iteration.

Result of the accumulation

Explanations

• Let's start with... the possibility of reinitializing the accumulation. We achieve this using the value contained in a "test" cell. For example, if C5 contains zero, the accumulation will be reinitialized.

• Next we must control an iteration step using the menu Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate. Defining it to 1 leads to 2 calculations. But we don't want to add C7 twice to the accumulation. The solution is simple: you divide the cumulative result by 2 (see above in E7).

• The Minimum Change value indirectly defines the maximum value taken into account. For example, with a value of 10000, anything in C7 which is greater than 20000 will cause the error 523 (The calculation does not converge).

• Let's continue with the cumulative calculation itself. The result which is to be accumulated has already been carried out in C7 but you can't use this cell as a source for the accumulation because it is necessary for the accumulation to be updated when any of the "source" cells for the calculation changes (these are the cells shown in orange in the screenshot). The calculation is therefore repeated in E7 together with a test for reinitialization and the necessary division by 2:

=IF(C5=0;0;E7+((C10-C13+C16)/2))

• During the loading of the sheet, the software recalculates... which is troublesome in the case of cumulative calculations. So you should take the precaution of deactivating this calculation automatically (menu Data ▸ Calculate ▸ AutoCalculate) and then carry out the recalculation "manually" by using Ctrl + ⇧ Shift + F9.

In the example, the sheet is protected (without a password) so that only the areas where data entry is expected are accessible.

• Download the sample workbook