Update: Turns out, Marilyn, you are exactly right. Took me a while, but setting all decimal places to 20 revealed after much tedious searching, some values just as you thought. How can this be though? Is Excel really incapable of properly rounding numbers?
My project is a checkbook balancing worksheet for my wife. After entering all transactions, the worksheet adds up deposits and payments, adds up items marked as not cleared subtracts them from the actual online bank balance and if the difference between the adjusted balance and register balance does not equal I wanted a relevant message to appear and an "out of balance" value to be displayed. As you suggested, using a comparison of < .01 works if the amount out of balance is less than that fraction, it does not take into account discrepancies greater than .01. Sounds like I need to nest multiple if then else arguments. Well, I will go ahead and send this since I wrote it, and while I don't expect any feedback, it would be greatly appreciated all the same! .db Not only does it make sense, it may have in fact been at least part of the problem. After going over it time and time again, I finally noticed H5 was displaying 0.00 even though I have the check box unchecked in the option, view tab show zero values. You know? A blank cell where the value is zero? Anyway, I did in fact do some modifications to the formulas, making H5 get its value from F63 and defining a name for h5 of balnobal with a formula of =f63. Issue resolved. As to the root cause... your analysis is as good as anything I can come up with. You know, if H5 was displaying 0.00 when the worksheet is set not to display zero values, then the true value of h5 was more or less, in some decimalistic (I just made that word up) way... Thanks for taking the time to think about it and reply! .db -----Original Message----- From: M and L Dorn [mailto:[email protected]] Sent: Tuesday, July 06, 2010 3:07 PM To: donald E. Bowen, Jr. Subject: Re: Excel formula, arguments and WE Not sure if I know enough about this to make sense of it, but seems like I've seen formula results that should be a nice even integer with some miniscule decimal if you look far enough to the right of the decimal point. For instance, you might have the formula 2 minus 1 is 1 but have it report 1.0000024 or something strange like it. In actual math, it shouldn't be there, but it is. I have to assume it's a bug in the software somewhere. Anyway, if this is actually what is happening, your cell might report the integer or 2 decimal places but be rounding off the miniscule part to the right of what you see. If this is the case, then the formula's result is not actually zero. The only thing that comes to mind here to fix it might be to change the formula that wants to see zero, i.e. H5 in your example. How about looking for anything less than .01. If H5 is less than .01, it is essentially zero when talking about dollars and cents. Anything that is over zero but less than 1 cent would be irrelevant. Hope this makes sense and proves to be of some help! Marilyn At 10:16 AM 7/6/2010, you wrote: >Hi, > > > >Running an XP SP-3 machine Office '03 Pro and Window eyes 7.2. > > > >Having reinforced what I know about Excel formulas and arguments in the >Excel documentations, I find myself unable to resolve the following riddle. > >Why does the following Excel formula work fine if the value in H5 is >entered directly and does not work when the value in h5 is the result of a >formula > > > >=if(h5 = 0,"Account Balanced","Out of Balance") > >For example, if I just enter a 0 in H5 then g5 (where the above formula >is) will display the text "Account balanced". If I enter a number larger >or smaller than 0 in h5, g5 will display the text "Out of Balance". > > > >However, if h5 is the result of a formula, for example h3-h4, then G5 will >always display "Out of Balance", regardless of the value in h5. > > > >Thanks. > >Sincerely, > ><http://mysite.verizon.net/reszmkrl/musicforsight/author.html>Donald E. >Bowen, Jr. > ><http://musicforsight.org/>Music for Sight > > > > >__________ Information from ESET Smart Security, version of virus >signature database 5256 (20100706) __________ > >The message was checked by ESET Smart Security. > ><http://www.eset.com>http://www.eset.com > > >If you reply to this message it will be delivered to the original sender >only. If your reply would benefit others on the list and your message is >related to GW Micro, then please consider sending your message to >[email protected] so the entire list will receive it. > >GW-Info messages are archived at http://www.gwmicro.com/gwinfo. You can >manage your list subscription at http://www.gwmicro.com/listserv. __________ Information from ESET Smart Security, version of virus signature database 5256 (20100706) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 5256 (20100706) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 5256 (20100706) __________ The message was checked by ESET Smart Security. http://www.eset.com If you reply to this message it will be delivered to the original sender only. If your reply would benefit others on the list and your message is related to GW Micro, then please consider sending your message to [email protected] so the entire list will receive it. GW-Info messages are archived at http://www.gwmicro.com/gwinfo. You can manage your list subscription at http://www.gwmicro.com/listserv.
