• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formulas returning -0- value when clearly there is a numerical answer. Help!

K

KristineK

New Member
I'm using Excel 2003. We have a very complex spreadhseet that has been used by our company for years. When I open up one of these spreadsheets, if I click on a cell to check or edit the formula, and then press enter to get the updated value/solution, the solution goes to zero, whn that is clearly not the correct answer to the calculation. It seems to only happen in formulas that reference other cells. (ie. If i simply type in 1000/25, instead of A3/C21, it renders out the correct answer to the formula). I have checked that the cells are formatted to General or Number and this doesn't solve the problem). I have checked error checking settings and this doesn't seem to be it. I will say though that if I click in the formula bar and hit F9, the correct answer suddenly appears. However, now I've lost my formula, so that obviously doesn't work as a long term solution. Has anyone ever seen anything like this?? Any help would be much appreciated!! Thank you.
 
are they originally Array formulas? they would have the curly brackets {} at the very beginning and end of the formula.....if so, instead of simply pressing enter, you must press Ctrl+Shift+Enter all together.
 
Are you in manual calculation mode instead of Automatic? (Tools - Options - Calculation)
 
No, I checked and I'm in Automatic calc mode, and also, the formulas don't have the curly brackets (to indicate them originally being Array formulas, as mentioned above).
 
Is there any other Automatic Calculation related setting besides (Tools - Options - Calculation)? It does seem like it's somehow related to this (even though I double-checked and I am in Automatic Calc mode). I say this because the formulas aren't recalculating when I make changes in the spreadsheet. . . .I didn't realize that this was happening at first. The second issue is that when I click in the formula bar to check the formula, and then press enter, the zero value appears instead of the correct answer (as discussed in my original post). The only way that I can get the correct answer to the formula is hitting F9, which again isn't a long term solution because then I lose the formula and can't make future changes to the spreadsheet. I feel like there's a ghost in my spreadsheet trying to make my life miserable.
 
Hurrah! I don't see that error very often, but it is annoying. Can happen when you get a lot of complex referencing going on (I think it's somewhere over 32,000 intercell dependencies...) and XL starts to get goofy and basically throws it's hands up in defeat. =P
 
You must log in or register to reply here.
Back
Top