Something is not "right"

[Corey872]

Seem to have stumbled across something strange:

Example - Cells A1 through A4 are 10,11,20,22 respectively. Cells B1
through B4 contain the respective formula "=RIGHT(A1)", etc to return
the right most character in the A1 through A4 cells. So cells B1
through B4 are 0,1,0,2.

The problem is when I make another cell that is "=SUM(B1:B4)" it is
always "0"? If I make a cell that is "=A1+A2+A3+A4" it does return
the proper value of "3" in this case.

Why won't the simple "sum" formula work?

Thanks

Corey

 

[Peo Sjoblom]

Try

=--RIGHT(A1)

then sum them, all text functions return txt format

 

[Bernie Deitrick]

Corey,

SUM ignores strings, which is what your values are, wheras the + coerces a
change to value.

Change your formula to
=VALUE(RIGHT(A1,1))

and the SUM will work.

HTH,
Bernie
MS Excel MVP

 

[George Nicholson]

-The RIGHT function returns text/string characters, not true numbers.
-SUM ignores text/string characters.
-Therefore, SUM(..a bunch of text, and ONLY text...) = 0

 

[Jerry W. Lewis]

That RIGHT() returns a string rather than a number has been repeatedly
explained. A possibly cleaner approach than coercing that string into a
number would be to use MOD(A1,10) instead of RIGHT(A1,1).

Jerry

 

[Dana DeLouis]

You could get Sum to work as an Array formula:

{=SUM(VALUE(B1:B4))}
or
{=SUM(--B1:B4)}

 

[Peo Sjoblom]

And if you don't want to array enter a formula

=SUMPRODUCT(--(B1:B4))

 

[Corey872]

Thanks all,

I suspected that the RIGHT function may be returning a text value, but
thought that would be cured by formatting the return cells as
"number". Apparently not. Using the "--RIGHT" seems to have solved
the issue.

Thanks agian for the help.

Corey