[Solved] Order in which calculations proceed

Discuss the spreadsheet application
Locked
monstercarp
Posts: 1
Joined: Mon Oct 12, 2020 8:36 pm

[Solved] Order in which calculations proceed

Post by monstercarp »

Hi

I've just seen a very interesting post elsewhere in this forum concerning an aspect of Calc that I was completely unaware of. Hopefully one of the experts here can enlarge on this as it is not explained in that post.

In order for the concept to work it appears necessary for the order of the calculations to proceed from the lowest number cell (eg C1) to the highest (eg C12) in one column and from the highest number cell in a subsequent column (eg E12) to the lowest in the next eg(E1). The calculation functions in each cell are quite straightforward.

This is something completely new to me. Searches using Google haven't produced any enlightenment and in fact I'm not even sure what term to use for the search since anything mentioning calculation and order wants to default to the order of precedence for +-/* functions.

I'm hoping somebody can throw some light on this.
Last edited by MrProgrammer on Mon Oct 19, 2020 9:55 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Open Office 4.1.7 on Windows10 pro 64
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Order in which calculations proceed

Post by Villeroy »

See functions LARGE and SMALL.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Order in which calculations proceed

Post by MrProgrammer »

Hi, and welcome to the forum.
monstercarp wrote:I've just seen a very interesting post elsewhere in this forum concerning an aspect of Calc that I was completely unaware of.
Why do you not tell us which one? There are over 100000 posts. We are not mind readers.
monstercarp wrote:In order for the concept to work it appears necessary for the order of the calculations to proceed from the lowest number cell (eg C1) to the highest (eg C12) in one column and from the highest number cell in a subsequent column (eg E12) to the lowest in the next eg(E1).
Conceptually, Calc categorizes cells as follows:
• Tier 0: Cells with data (no formula)
• Tier 1: Cells with formulas which reference only tier 0 cells
• Tier 2: Cells with formulas which reference only tier 0 or tier 1 cells
• Tier 3: Cells with formulas which reference only tier 0, 1, or 2 cells
• Tier 4: Cells with formulas which reference only tier 0, 1, 2, or 3 cells
• etc.

Tier 0 cells need no calculation. All tier 1 cells are then evaluated. Calc can do that because they reference only data cells. All tier 2 cells are then calculated. Calc can do that because they use only cells in tiers 0 and 1 which were just evaluated. All tier 3 cells are then calculated. Calc can do that because they use only cells in tiers 0, 1, and 2 which were just evaluated. This process continues until all cells are processed. The method is guaranteed to succeed since cells in a tier only use cells evaluated in lower tiers.

Spreadsheets do not allow circular references. Those are situations where two cells reference each other's value, for example cell X references cell Y which references cell X. With a circular reference, Calc would not know to do X first or Y first. Circular references always violate the tier system. Let's say X is tier 5 and it references Y. Then Y's tier can be no higher than 4. If cell Y now references cell X we have a tier 1, 2, 3, or 4 cell using a tier 5 cell, which is not allowed. If circular references are permitted there is no simple method to determine cell evaluation order.

Thus it is a fundamental principle in traditional spreadsheet evaluation that circular references are not permitted. When you attempt this in Calc you receive message Err:522 and those calculations are not performed. More complicated circular references are where cell X references cell Y which references cell Z which references cell X again. This also produces Err:522. Any cell which references an Err:522 cell also produces Err:522. Calc provides Tools → Detective → Trace Error to help diagnose these problems.

Here is an example:
•         A      B
1   Content   Tier
2         3      0
3   =A2*5        1
4         4      0
5   =A7-A4       2
6         1      0
7   =A2+A6       1
A2, A4, and A6 are tier 0 and do not need calculation. Cells A3 and A7 are evaluated next. It doesn't matter which one Calc decides to do first. Finally cell A5 in tier 2 is evaluated.

The calculation order depends only on which formulas are placed in which cells. If we have a column with increasing tiers, cells are evaluated from top to bottom. If we have a column with decreasing tiers, cells are evaluated from bottom to top. If the tier order is mixed, as in the example, evaluation jumps around in the column. It may jump back and forth between columns as well so that lower tiers are evaluated first.

[Solved] What order are cells with formulas calculated?

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Order in which calculations proceed

Post by John_Ha »

MrProgrammer wrote:Spreadsheets do not allow circular references. Those are situations where two cells reference each other's value, for example cell X references cell Y which references cell X.
May I quibble?

While I totally agree with your abhorrence of circular references and I strongly advise people never to use them, some spreadsheets seem to accept them. For example, Microsoft has this to say about Excel Remove or allow a circular reference.

See [Solved] LibreOffice Calc calculation errors. Needless to say, Calc was working properly but the poster didn't realise they had a circular reference and didn't understand what was happening. Your comment there applies here too:
I just noticed that the Libreoffice Calculation Error.ods has enabled option Calc → Calculate → Iterations. I know of no documentation that provides the details about how formulas are calculated which use that option, thus, far as I am concerned, all those calculations are undefined, whether using Precision as Shown or not. There are numerous topics on the forum about strange results if Iterations is enabled. I cannot help anyone who is using that option for their spreadsheet.
It piqued my interest enough to do some experimenting and I wrote this circular expression for calculating a square root by the Newton-Rapheson method. I had to be careful where the cell with the formula was located relative to the cell with the answer or it went wrong. But, as shown, it seems to work within the limits I set for the iterations.

I could not find any documentation for circular references so strongly deprecate their use. As you say, if there is no documentation how does a user know what it does?
 Edit: Edited to correct image and spreadsheet.

I originally had the result calculated in C16 but I moved it to D16 and forgot to update the text with D16 instead of C16. 
Clipboard01.gif
Attachments
circular reference.ods
This circular reference SEEMS to work
(17.72 KiB) Downloaded 85 times
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
Locked