# "IF" involving intervals

#### wvan

##### New Member
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

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
What if A1 equals 0 or 3?

Well, I tried to modify the suggested formula

=IF(C3>D3;1;IF(C3=D3;"N";2))

to

=IF(K7>3;1;IF(K7>0<3;"N";2))

or

=IF(K7>3;1;IF(K7>0 and K7<3;"N";2))

but I only get results of 1 or 2 though i know there should be N's

try ...

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

one may be able to simplify this to save some key strokes but it works.

Regards!

Yogi

The vlookup option would be
=VLOOKUP(A1,{-999,2;0,"N";3,1},2)

where your lowest expected value is -999. Adjust downward to suit your criteria. This could also be "dressed" up in the case that A1 is blank.

good luck

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,
=IF(A1>3;1,IF(A1>0;"N";2))

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

Also, seach the Help for the proper syntax of the AND function. It is not needed here, but when it is, you will need to change the syntax to AND(test1,test2,...)

On 2002-08-21 14:46, IML wrote:
The vlookup option would be
=VLOOKUP(A1,{-999,2;0,"N";3,1},2)

where your lowest expected value is -999. Adjust downward to suit your criteria. This could also be "dressed" up in the case that A1 is blank.

good luck

But, VLOOKUP's suitability would depend on the answer to my question above.

try this to assure all points from 0 to 3 including are accounted for

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

this will fix the 0 and 3 issue along with the possibilty of a -1 in cell a1 (less than 0)

On 2002-08-21 14:23, wvan wrote:
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

if A1 >= 3 ==> -1
if A1 > 0 and A1< 3 ==> 0 [or "N" ]
if A1<= 0 ==> 1 ?

The formula for this would be:

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

A 0 result can be rendered as "N" by custom formatting the formula cell as:

[=0]"N";General
3ValuedLogic wvan.xls
ABCD
13-1-1
2011
310N
420N
55-1-1
6-411
7
Sheet1

Aladin, just curious, why the =0+ at the beggining ?

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

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

Replies
5
Views
404
Replies
2
Views
369
Replies
5
Views
293
Replies
1
Views
284
Replies
4
Views
246

1,218,742
Messages
6,144,220
Members
450,531
Latest member
avril18

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back