adding cells after stripping numbers out of text fields

[Alan]

I have a time sheet where I want to add the last 3 characters in each of
the cells (numbers) to get a grand total for the row.
Here's a sample:


WED THUR FRI SAT SUN TOTAL


N3 - 7.5 N3 - 7.5 N3 - 7.5 N3 - 7.5


If I treat the 5 columns as A to E, the total in cell F2 might be the
formula =value(right(A2,3))+value(right(B2,3))+value(right(C2,3))+value
(right(D2,3))+value(right(E2,3))

The problem is that the cell B2 (THUR) is blank so Excel interprets this as
an error. Can anyone offer me a formula that will work if it is blank? I
also will have a total of 14 cells to add (2 weeks) - this is just a
sample. It will also not do to place 0's in the empty cells, it's too ugly.

TIA!

Alan

 

[Ron Rosenfeld]

I have a time sheet where I want to add the last 3 characters in each of
the cells (numbers) to get a grand total for the row.
Here's a sample:


WED THUR FRI SAT SUN TOTAL


N3 - 7.5 N3 - 7.5 N3 - 7.5 N3 - 7.5


If I treat the 5 columns as A to E, the total in cell F2 might be the
formula =value(right(A2,3))+value(right(B2,3))+value(right(C2,3))+value
(right(D2,3))+value(right(E2,3))

The problem is that the cell B2 (THUR) is blank so Excel interprets this as
an error. Can anyone offer me a formula that will work if it is blank? I
also will have a total of 14 cells to add (2 weeks) - this is just a
sample. It will also not do to place 0's in the empty cells, it's too ugly.

TIA!

Alan

2 possible solutions:

=SUM(IF(ISNUMBER(--RIGHT(A4:E4,3)),--RIGHT(A4:E4,3),0))

entered as an **array** formula: After typing in the formula, hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula.

You could put 0's in the blank cells, and then format the cells so that the 0's
do not show. If you did that, you could use the non-array formula:

=SUMPRODUCT(RIGHT(A4:E4,3)*1)


--ron