Posted by Barrie Davidson on April 27, 2001 12:08 PM

Assuming you enter the data in cell A1,

=IF(AND(A1>=B2,A1<G15),1,2).

Posted by Kevin James on April 27, 2001 12:47 PM

At first you said data is entered into A2 and then later into A1. I'm assumming you enter into A1 in the following formula.

=IF(AND(A1>=B2,A1<=G15),1,2)

So, let me guess, we're doing your homework for you?

Posted by Aladin Akyurek on April 27, 2001 4:22 PM

A bit ludic formulation...

==================

Another, a bit playful solution, where I assume the test A1=B2 is essential and it's not necessarily so that G5>B2:

=IF(OR(A1=B2, AND(A1>=MIN(B2,G15),A1<=MAX(B2,G15))),1,2)

Aladin

Posted by Aladin Akyurek on April 27, 2001 10:41 PM

That should have been:

=IF(AND(A1>=MIN(B2,G15),A1<=MAX(B2,G15)),1,2)

Posted by Mark W. on April 28, 2001 6:45 PM

If by "inbetween" you mean exclusively between,

then use the following array formula:

{=IF((A1=B2)+((B2-A1)*(G15-A1)<0),1,2)}

If "inbetween" means inclusively between,

then use the following array formula:

{=IF((B2-A1)*(G15-A1)<=0,1,2)}

> I need to return a "1" if the value entered in

> cell a1 is equal to cell b2 ,or is between b2

> and G15, otherwise return a "2".

> I dont know how to reference the "inbetween"

> the values in b2&g15.

Posted by Mark W. on April 28, 2001 7:13 PM

Actually, there's no need to enter either

formulation as an array.

Use =IF((A1=B2)+((B2-A1)*(G15-A1)<0),1,2) for

an exclusive between...

...or =IF((B2-A1)*(G15-A1)<=0,1,2) for an inclusive between.

Posted by Mark W. on April 29, 2001 3:10 PM

Aladin, I understand and appreciate what you're doing

with the MIN() and MAX() functions... namely, guard-

ing against the uncertainty of which cell contains the

greater value... B2 or G15? However, this concern

can be eliminated altogther by using the product of

the differences of the extreme values and the compar-

ison value, =(B2-A1)*(G15-A1)<0 for an exclusive

between. The "sweet spot" of this formula is where

(B2 < A1 < G15) or (G15 < A1 < B2), and the results

are negative. If A1 is less than both limits then the

result is -p*-q. If A1 is greater than both limits

then the result is p*q.

Posted by Aladin Akyurek on April 29, 2001 4:16 PM

Mark, I already included the one that you proposed in the workbook containing my 'ludic' formulation. Also, appended the reasoning below to it.

Cognition tends to serialize things: B2 comes before G15, so smallest value goes into B2, the largest into G15. A scheme that fits well positive numbers, which is another interesting bias, but not the negative numbers (your 'sweet spot'). Taking such matters seriously, I sometimes think, would make software or models less bug-ridden.