Calc is unable to resolve dependent formulas

[doak]

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.

[Triggerhousinggroup]

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.