Calc is unable to resolve dependent formulas
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 packagea
of packagetest1
.
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 keyDelete
. - 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.
Is there a canonical way to provide an example spreadsheet?
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.
For which for a new user the question needs to be up'ed to be able to attach a file. Done.
@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.
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..
@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.
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, 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".@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 anErr:523
?It seems that I found the cause why the calculations have stopped too early: I used
"<>0"
in one of theMAXIFS()
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.