Text String Calulations

[filky]

Can anyone explain the following probolem I have encountered.
I have imported some details from MSProject that inclue a cell values
for example

1.45 hrs
1.25 hrs

To sum these texts I used =LEFT(A1,LEN(A1)-4) in column B to remove th
hrs.

If I then use +B1+B2 it adds up the figures to be 2.7 - correct
If I use =SUM(B1:B2) it adds them up to 0 - incorrect

So, I amend the formulae to =VALUE(LEFT(A1,LEN(A1)-4)) in column B an
then repeat the exercise. This time they are both correct.

I could understand this if both were 0 in the first instance as th
result is a text string. But why is only one 0

 

[Frank Kabel]

Hi
The mathematical operation'+' forces Excel to convert the strings to
numbers. SUM on the opposite ignores text values. You can see a similar
difference if you try the following:
A1: Text
A2: 2

Now use
=A1+A1
(you'll receive an error #VALUE)

=SUM(A1:A2) or =SUM(A1,A2)
and you'll receive '2'


Sidenote:
instead of
=VALUE(LEFT(A1,LEN(A1)-4))

you could use
=--LEFT(A1,LEN(A1)-4)

 

[Jason Morin]

In the first instance both numbers are still text. They
are coerced into numbers when you add them directly
using "+". However, the SUM function does not coerce them
into actual numbers and because SUM ignores text strings,
it returns 0. When you use VALUE, the strings convert to
text and thus the SUM function will return the correct
total.

HTH
Jason
Atlanta, GA

 

[filky]

Thanks. This does make sense - I won't tell you how long it took me t
figure out how to make it work at all..