"IF" involving intervals

wvan

New Member
Joined
Aug 18, 2002
Messages
13
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.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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,...)
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 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

How about this "three-valued logic":

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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top