Excel strange (erroneous?) decimal behaviour
 

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??

Excel strange (erroneous?) decimal behaviour
 

Hi DoctorG,

See Chip Pearson's Rounding errors page at:

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


---
Regards,
Norman



"DoctorG" wrote in message
...
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??

Excel strange (erroneous?) decimal behaviour
 

? 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.

--
Regards,
Tom Ogilvy

"DoctorG" wrote in message
...
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??

Excel strange (erroneous?) decimal behaviour
 

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

--
Regards,
Tom Ogilvy


"DoctorG" wrote in message
...
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??

Excel strange (erroneous?) decimal behaviour
 

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.

---
Regards,
Norman


"Norman Jones" wrote in message
...
Hi DoctorG,

See Chip Pearson's Rounding errors page at:

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


---
Regards,
Norman



"DoctorG" wrote in message
...
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??

Excel strange (erroneous?) decimal behaviour
 

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?

Excel strange (erroneous?) decimal behaviour
 

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?

"Norman Jones" wrote:

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.

---
Regards,
Norman


"Norman Jones" wrote in message
...
Hi DoctorG,

See Chip Pearson's Rounding errors page at:

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


---
Regards,
Norman



"DoctorG" wrote in message
...
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??

Excel strange (erroneous?) decimal behaviour
 

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. :)
--
Dana DeLouis
Win XP & Office 2003


"DoctorG" wrote in message
...
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?

Excel strange (erroneous?) decimal behaviour
 

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.

"Dana DeLouis" wrote:

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. :)
--
Dana DeLouis
Win XP & Office 2003


"DoctorG" wrote in message
...
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?