Subtracting whole numbers producing a decimal

danhodges99

New Member
Joined
Apr 22, 2009
Messages
12
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!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,059
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Where does the 4.0 come from? Is it keyed in directly or calculated from something?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,782
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad we were able to help.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,699
Messages
5,766,002
Members
425,322
Latest member
galaxy6623top

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top