| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if statment
not sure on how to do this, got 2 columns first has a dollar value, second
has the formula, the dollar value has both pos and neg value, based on what value is in will return a word, so +/- 1-500=tl +/-501-2000=ss and so on I came up with this wich works for the pos but has to work for the neg as well =if(and(a10,a1<501),"tl",if(and(a1500,a1<2000)," ss","")) and so on, is there a way that the formula can be put in with out having to go something like, =if(and(a10,a1<5001,a1-501,a1<-0),tl,"") and so on it's to look something like this a1 $200 b1 tl a2 $600 b2 ss a3 -$300 b3 tl a4 -$1000 b4 tl |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if statment
You could create an additional column next to your dollar column with the
formula =ABS(xx) where xx is the ref of the cell to the left ABS would make all numbers positive. You'd then run your working calculation on the ABS value "Dreamstar_1961" wrote in message ... not sure on how to do this, got 2 columns first has a dollar value, second has the formula, the dollar value has both pos and neg value, based on what value is in will return a word, so +/- 1-500=tl +/-501-2000=ss and so on I came up with this wich works for the pos but has to work for the neg as well =if(and(a10,a1<501),"tl",if(and(a1500,a1<2000)," ss","")) and so on, is there a way that the formula can be put in with out having to go something like, =if(and(a10,a1<5001,a1-501,a1<-0),tl,"") and so on it's to look something like this a1 $200 b1 tl a2 $600 b2 ss a3 -$300 b3 tl a4 -$1000 b4 tl |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if statment
not a way the that would have thought of, just had to find a empty column
that I could put it in, but it works, I just put that column it's in to hidden so it could not be seen, thanks for the help "RFJ" wrote: You could create an additional column next to your dollar column with the formula =ABS(xx) where xx is the ref of the cell to the left ABS would make all numbers positive. You'd then run your working calculation on the ABS value "Dreamstar_1961" wrote in message ... not sure on how to do this, got 2 columns first has a dollar value, second has the formula, the dollar value has both pos and neg value, based on what value is in will return a word, so +/- 1-500=tl +/-501-2000=ss and so on I came up with this wich works for the pos but has to work for the neg as well =if(and(a10,a1<501),"tl",if(and(a1500,a1<2000)," ss","")) and so on, is there a way that the formula can be put in with out having to go something like, =if(and(a10,a1<5001,a1-501,a1<-0),tl,"") and so on it's to look something like this a1 $200 b1 tl a2 $600 b2 ss a3 -$300 b3 tl a4 -$1000 b4 tl |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if statment
While the solution works fine, there's really no need for the extra
column - see my other reply. In article , Dreamstar_1961 wrote: not a way the that would have thought of, just had to find a empty column that I could put it in, but it works, I just put that column it's in to hidden so it could not be seen, thanks for the help |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if statment
One way:
B1: =LOOKUP(ABS(A1),{0,"tl";500.01,"ss";2000.01,"somet hing else"}) In article , Dreamstar_1961 wrote: not sure on how to do this, got 2 columns first has a dollar value, second has the formula, the dollar value has both pos and neg value, based on what value is in will return a word, so +/- 1-500=tl +/-501-2000=ss and so on I came up with this wich works for the pos but has to work for the neg as well =if(and(a10,a1<501),"tl",if(and(a1500,a1<2000)," ss","")) and so on, is there a way that the formula can be put in with out having to go something like, =if(and(a10,a1<5001,a1-501,a1<-0),tl,"") and so on it's to look something like this a1 $200 b1 tl a2 $600 b2 ss a3 -$300 b3 tl a4 -$1000 b4 tl |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if statment
this one I like better, as it's smaller formula, and easyer to add to the
work file. thanks for the help "JE McGimpsey" wrote: One way: B1: =LOOKUP(ABS(A1),{0,"tl";500.01,"ss";2000.01,"somet hing else"}) In article , Dreamstar_1961 wrote: not sure on how to do this, got 2 columns first has a dollar value, second has the formula, the dollar value has both pos and neg value, based on what value is in will return a word, so +/- 1-500=tl +/-501-2000=ss and so on I came up with this wich works for the pos but has to work for the neg as well =if(and(a10,a1<501),"tl",if(and(a1500,a1<2000)," ss","")) and so on, is there a way that the formula can be put in with out having to go something like, =if(and(a10,a1<5001,a1-501,a1<-0),tl,"") and so on it's to look something like this a1 $200 b1 tl a2 $600 b2 ss a3 -$300 b3 tl a4 -$1000 b4 tl |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If statment if its possible. | Excel Discussion (Misc queries) | |||
| SumIF Statment | Excel Discussion (Misc queries) | |||
| Something is wrong with this IF statment | Excel Worksheet Functions | |||
| If statment in Chart | Excel Discussion (Misc queries) | |||
| If statment | Excel Worksheet Functions | |||
Hybrid Mode
