Why am I obtaining this strange value multiplying 2 number having different format using Excel?

Question

I am very new to Excel and I have a problem with a simple multiplication (I know, it is depressing but I'm stuck).

I have to multiply the numeric content of 2 cells (these value are calculated using 2 different formulas).

The problem is that it seems that these 2 cells contain numeric values having different format and I obtain a strange result.

Infact I have:

1) The K3 cell containing this value: 0,0783272400

2) The K6 cell containing this value: 728.454911165

In another cell I simply do:

=K3*K6

but now I am obtaining this nonsense value: 57.057.862.655,9996000000

I think that the problem could be related to the fact that the first one use the , do divide integer section and decimal section, and in the other one I am using . to divider the integer section and decimal section.

How can I correctly handle this situation?

I can't replicate this in Excel. How did you get these both entered is as numbers? — Enigmativity, Dec 22 '17 at 14:01
@Enigmativity - add 4 decimals to the result, you will see it. — Vityata, Dec 22 '17 at 14:10
@pnuts - If I did that how would I add the `728.454911165` to another cell? — Enigmativity, Dec 22 '17 at 22:57
@pnuts - If I change my formatting to using a `,` for the decimal separator then `728.454911165` can't be entered as a number. That's what I meant. — Enigmativity, Dec 23 '17 at 06:15

score 0 · Answer 1 · answered Dec 22 '17 at 13:54

Format both values as Currency in Excel and forget about the issue.

You are getting it, because the floating point values are not represented differently in many programming languages. In Excel probably the best way to make sure you do not give strange values is to format as Currency.

Or in VBA to use the CDec and to convert to decimal.

Is floating point math broken?

score 0 · Answer 2 · answered Dec 22 '17 at 14:03

Excel is treating 0,0783272400 as something less than one tenth and 728.454911165 as getting on for one thousand billion. The result is formatted with . for thousands separator and , for decimal separator - and is not nonsensical (though the choice of formatting is).

Why am I obtaining this strange value multiplying 2 number having different format using Excel?

2 Answers2