"IF" involving intervals

[wvan]

I run Excel on native MS Office 2002 XP.

I am trying to define 3 conditions for analysing my data, one of which includes an interval. These are:

If A1 > 3 then A2 = 1
If A1 < 3 and > 0 then A2 = N
If A1 < 0 then A2 = 2

I am frustrated in my attempts to use MS Help because it is formatted to accept only 2 outcomes (true or false)

Thank you for you help

 

[Aladin Akyurek]

On 2002-08-21 16:02, Juan Pablo G. wrote:
Aladin, just curious, why the =0+ at the beggining ?

=0+((A1<=0)-(A1>=3))

shouldn't the "-" take care of the coercing ?

Yes, it will. I had actually another part in between. When it occurred to me to remove that, I forgot to remove 0+ together with it.

So lets make it:

=((A1<=0)-(A1>=3))

How about the logic, Juan?


Aladin

 

[Juan Pablo González]

I liked it, you know, since you're formatting the 0 to appear as "N", you could also format the negatives to appear as "2"...

 

[wvan]

On 2002-08-21 14:54, Jay Petrulis wrote:
Hi,

You need to answer Mark's question -- what happens at 0 and 3?

If you want the following
x -> (oo,3) = 1
x -> [3,0) = N
x -> [0,-oo) = 2

try,
=C

Change the > to >= if you want to test the critical points differently

----

Probably because the sintax is different on Excel XP 2002 I get an error message. I am then suggested a general formula which is

If(Logical_Test;[value_if_true];[value_if_fase])

which is placed in yellow under the formula in copied.

In that general formula, the [value_if_true] part is highlighted as well as the specific formula from the ";" onwards namely

1,IF(A1>0;"N";2))

obviously, my version of Excel assumes that the above expression is the [value_if_true] part of the formula. I tried changing around the ";" for "," but to no avail.

On the other hand,

=0+((A1<=0)-(A1>=3)) works but I would love to express it using IF.

Thank you

 

[Aladin Akyurek]

...

On the other hand,

=0+((A1<=0)-(A1>=3)) works but I would love to express it using IF.

You can omit 0+, so it's:

=(A1<=0)-(A1>=3)

which is re-expressible in terms of IF as:

=IF(A1<=0,1,IF(A1>=3,-1,0))

or

=IF(A1<=0,1,IF(A1>=3,-1,"N"))

Aladin