False zero value in cells

[jmw]

I thought I'd got this spreadsheet (Excel 2002) done and then I get a change request.
I make a correction and get a circular reference.
I'm tracking it down and think I've fixed it when I find something totally screwy in a couple of cells.

AQ15 = AM15 + AO15
AM15 = 11.6834, the result of a formula
AO15 = 85.3193, also the result of a formula.
AQ15 therefore ought to show 97.0027
But AQ15 actually shows 0.0000

Further down I have:
AO18 = 1 - AM18 where AM18 = 0.1204 but instead of 0.8796, it shows 0.0000

I then set up some empty cells AT10 and AU10. I manually entered the values from AM15 and AO15 in the two cells and in a third AV10, the formula for the sum of these two cells. I get the right answer displayed.

I did the same for AO18, manually put the value in one cell AU8 and the calculation in the next AV8.

Satisfied with that I then linked the new value cells to the originally cells replacing the values manually entered with the cell values.

In AU8 I now entered "=AM18".
Immediately, AU8 showed zero but AV8 still shows the right answer.
Likewise, when I link AT10 to AM15 and AU10 to AO15 both AT10 and AU10 then show 0.0000 while AV10 still shows the right answer.

This is real screwy because the cells showing 0.0000 seem to have the right numbers in there because they are successfully used in subsequent calculations.
I have applied no special formatting. There are no external functions relating to any of these cells.

I don't have any error messages up and no circular references errors because I corrected that error (or so I think)

I tried F9 (even though calculation is set to automatic) to no effect.

I'd like to move on but I need to know how and why Bill Gates is messing with my head before I can.

JMW

http://www.ViscoAnalyser.com

 

[jmw]

Saved, closed and re-opened and there is still a circular reference.

JMW

http://www.ViscoAnalyser.com

 

[IRstuff]

Can you post the sheet?

TTFN

FAQ731-376
Chinese prisoner wins Nobel Peace Prize

 

[zappedagain]

Redefine your formatting on those cells to make sure there isn't some custom formatting in there.

What version of Excel are you using?

Z

 

[jmw]

Thanks Guys,
I'm using Excel 2002.

I think the cause is actually something to do with circular references, I have re-written the code around this group of calculations and the problem has gone.

However, I do find it curious, circular reference or not, that this behaviour should result.
But for the moment I think this part will be fine.

I am now moving on to the next problem which is that between the various cell formulae with lots of IF statements, and the If Then & Elseif statements, when I change the calculation types, even with automatic calculation, I can't get the calculations to update unless I tab from one unprotected data entry cell to another, even without changing anything.

I'm going to try manual calculation and put a calculation button on the sheet.
I'd like it to automatically calculate but the risk of users not realising the calculations aren't updating is not one I want to take.

What really surprises me is that accomodating changes I needed the calculations from another spreadsheet added in.
This is a spreadsheet I wrote some time ago and I now have no idea how it works (something I need to revisit).

But it does work, rather than recreate the calculations I simply moved all the sheets over into the new workbook and surprised myself by finding it all worked a treat, even after a bit of pruning.
I've had problems before copying sheets which results in the new workbook forever trying to link across to the old but selecting move sheet, one sheet at a time, it does exactly what I hoped for and settles into the new work book as if written there.

PS Sorry, I can't post the spreadsheet as it will be confidential and someone has to make a web app of it very shortly.



JMW

http://www.ViscoAnalyser.com