IF formula question...
Posted by Paul Magruder on April 27, 2001 12:02 PM
What would be the if formula for:
Data is entered in cell A2
Cell B2's value = 10
Cell G15's Value = 20
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.
Thanks in advance
Paul
Paul
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
Revised...
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
Okay, my turn...
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
Correction...
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
Re: Revised...
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
Re: Revised...
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.
