Something is not "right"

  • Thread starter Thread starter Corey872
  • Start date Start date
C

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
 
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
 
-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
 
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
 
You could get Sum to work as an Array formula:

{=SUM(VALUE(B1:B4))}
or
{=SUM(--B1:B4)}
 
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
 
Back
Top