Cell with formula not always autocalculating when new data present

English
#1

I’m having a problem where cells do not automatically update, or even MANUALLY update using options like Recalculate or Recalculate Hard. BUT they will update if I drag the formula down from the cell above–even though the formula before and after is identical.

This formula looks at a column of quarterly data and basically checks a few things like “Do these cells contain numbers? Is it dealing with a number of days over 365?” and then either returns a number or just leaves things blank.

=IF(ISNUMBER(F39),IF(AND(B39>365,ISNUMBER(F35)),F39-F35,IF(B39<365,F39,{})),{})

But when I add new numeric data to cell F39, it just doesn’t care and remains blank. Until I fill the cell again with the exact same formula by dragging from above. AutoCalculate is on, and as stated earlier Recalculate and Recalculate Hard don’t make a change. Editing the formula and hitting Enter also doesn’t change anything. Once it has actually updated, though, if I change the value of cell F39 again the result does change, so it is apparently willing to autocalculate sometimes.

When I started writing this message I was using version 25.2.something. I decided to try the obvious and have just upgraded to 26.2.0.3, but the behavior is the same.

#2

Can’t confirm the issue.
Please always attach a file (.ods) showing the problem for you in such a case.

#3

staticnswproblemexample.ods (35.3 KB)
Here is the one I’m having issue with today. Adding a new value to the F column should change the D and E right next to it.

#8

I suppose a bug.
Unfortunately it’s one for which I wouldn’t know a proper way of reporting.
The formulas in columns D and E seem to be not correctly parsed as long as the corresponding cell in column F doesn’t contain a number.
I have no idea what use the formulas in column H should have, and can therefore not tell if they are afflicted by a workaround replacing pseudo-blank results (created by empty inline arrays) with empty strings.
Check the attached version of your example.
staticnswproblemexampleVariant.ods (29.8 KB)

.

#16

Thanks folks, it seems like replacing {} with “” does take care of the problem. Usually for something like this I start with more columns for easier debugging before combining everything, so being able to check what parts were numbers or text was probably meaningful at that stage of things when making this sheet a few months back.

Column H I have no problem with.

2 Likes
#4

What do the {} do at the end of those formulas? Just remove them.

F2, Enter is not “editing a formula”; you must do something, like adding a space and deleting it.

#5

They create an output which answers NO to ISTEXT()? and to ISNUMBER()? as well.
This is surely intentional.

#6

And ={} is seen as an empty cell.

#7

It is what comes next to it.
You get the same output for =A1:A1 enterd as array formula if A1 is blank.

#9

You see: as for today, I haven’t fully grasped arrays.

#10

Hello, colleagues!
I agree with @LeroyG - there’s no point in using the {} construct.

#11

Concerning empty online-arrays I only reported my experience.
I just checked Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 4: Recalculated Formula (OpenFormula) Format.

It doesnt mention empty inline arrays, but talks of a constant arry consisting of one or more rows and one or more columns. We may se it as a bug that the formula parser doesn’t throw an error if getting an empty inline array.
[However, there is also: 3.3/1.1) Inline Arrays
Element (0;0) of the array is used in place of the array.] :hot_face:

With [=A1:A1} it’s different. It references one element. If the actual behavior for blank reference is explicitly specified, I didn’t research.

#12

Yes, but if you also wan’t to test for ISNUMBER() and for ISTEXT() you have a problem with the surrogate "" (empty string).
Anyway, I also tested with the variant to use $H$1:$H$1 (blank) as array expression. In the given example. It caused the same issue.

We have to live with “Bugs are constitutive for software including its specifications.”

#13

We know the cell contains a formula, which means the cell isn’t empty.
The <>"" construct will be true for any number.

#14

I didn’t talk about the cell containing the formula, but about a referenced blank cell (referenced as a single-cell range under array evaluation). In the example provided by the OQer $H$1:$H:1 was such a range.

#15

I suggest we proceed sequentially, step by step. :slight_smile:
The topic’s author was asked about the formulas in columns D and E.
Suggestion: replace {} in these formulas with "".
If these formulas begin to behave normally, we can move on to discussing the formulas in column H.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.