CALC: Function Wizard shows correct result; cell shows incorrect value or error message

English
#1

Here is a screenshot from a spreadsheet that I have had for a long time and have never had problems with before. This morning I suddenly got a bunch of weird values in a a lot of formula cells:

image
image1899Ă—991 251 KB

You can see in the screenshot that the Function Wizard is telling me that the value for I302 is $3.48, which is exactly what I want. It is the sum of the values from J302 to U302. But what is displayed in cell I302 is $0.00 instead.

Some of the cells are displaying incorrect values and other are displaying error messages. In every case, when I go to the Function Wizard it shows everything calculating correctly.

In some places I have retyped the formulas and then the cell starts showing the correct results, in other cases I have done this and it didn’t help.

What is going on?

#2

First step: Menu / Data / Calculate. Check AutoCalculate and execute the menu item Recalculate Hard.

#3

Hi!

I’ve done that already, and nothing changed. The Autocalculate was already checked, and I did both of the Recalculates, and the problem remained.

#4

Second step. Upload the file or fragment containing the error and indicate which cell contains the error.

#5

OK. I’ll have to upload a few sheets because they reference each other.

#6

Here is a subset of the spreadsheet. The sheets that are having problems right now are “HFA” and “Compulsory sales”. I was also having problems with “Main”, “CCMG”, and “3rdParty” earlier, but for some reason those started acting right when I retyped the formulas and pasted them.

Sample of accounting spreadsheet.ods (772.8 KB)

#7

After opening the file, I have the displayed value of cell I302 on sheet Compulsory sales equal to $3.48.

#8

OMG! I tried closing the file and reopening it once, and I was still getting errors. Let me try it again.

#9

OK, I reopened the sample file that I had uploaded here, and everything looked right. Then I reopened my real spreadsheet and a lot of the problems were gone, but column I in “Compulsory sales” was showing all zeros. But then I tried one more go-round of retyping the formula and pasting it, and everything seems to be back to normal.

Any ideas of what might have made it go loopy today? If there is any way that I can avoid having this happen again . . .

#10

Alas, I can only make a diagnosis if I can reproduce the effect on my computer.

#11

Sigh. I don’t like being special when it comes to technology problems. I like it when people say, “Yeah, that same thing happened to me three years ago, and it turned out that is was XYZ.”

Thank you for your assistance, and for now I will go on blindly hoping that the problem will not recur.

#12

Most importantly - if the error occurs again, then try to save it and immediately upload it to the forum. :slightly_smiling_face:

1 Like
#13

:+1:t5:

Will do! Thanks.

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