×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

False zero value in cells

False zero value in cells

False zero value in cells

(OP)
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
www.ViscoAnalyser.com

 

RE: False zero value in cells

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

JMW
www.ViscoAnalyser.com

 

RE: False zero value in cells

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
 

RE: False zero value in cells

(OP)
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
www.ViscoAnalyser.com

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources