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
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.ameans sub package
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
G18) is calculated using data from cells
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
- Renter the very same value
- Version: 184.108.40.206
- 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.