Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Set the number of characters in a column | Excel Discussion (Misc queries) | |||
Set the number of characters in a column | Excel Discussion (Misc queries) | |||
Number of Characters for a formula | Excel Discussion (Misc queries) | |||
Number of Characters | Excel Discussion (Misc queries) | |||
Defined number of characters | Excel Discussion (Misc queries) |