Excel strange (erroneous?) decimal behaviour

[Guest]

Cell A1 = 4.469 with 3 decimals numeric format

if cell A2 formula is "=A1" ==> A2 = 4.469

if in VBA: Range("A2").Value=Range("A1").Value ==> A2 = 4.470
if in VBA: Range("A2").Value=Range("A1").Value * 1000 / 1000 ==> A2 = 4.469

Can anyone explain this please and how to avoid it?? Could it be that there
is a SET DECIMALS TO 2 VBA environment setting that causes a 2-decimal
rounding??

 

[Norman Jones]

Hi DoctorG,

See Chip Pearson's Rounding errors page at:

http://www.cpearson.com/excel/rounding.htm

 

[Tom Ogilvy]

? Range("A1").Value
4.469
? Range("A2").Value
4.469
? range("A1").Value*1000/1000
4.469
? range("A2").Value*1000/1000
4.469

I couldn't reproduce it.

 

[Tom Ogilvy]

In tools=>Options=>Calculation tab, make sure precision as displayed is
unchecked.

 

[Norman Jones]

Hi Doctor G,

Please ignore my suggestion.

I could only reproduce your experience if, as suggested by Tom, I had the '
Precision as displayed ' option selected and I had cell A2 formatted as a 2
decimal place number.

My apologies for my first response.

 

[Guest]

Tom, I checked Precision as Displayed and it is not checked.

I was also not able to reproduce it as you tried and I found out that it is
a result of the Currency format, through the Currency button. This button
results in an "Accounting" format with 2 decimals. I change this to 3
decimals through Format Cell, but through VBA-->
Range("A2").Value=Range("A1").Value Excel rounds the result.

Does this give you a clue? Can you reproduce it like this?

 

[Guest]

My dear Norman, you mean that deep inside you are human and you happened to
suggest something that wasn't exactly to the point?? Please, just think where
all us out here would be left if you guys (and gals) weren't helping us out.
Thanks for being there. You are all great!

By the way, I am situated in Greece so my Currency button (read my response
to Tom) displays a Euro symbol. Does this have anything to do with the
problem?

 

[Dana DeLouis]

results in an "Accounting" format ...etc

Does this give you a clue?

Yes. You need to use "Value2" instead of "Value."

Range("A2").Value = Range("A1").Value2

HTH. :>)

 

[Guest]

Dana you are absolutely correct.

I was not aware of "value2" and it seems that it provides the extra
(decimal) precision I needed, as stated in Help.

Thanks a lot.