why does 111111111111 X 111111111111 end in 0?

[Guest]

when working out the answer to 111111111111 X 111111111111 in excel the
answer ends in 0 but it should end in 1
we have played with lots of combinations of 1111 X 1111 sums and it seems to
go wrong when you get to 111111111 X 111111111
Anyone got any ideas?

 

[Gord Dibben]

Because Excel will only go to 15 significant places.

After the 15th place it will start substituting zeros.


Gord Dibben MS Excel MVP

 

[Guest]

I'm just stabbing in the dark here, but are you using new math? 1x1 is 1,
11x11 is 121... how do you get 1 from 111111111111 X 111111111111 ??

 

[Alan]

END in 1

I'm just stabbing in the dark here, but are you using new math? 1x1 is 1,
11x11 is 121... how do you get 1 from 111111111111 X 111111111111 ??

 

[Guest]

Excel only accomodates 15 significant digits. Everything after the 15th
number is converted to 0.

You might look at VBA for some help to get the answer. Copy/Paste the
function into a general module, then enter =MyProduct(A1,A1) where A1 =
111111111111. The answer is text-which is the only way to see the digits
after 15. Note the decimal data type is limited (for positive numbers) to
79,228,162,514,264,337,593,543,950,335



Option Explicit

Function MyProduct(dbl1, dbl2) As String
Dim dec1 As Variant
Dim dec2 As Variant
Dim dec3 As Variant

dec1 = CDec(dbl1)
dec2 = CDec(dbl2)
dec3 = dec1 * dec2
MyProduct = CStr(dec3)

End Function