if statment

[Guest]

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(a1>0,a1<501),"tl",if(and(a1>500,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(a1>0,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

 

[RFJ]

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

 

[JE McGimpsey]

One way:

B1: =LOOKUP(ABS(A1),{0,"tl";500.01,"ss";2000.01,"something else"})

 

[Guest]

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

 

[JE McGimpsey]

While the solution works fine, there's really no need for the extra
column - see my other reply.

 

[Guest]

this one I like better, as it's smaller formula, and easyer to add to the
work file.
thanks for the help