Formula help with having characters in the number

[Jeremy]

I have a formula in C1 that reads
=IF(RIGHT(A2,1)="+",MID(Sheet1!A2,1,SEARCH("+",Sheet1!A2)-1)*(B2+0.5),A2*B2).
When you place a 15 in A1 C1 answers 300 where if you put a 15+ in A1 C1
will answer 307.5. The problem I am having is if you put in A3 =Sum(A1:A2)
you will get a 0 or only what is in A2 if you have 15+ in A1. The problem is
from the + how do I fix it so it will add both A1 and A2 if there is a number
and + in A1?

 

[Lars-Åke Aspelin]

I have a formula in C1 that reads
=IF(RIGHT(A2,1)="+",MID(Sheet1!A2,1,SEARCH("+",Sheet1!A2)-1)*(B2+0.5),A2*B2).
When you place a 15 in A1 C1 answers 300 where if you put a 15+ in A1 C1
will answer 307.5. The problem I am having is if you put in A3 =Sum(A1:A2)
you will get a 0 or only what is in A2 if you have 15+ in A1. The problem is
from the + how do I fix it so it will add both A1 and A2 if there is a number
and + in A1?

Try this formula in cell A3:

=SUMPRODUCT(0+SUBSTITUTE(A1:A2,"+",""))

Hope this helps / Lars-Åke

 

[Jeremy]

How does this formula know to add .50 to b2 when there is a + after the
number in A1?

 

[David Biddulph]

You could try changing the formula to
=SUMPRODUCT(0+SUBSTITUTE(A1:A2,"+",".5"))