Subtracting whole numbers producing a decimal

[danhodges99]

Hi all,

I hope you can help, this is driving me crazy. I'm sure the answer is out there somewhere but I none of search terms I've used seem to lead me to it. Should be a simple one for this community...

I've got a spreadsheet which essentially calculates profit and loss on certain items, but seems to behave slightly differently on some rows than others. Simple example:

Row 3.... Bought for 4000000, Sold for 4100000. Simple formula then subtracts the Bought cost from the Sold cost and should give me 100000. However, it's giving me 99999.9999999995.

Another example
Row 26... Bought for 7900000, Sold for 8200000. Shoud give me 300000. Same problem, it's actually giving me 299999.9999999999 this time.

The Bought for and Sold for columns are themselves upscaled by multiplying a manually-entered figure by 10000000. For example, for Row 3 above, the 4000000 figure is generated by 4.0*1000000.
This seems to be the cause of the problem - if I manually enter 4000000 and 4100000 instead of letting the numbers be upscaled it all works perfectly.

Having said that, when I use the upscaled numbers as the source, they are all 'clean' and correct when I when I look in the 'evaluate formula' option, so I don't understand why it's giving me the wrong result.
Some might say that I should just manually enter the numbers in the upscaled format to workaround this issue, but the point is that I shouldn't have to - it's inconvenient when it's a simple sum that should just work!

I hope someone has come across this before and can tell me what I'm doing wrong - many thanks!

 

[Joe4]

You are not doing anything wrong, it has to do with how Excel stores numbers.
See here: Floating-point arithmetic may give inaccurate result in Excel - Office

If you look at the "Correcting Precision Errors" section, "Method 1: The ROUND function" is usually the solution I use in this situation.

 

[RoryA]

Where does the 4.0 come from? Is it keyed in directly or calculated from something?

 

[danhodges99]

Thanks - got this sorted now, very grateful!

 

[Joe4]

You are welcome.
Glad we were able to help.