=IF function

[Mike]

Hello All,
Using Windows and Excel XP.

I have a spreadsheet that contains numbers in A1:B2. What I want to do is
to check the numbers in A2:B2 to see if those numbers fall in the range of
numbers in A1:B1. If one OR both of the numbers fall in the range I would
like for the formula to return "ok", and if it doesn't I would like for it
to return "NO".
Example #1
A B
-----------------------------------
1 40 37
2 37 12

in this example 37 (in A2) falls in between 40 (A1) and 37 (B1), but 12 (B2)
does not. Because the number 37 (A2) is in the range of 40 (A1) and 37
(B1) I would like the formula to return "ok".

Example #2
A B
----------------------------------
1 22 7
2 30 23

in this example 30 (A2) does not fall in the range of 22 (A1) and 7 (B1) and
the 23 (B2) does not fall in the range of 22 (A1) and 7 (B1). Because the
numbers in A2:B2 do not fall in the range of A1:B1 I would like for the
formula to return "NO".

So I am always comparing the data in row 2 to the data in row 1.

The formula would be something like: =if(A2=rangeA1:B1) OR
if(B2=range(A1:B1),"ok","NO")

I need help writing a correct formula.
Thanks in advance,
Mike

 

[Bernard Liengme]

If we may assume that A1 will always > B1 then
=OR(AND(A2<=A1,A2>=B1),AND(B2<=A1,B2>=B1))
otherwise
=OR(AND(A2<=MAX(A1:B1),A2>=MIN(A1:B1)),AND(B2<=MAX(A1:B1),B2>=MIN(A1:B1)))
Of course, these return TRUE or FALSE so you need an IF wrapper
IF(OR(AND(A2<=A1,A2>=B1),AND(B2<=A1,B2>=B1)), "OK", "NO")

 

[Bob Phillips]

Mike,

Here is an array formula

=IF(ISNUMBER(MATCH(B1:B2,ROW(INDIRECT(A1&":"&A2)),0)),"ok","")

as an array formula, it should be committed with Ctrl-Shift-Enter, not just
Enter

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

If the upper and lower limits might be in either cell of A1:B1, try this:

=IF(SUMPRODUCT((A2:B2>=MIN(A1:B1))*(A2:B2<=MAX(A1:B1))),"OK","NO")


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

Hi Mike

The formulae is soemthing like
=IF(A2<A1,IF(A2>B1,"ok",IF(B2<A1,IF(B2>B1,"ok","no"))),IF(B2<A1,IF(B2>B1,"ok","no"))).

You will need to decide whether a value equally to the range extremes is
inside or outside the range

Eric

 

[Bernard Liengme]

Neater than mine!
But SUMPRODUCT is not needed here
=IF((A2:B2>=MIN(A1:B1))*(A2:B2<=MAX(A1:B1)),"OK","NO")
works
best wishes

 

[Bernard Liengme]

Foot in mouth! Wish I could recall that message! SUMPROUDUCT IS needed

 

[Mike]

Thank you Bernard for your help. I apparently posed my question wrong....
I applied your formula to my worksheet and there are instances where the
numbers in B1 will be greater than A1 and number in B2 will be less than A2
but still will be in the range of A1:B1.
For example:

A B
----------------------
1 50 32
2 57 24

the range of numbers in A1:B1 is 50,49,48,47,etc . . . .32
the range of numbers in A2:B2 is 57,56,55,54,53,52,51,50,49, etc......24

Because some of the numbers in A2:B2 are in the range of A1:B1 I would like
to return TRUE or "OK".

If the numbers in A2:B2 were 65 and 51 then those numbers are NOT in the
range of A1:B2 and would like to return FALSE or "NO".
I would like to have a formula for that situation.
Thanks again,


Mike

 

[Bob Phillips]

if it is comparing row to row, it should be

=IF(ISNUMBER(MATCH(A2:B2,ROW(INDIRECT(A1&":"&B1)),0)),"ok","")

still an array formula

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Mike]

Hi Bob,
The formula you gave me is returning a blank (NO) when I have
A B
--------------
1 55 30
2 58 18

It should return OK because the range of 58 to 18 has numbers in A1:B1 (55
through 30).
thanks.
Mike