# "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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### Mark W.

##### MrExcel MVP
What if A1 equals 0 or 3?

#### wvan

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

#### Yogi Anand

##### MrExcel MVP
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

#### IML

##### MrExcel MVP
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

#### Jay Petrulis

##### MrExcel MVP
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,...)

#### Mark W.

##### MrExcel MVP
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.

#### lars

##### Board Regular
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)

##### MrExcel MVP
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

#### Juan Pablo González

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

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

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

Replies
9
Views
291
Replies
6
Views
372
Replies
0
Views
244
Replies
1
Views
163
Replies
18
Views
749

### Forum statistics

1,190,833
Messages
5,983,155
Members
439,824
Latest member
jr599 ### 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