Basic Arithmetic doesn't work?!

[Cowlers]

Hi All,

I have a strange situation in Excel and I cannot see where I'm going wrong.

I have attached an image showing the way my formulae are set up. You can see this is a simple arithmetic function and I'd expect the following to be the case:

Excel Problem.JPG

Cell F13 would calculate the sum of G13:I13, i.e. 5.21 + 363.02 + 0 = 368.23

Cell D13 would find the sum of E13:F13 i.e. 0 + 368.23 and delete this value from the value of C13, i.e. 368.23 - 368.23 = 0

I would therefore expect cells D13 to equal "" as opposed the "****" value that has resulted from this calculation.

If I evaluate the formula I see that apparently 368.23 - 368.23 does not equal 0 as I was taught but in fact it equals a tiny exponential amount which seems to be where my problem lies!

Please could someone help me to overcome this?!

Thanks for your time in advance,

Dan

[Paul_Hossler]

http://blogs.office.com/b/microsoft-...g-answers.aspx


Just the way Excel stores floating point decimal numbers in binary

Maybe =IF(ABS(C13-(E13+F13))<.0001, "", "****") would work for you


Paul

[mdmackillop]

Treat with caution as you will lose accuracy, but you can change to Precision as Displayed.
Click on the File tab and go to Options. On the left side of the window that appears, click on Advanced. Scroll down to "When calculating ..." and check the box next to “set precision as displayed.”

[Cowlers]

Ah I see, thank you very much for your help!

It is a little concerning if you don't know about this already but now I have read the excellent article that Paul posted I understand why this happens.

Thanks again to Paul and to mdmackillop for your help in resolving this for me!

Dan

[Paul_Hossler]

glad to help

Don't forget to mark it "Solved" using Thread Tools at the top

Paul