Calc is unable to resolve dependent formulas

asked 2019-03-02 16:30:31 +0100

doak
13 ●1 ●2 ●6

updated 2019-03-06 20:51:00 +0100

If have a spreadsheet using several quite complicated formulas "in a row", which also depends on each other. Recalculation triggered by changing the content of some (specific) cell results in a #VALUE! error in some cells.

Manually recalculation (using ctrl+shift+f9) fixes this.

As far as I understand, there is no circular dependency, but I can be wrong.

Am I doing something wrong? If yes, is there a way to fix the calculations?
If no, is this a bug?

I stripped-down the spreadsheet to some "minimal" example attached here. The original spreadsheet does make more sense, of course, but the core is still there: The idea is to calculate start and end of some work package based on dependencies on other work packages. A work package itself can be structured, in which case the start is the earlist start of all sub packages, whereas the end is the latest end of all sub packages.

Data can be entered in the cells with the light blue background: some identifier (name), some dependency, and some duration.
Subpackages are denoted by a dot, i.e. test1.a means sub package a of package test1.

The obvious problem seems to be, that the end date located in row G is calculated using end dates of all sub packages (a MAXIFS(G:G) formular). For example in the attached spreadsheet end date of test3.b (cell G18) is calculated using data from cells G3:G5. Imho there should be no (or at least no unsolvable) circular reference, since the conditions in the used MAXIFS() prevent that the result of G18 is used at all. Nevetheless this seems to be the issue (i.e. causes the #VALUE! error). If I decouple the MAXIFS() ranges, i.e. handle row 3 to 10 by its own as well as row 11 to 20, it works like expected.

How to reproduce the issue in the attached example spreadsheet:

Delete content of cell D8 (marked yellow) with key Delete.
Renter the very same value test1 again.

Environment:

Version: 6.1.5.2
Build ID: 1:6.1.5-1
CPU threads: 4; OS: Linux 4.19; UI render: default; VCL: x11;
Locale: de-DE (en_US.utf8); Calc: group threaded

In the original spreadsheet I sometimes need to trigger the recalculation more than once to get every cell calculated as expected. Sometimes LibreCalc even crashes if I trigger recalculation too often. The last seems to be a bug for sure. Nevertheless I want to understand what I am doing before reporting this as a bug.

edit retag flag offensive close merge delete

Comments

Is there a canonical way to provide an example spreadsheet?

doak ( 2019-03-02 16:31:14 +0100 )edit

Yes: using steps from "How to use the Ask site" referred from the site's main page.

And of course, mentioning version info (LO/OS) is expected :-) - see "Key detail" section there.

Mike Kaganski ( 2019-03-02 17:20:26 +0100 )edit

For which for a new user the question needs to be up'ed to be able to attach a file. Done.

erAck ( 2019-03-05 00:12:22 +0100 )edit

@erAck: lately, that's not needed anymore; you can see in the history that OP has attached the document as soon as I posted the comment.

Mike Kaganski ( 2019-03-05 04:59:52 +0100 )edit

That link doesn't download a document for me (despite ending in upfiles/1551546435315945.ods) but just reloads this question page, hence I thought something's wrong..

erAck ( 2019-03-06 14:18:55 +0100 )edit

@rtAck, thanks. I didn't noticed that (and was unable to decompile your comment ;). Don't know what was going wrong. I have just uploaded it again, hope it works now.

doak ( 2019-03-06 14:34:31 +0100 )edit

File is present now.

Iterations are turned on for the document and there are circular references, as can be easily seen if iterations are turned off then there are Err:522 (circular reference) error results. It is unclear to me what the actual goal of the calculations is and why it involves circular references and iterations. However, iterations with string results are odd as there is no convergence (i.e. delta nearing the result changes), it might work by chance if recalculated in enough circles, hence after an additional Shift+Ctrl+F9. But as I didn't dive further into the document's formulas and dependencies I can't say more about it.

Best you explain what you actually did there and why.

erAck ( 2019-03-06 19:50:39 +0100 )edit

@erAck, thanks a lot, I appreciate your comments. I have updated the question as well as the example. Now it hopefully contains enough information to understand the use case. Regarding circular references: From my point of view, LibreCalc should be able to solve these. But again, perhaps my understanding is wrong. Furtheremore I am working with dates (hence no strings) afaik. I am only using the null string (="") to denote "no date".

doak ( 2019-03-06 20:47:41 +0100 )edit

@erAck, I am now facing the issue that I get Err:523 errors. My guess with reference to provided answer is, that the used formulars still use non-number values (in some cell). Do you know if this could be the case, i.e. if that could also lead to an Err:523?

doak ( 2019-03-11 11:45:31 +0100 )edit

It seems that I found the cause why the calculations have stopped too early: I used "<>0" in one of the MAXIFS() formulas which obviously was an intermediate value which itself prevents the updated value to be considered in the next iteration. Please let me know if this is likely a bug. In this case I will prepare a bug report.
Nevertheless, this behaviour is out-of-scope of this question.

doak ( 2019-03-11 12:27:27 +0100 )edit

add a comment

2 Answers

Sort by » oldest newest most voted

answered 2019-03-08 09:50:32 +0100

newbie-02
594 ●31 ●49 ●57

hello doak, pls. check:

you might be trapped by either:

'late evaluation of format changes', cell D8 in your sample contains 'text', but is formatted as 'number',

(if the format of a cell is changed after input of the value (number, string or formula ...) this change - of the format - is intentionally not! evaluated by the program unless the 'content' of the cell is changed too. at least it has to be 'touched', in plenty cases a 'non-changing-edit' is sufficient to trigger recalculation.)

(similar problems might exist in other cells of the sheet affecting your chained calculations)

(annoyingly it's not visible to the user when and which cells are affected, i doubt plenty users being able to remember in which sequence they keyed and formatted the input of cells)

or:

cells in your document might be 'excluded from autocalculate',

(something like this shouldn't normally happen (sorry for my english - deepl -) but there are way's - bugs? - to produce it, one source is to edit copies of formulas (groups of 'shared formulas' which are created when you place copies of formulas near to the source), another is to change something in ranges shared formulas point to, may bee there are many more ways to produce)

(annoyingly it's not visible to the user when and which cells are affected)

imho a reliable spreadsheet shouldn't need 'hard recalcs' while autocalculate is on, but that's another question ...

i couldn't test your sheet with a version which - as i hope - is free from the second possibility, ver. 4.1.6.2 shows the sheet correctly on load, but switches to #NAME! in plenty cells after the first edit, i think there are formulas / functions in your sheet not known to this version ...

reg.

edit flag offensive delete link

Comments

I am just evaluating the first idea and it seems to be the issue. I fixed it in the example spreadsheet by just removing all "" fallback values I used to distinguish between no value and zero value. It does not work in the orignal spreadsheet yet, I am on it to transform the logic and see if it works.

doak ( 2019-03-08 19:05:39 +0100 )edit

ver. 4.1.6.2 shows the sheet correctly on load, but switches to #NAME! in plenty cells after the first edit [...]

Perhaps you were caught by a bug in context of named ranges. There are plenty: https://bugs.documentfoundation.org/s...

doak ( 2019-03-08 19:29:42 +0100 )edit

I am still trying to fix my spreadsheet. Nevertheless I voted your answer up because it provided very important input to understand the issue. The original error is gone, but experience now Err:523 ("The calculation procedure does not converge") and the fact, that the calculated values are sometimes wrong.

doak ( 2019-03-11 11:42:09 +0100 )edit

It seems I managed to fix my spreadsheet. I will eventually accept your answer. Do you mind to reformat your answer to be more clear (e.g. structuring with headers, codeblocks for cell names, etc.)? It was okay for me, since I knew the context, but it would be very great for other readers (in the future). I would do it by my own, but I don't have enough karma (200 is needed for this priviledge).

Please let me know, too, if I shall rephrase/improve my answer as well.

doak ( 2019-03-11 12:34:41 +0100 )edit

hello @doak,

i'd given the answer as good as i can, it's intentionally not an analysis of your work and problems, as that looks very complex to me. i just had the idea that the two points mentioned might affect your calculations.

if you have time to report about the results i think you are allowed to answer your own question, feel free to use all or parts of my text, feel free to change as well ...

@all - the fact that i described the behaviour doesn't mean that i like it, in my opinion neither 'late evaluation of format changes' nor 'excluding cells from autocalculation' are good ideas for a spreadsheet that should help users to get their work done instead of irritating them ...

reg.

newbie-02 ( 2019-03-11 16:30:17 +0100 )edit

@newbie-02, my itention is really only about formatting to be more skimming-friendly for new readers which stumble about the very same question because of LibreOffice's "short-comings" (like you pointed out).

The content of your answer is valid and should really be preserved, even if the "excluded from autocalculate" was not the issue: it might be for others.

I won't create an extra answer just for the formatting. Imho this is bad practice. Perhaps somebody with enough karma will improve the style.

Nevertheless, thank you very much for your great answer. It really helped me out! See you ;)

doak ( 2019-03-11 17:59:56 +0100 )edit

[...] one source is to edit copies of formulas (groups of 'shared formulas' which are created when you place copies of formulas near to the source) [...]

@newbie-02, btw: I found no reference to "shared formulas" on the web. Can you provide a link or some more detail on this? In other words: how do I detect if I face issues with shared formulas not calculated correctly?
Thanks!

doak ( 2019-03-12 00:18:13 +0100 )edit

@doak: 'shared formulas' are as new to me as to you, look around on this site: https://bugs.documentfoundation.org/s..., and: 'Those shared formula groups were introduced in some earlier (4.2 IIRC) version, so of course before that these things worked. Yes their dependency handling is broken and was from the beginning it seems.' mentioned there.

http://www.bing.com/search?q=calc+%22...,

http://kohei.us/2013/08/15/shared-for...

afaik there is no indicator in user view / user level showing that this feature is 'on' or working for him,

from comments i'd read about and from own experiments:

used likely when you place identical or very similar formulas direct above or below each other,

difficult to handle when something changes in 'the field around',

reg.

newbie-02 ( 2019-03-13 14:14:11 +0100 )edit

add a comment

answered 2019-03-13 10:43:17 +0100

doak
13 ●1 ●2 ●6

updated 2019-03-13 10:45:28 +0100

Solution

For reference, the fixed example spreadsheet is attached here. Thanks @newbie-02 to point to the solution space very exactly.

In fact my primary issue (the #VALUE! errors) had been caused because I used "" to denote empty cells which else contain a date (i.e. a number). Due to late/unreliable type changes of cells in general (like pointed out by @newbie-02), the type is not propagated like expected natively which led to these #VALUE! errors.
Not setting the "fallback" value "" solved this. For example, I just changed =IF($J3;$N3;"") to =IF($J3;$N3).
The page Handling of Empty Cells of the official documentation is also a good reading in this context.

OffTopic

Furthermore I had other issues (which resulted in incomplete computation) in my original spreadsheet which I solved eventually as well. Although OT in respect of this question, I want to mention these for reference purpose:
I used MAXIFS() functions which input and output vector was the same. Furthermore I checked for values <>0 within this function which seem to was an intermediate value during calculation and seems to break iterations. I rephrased my formulas to handle 0 and skipped the check for values <>0.

edit flag offensive delete link

Comments

Thanks. I found a reference to if(this, "", else_that) in my spreadsheet and eliminated it. I was able to remove a wedged that forced me to put something...anything into a cell to prevent #VALUE errors. Now things are a bit cleaner.

Not sure if this is related to the lack-of-autorecalc that happens every now and again, but hopefully it will help. +1 if I could.

Triggerhousinggroup ( 2019-10-09 17:49:45 +0100 )edit

add a comment

Calc is unable to resolve dependent formulas

Comments

2 Answers

Comments

Solution

OffTopic

Comments

Question Tools

Stats

Related questions

Calc is unable to resolve dependent formulas edit

Comments

2 Answers

Comments

Solution

OffTopic

Comments

Question Tools

Stats

Related questions

Calc is unable to resolve dependent formulas