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

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

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

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

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

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