Why does Excel add "extra" numbers in the 10-16 decimal place holders?

promalley2

New Member
Joined
Nov 16, 2009
Messages
24
Greetings all!

This is probably an easy question for the experienced, but could someone tell me why:
When I dimension a numeric decimal value as a single or double in VBA code (such as 5.05, etc.), why does excel turn it into 5.0499999999999998?

I've tried using the round function as cleverly as I can, including changing the number from 5.05 to 5.051 and then rounding to 2 decimal places, but it doesn't make a difference.

I'm thinking that the issue is with excel because I'm using the value(s) in the spreadsheet to populate fields on a Word mail merge main document and even when the Excel cell displays the correct value in the cell (5.05), the value in the Word merge field ends up 5.0499999999999998. Not to mention, sometimes even though the correct value is in the cell, when it is selected as the active cell the value that appears in the formula bar is not 5.05 but 5.049999999999999998.

Anyone have any ideas as to why this is happening? I have other hard coded values within the code and they work just fine, but they're positive, whole, real numbers.

Thanks in advance for your time!

--Patrick

 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Excel has trouble handling decimals, because numbers in Excel are generally in Base 2, so what you get is the nearest representation.
To get accurate decimals you can use the Decimal data type in VBA (declare it as a Variant, then use the CDec function) or you can use Currency.

Denis
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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
Back
Top