Seemingly incorrect addition

  • Thread starter Thread starter Laurence Lombard
  • Start date Start date
L

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
 
Hi Laurence
it comes from your division of cell A2. Try the following formula
=A1&TEXT(A2/1000,".000")
 
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
 
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
 
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.
 
Back
Top