Formula help with having characters in the number
 

I have a formula in C1 that reads
=IF(RIGHT(A2,1)="+",MID(Sheet1!A2,1,SEARCH("+",She et1!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?

Formula help with having characters in the number
 

On Thu, 3 Sep 2009 13:02:02 -0700, Jeremy
wrote:

I have a formula in C1 that reads
=IF(RIGHT(A2,1)="+",MID(Sheet1!A2,1,SEARCH("+",Sh eet1!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

Formula help with having characters in the number
 

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


"Lars-Ã…ke Aspelin" wrote:

On Thu, 3 Sep 2009 13:02:02 -0700, Jeremy
wrote:

I have a formula in C1 that reads
=IF(RIGHT(A2,1)="+",MID(Sheet1!A2,1,SEARCH("+",Sh eet1!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

Formula help with having characters in the number
 

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

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


"Lars-Åke Aspelin" wrote:

On Thu, 3 Sep 2009 13:02:02 -0700, Jeremy
wrote:

I have a formula in C1 that reads
=IF(RIGHT(A2,1)="+",MID(Sheet1!A2,1,SEARCH("+",She et1!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