Excel format error

[BORUCH]

Hi

I seem to be having this weird problem

in cell A1 i have a number formatted as accounting $13,914.40 in cell B1 i have a number $13,881.75

When i minus cell A1 from cell B1 to get the difference i get this $32.649999999999600

why ?

[AliGW]

To find out, increase the number of decimal points for cells A1 and B1 - eventually you should find some anomaly. It's to do with rounding and what is known as the floating point error.

[BORUCH]

deciaml error.JPG

actually that is not correct as you can see in the picture

[comatose1978]

Select the cell that is formatted wrong and right click on it, choose format cell/ currency/ set decimal places to 2.

[PeteABC123]

You could set precision as displayed.
File>Options>Advanced>When Calculating This Workbook>Check

Pete

[BORUCH]

The formatting will only help how it shows on the screen

the problem is i'm entering 32.65 on an excel macro to do something with that number, and its not recognizing because internally,y its still $32.649999999999600

[AliGW]

Attachment 586219

actually that is not correct as you can see in the picture

You may not have gone far enough. Hard to tell without seeing the workbook for myself.

the problem is i'm entering 32.65 on an excel macro to do something with that number, and its not recognizing because internally,y its still $32.649999999999600

So this is a VBA question? Do you need help with rounding in VBA, then?

[BORUCH]

yes i guess my issue is rounding with vba

but what would i round it to in an excel formula if i want it to show 32.65

[AliGW]

Two decimal places:

=ROUND(A1,2)

I don't know how it's done in VBA - sorry. Should I move the thread for you?

[FDibbins]

To read more about the floating point issue...
https://www.microsoft.com/en-us/micr...wrong-answers/

Note that "showing" only 2 decimals in excel and "having" only 2 decimals is not necessarily the same thing.

Ali's suggestion will remove (completely) all points after the 2nd decimal, but FORMATTING will still show 2 decimals, but the cell will continue to contain more decimals (if there are more)