Formula help with having characters in the number

J

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?
 
L

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
 
J

Jeremy

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

David Biddulph

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top