Unknown user action causes array formula to display syntax (not result)

bulunguladave · October 30, 2020, 12:51pm

I have a complex spreadsheet that makes use of lots of Array formulas.

I have protected cells and sheets as much as possible.

However, inexplicably, on rare occasions, after unknown user actions, a few of the array formula occasionally display the syntax of the array formula and not the result of the formula. This causes an error in the total calculation as the cell is treated as a zero value instead of what the formula would have calculated. I can’t think of what it is that the user has done that might cause this to happen. The Array formula concerned is:

{=IF(ROWS([.B$83:.B83])>[.$M$81];"";INDEX((combinedcashaccom);SMALL(IF(allcashdates=[.$M$82];ROW(allcashdates));ROWS([.B$83:.B83]))))}

Error in cell B83.

The same formula is copied down from B83 to B100.

I’m guessing that the user is cutting and pasting or doing something like that in one of the input fields in the source sheets. But I can’t see any pattern. Sometimes it happens when the value generated would have been zero/blank in any case. Other times there should have been a value generated. Has anyone got any ideas why the formula would display like this?

Here is the Google Drive link for the file:

The password to unprotect sheets is blank.

thanks!

Lupp · October 30, 2020, 6:34pm

With the cell showing the issue selected inspect Format Cells...>Numbers using the context menu (rightclick).
Most likely you find there the format code “@” which means “Text: Neiter number nor formula recognition”. A cell with this setting will take the formula simply as another string.
Change the cell(s) to the wanted numeric format, and edit the formula in an insignificant way (Append a space after everything else. Everything should work then.

bulunguladave · November 2, 2020, 8:16am

Thanks for your answer. This is what I see when I do as instructed:

bulunguladave · November 2, 2020, 8:21am

Here is the link for the file:

The password for protect sheet is blank

Lupp · November 2, 2020, 1:16pm

If a formula wasn’t recognized, and you change the cell format to a numeric one to allow for formula recognition, you need to do a fake editing (like appending a space behind the end), and to enter the formula again (in this case with Ctrl+Shift+Enter) to trigger a recognition process.
Probably you entered that formula originalyy by pasting it with the curly brackets already included. That doesn’t work.
Anyway: What you supposed to be a formula was this monster: {=IF(ROWS([.B$83:.B83])>[.$M$81];"";INDEX((combinedcashaccom);SMALL(IF(allcashdates=[.$M$82];ROW(allcashdates));ROWS([.B$83:.B83]))))}
Where did you get it from? What did you expect the square brackets to mean? What did you expect it to calculate?
If the formulas you have below the row with the “issue” work as you expected, simply copy from such a row (A84:J84 e.g.) and paste it above (A83).
Good luck! I wouldn’t trust in the construct. Why do you? You obviously neither designed nor understood it?

bulunguladave · November 2, 2020, 1:38pm

Every formula was written by me - I promise you - and there are much bigger monster formulas in that sheet

So, your comment was still helpful because whatever it is that the User is doing, it is causing the Formula to change from the correct formula which is:
{=IF(ROWS(B$83:B83)>$M$81,"",INDEX((combinedcashaccom),SMALL(IF(allcashdates=$M$82,ROW(allcashdates)),ROWS(B$83:B83))))}

And then some unknown User action causes the above (protected) formula to change to the problem formula:

{=IF(ROWS([.B$83:.B83])>[.$M$81];"";INDEX((combinedcashaccom);SMALL(IF(allcashdates=[.$M$82];ROW(allcashdates));ROWS([.B$83:.B83]))))}

I don’t know what that action could be. The formulas remain correct in neighbouring columns (e.g. C83, D83, etc)

And on other sheets (e.g. ‘Rest 10’ F85) this problem can happen on other rows and columns. No consistency in terms of which cell this problem manifests in other than that it is always in the cells with these formulas.

erAck · November 2, 2020, 6:32pm

Error in cell B83.

Next time please tell on which sheet of the >66 ones… for the curious it’s on Rest 1.

However, I have no idea how you ended up with that, but the file storage says it is a text cell without formula. i.e.

  <table:table-cell table:style-name="ce357" office:value-type="string" calcext:value-type="string">
    <text:p>{=IF(ROWS([.B$83:.B83])&gt;[.$M$81];&quot;&quot;;INDEX((combinedcashaccom);SMALL(IF(allcashdates=[.$M$82];ROW(allcashdates));ROWS([.B$83:.B83]))))}</text:p>

whereas a correct formula cell, e.g. the next column same row, is stored as

  <table:table-cell table:style-name="ce357" table:number-matrix-columns-spanned="1" table:number-matrix-rows-spanned="1" table:formula="of:=IF(ROWS([.C$83:.C83])&gt;[.$M$81];&quot;&quot;;INDEX((combinedcashfood);SMALL(IF(allcashdates=[.$M$82];ROW(allcashdates));ROWS([.C$83:.C83]))))" office:value-type="float" office:value="342" calcext:value-type="float">

Note the table:formula="of:=... (and table:number-matrix-...-spanned=...) missing from the failing cell.

The bracketed references like [.B$83:.B83] are correct, it is how cell references are stored in ODF OpenFormula, but as said, no idea how you ended up with that text instead of formula, and never seen before. The <meta:generator> element says the file was saved using LibreOffice/6.4.7.2$Windows_X86_64, but is that the version the erroneous cell was created with? Or is it only the last program/version that happened to save the file?

There are 17 such cells in the document distributed over several sheets, you can find them with

Find: ^\{=
All sheets: on
Regular expressions: on
Search in: Values
Find All

bulunguladave · November 3, 2020, 1:21pm

Thanks for your comments. To be clear, all the formulas in all the Restxx sheets are exactly the same and correct. Some user action causes some of them to change. Every month there are different cells in the same table that are affected (i,e, in another month B83 is correct and D91 has this issue).

As to your question: the spreadsheet was created in version 4.3.5 and the User has that same version on their computer. So the error emerged in 4.3.5. I am using 6.4.7 on my machine. I will be upgrading their machine to 6.4.7 soon.

erAck · November 3, 2020, 7:37pm

Some user action causes some of them to change.

I doubt that. There’s no UI to change a formula to its textual representation in the ODFF syntax (with bracketed references), even less likely a user voluntarily changing it, and replacing the cell with that. I’d rather suspect a macro or extension running wild. Or a bug in such (very) old version that occasionally saves an array formula as text content instead of formula content under some unknown circumstances.