Seemingly incorrect addition

[Laurence Lombard]

I have in A1 the number 1474331 and in A2 the number 3. In A3 the formula
=A1&A2/1000 which I want to result in the string 1474331.001 but strangely
the result is 14743310.001. Where does the extra 0 just to the left of the
decimal come from?
Laurence

 

[Frank Kabel]

Hi Laurence
it comes from your division of cell A2. Try the following formula
=A1&TEXT(A2/1000,".000")

 

[Laurence Lombard]

Is there anything you guys DON'T know!
Thank you very much
Laurence

 

[Laurence Lombard]

OK now it figures A2/1000 is 0.003 and not .003 -
alternatively =text(a1+a2/1000,".000"). Have you encountered this before?
I'm intrigued as to how you worked it out so easily!
Laurence

 

[AlfD]

Hi!

Grumpy here!

The quick shifts between numbers and strings and addition an
concatenation leave me - well, a bit anxious.

Easy to see that 6&7 will give the result 67. But even if the input
were numbers, the output isn't a number. Or is it? Square it and i
will let you...

Similarly, no problem with 3/1000 giving 0.003 (a number) but not .003
which people and computers don't generally do. So we can coerce th
3/1000 to be a string without its leading integer 0. We can concatenat
the result with a long number. And we have an interesting hybrid.

This is, to me, on a par with saying 1 and 1 is eleven. True but wher
does it get you? Maybe into realms of semantics. I for one am going t
continue to separate very carefully my use of + and & (and and and, i
you like).

I reckon modern spreadsheets are sometimes too forgiving. Time was whe
typing would have intervened (or was it grandma?).


Al

 

[Dana DeLouis]

Just personal preference would be to add the two numbers within the Text
function:

=TEXT(A1+B1/1000,"#.000")

What I was thinking is that if the first number was the result of a
calculation, (for example 1474331.000001), then the String join might mess
up your results. Again, it depends on your situation.