finding range of numbers in one row to another row

[Mike]

Hello All,
I posted earlier today but there was some confusion on how I posted the
question.
What i have two rows of data with numbers in columns A & B. What I would
like to do: check the numbers in A2:B2, which in this case is 57 and 24.
And then I need to find out if any numbers in the range of 57 to 24
(57,56,55,54,....etc...26,25,24) are in the range of numbers of A1:B2, in
this example 50 to 32. The formula would be something like this:
=if(a2:b2)=range(a1:b1),"ok","NO")

For example:

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

so in this example the result would be "ok" because some of the numbers in
A2:B2 are in the range of A1:B1 (50.....to 32).
If A2 was 66 and B2 was 53 then it would return "NO" as of none of those
numbers are in the range of 50 to 32.

There will be times when the number in A2 will be larger or smaller than A1
and also B2 and B1. I am just looking for any numbers in the range of A2:B2
are in the range of A1:B1.

Thanks in advance,
Mike

 

[ScottO]

Mike, will the value in ColA ALWAYS be >= the value in ColB, like in
your example. Or can the smaller number sometimes show up in ColA?
Rgds,
ScottO

| Hello All,
| I posted earlier today but there was some confusion on how I posted
the
| question.
| What i have two rows of data with numbers in columns A & B. What
I would
| like to do: check the numbers in A2:B2, which in this case is 57
and 24.
| And then I need to find out if any numbers in the range of 57 to 24
| (57,56,55,54,....etc...26,25,24) are in the range of numbers of
A1:B2, in
| this example 50 to 32. The formula would be something like this:
| =if(a2:b2)=range(a1:b1),"ok","NO")
|
| For example:
|
| A B
| ----------------------
| 1 50 32
| 2 57 24
|
| so in this example the result would be "ok" because some of the
numbers in
| A2:B2 are in the range of A1:B1 (50.....to 32).
| If A2 was 66 and B2 was 53 then it would return "NO" as of none of
those
| numbers are in the range of 50 to 32.
|
| There will be times when the number in A2 will be larger or smaller
than A1
| and also B2 and B1. I am just looking for any numbers in the range
of A2:B2
| are in the range of A1:B1.
|
| Thanks in advance,
| Mike
|
|

 

[Mike]

ScottO,

the value in Col A will always be greater than the value in Col B

 

[ScottO]

Then try this out ...
=IF(OR(AND(A2<A1,A2>B1),AND(A2>A1,B2<A1),AND(B2<B1,A2>B1)),"OK","NO")

I think that catches all the possibilities, but I might have tangled
myself up somewhere
I wasn't sure whether you wanted strictly 'greater/less than', or if
you wanted to allow for 'equal to' as well, so I left out the = signs
(assuming 'strictly').
If my assumption is wrong, then you can put them in as required.

Rgds,
ScottO

| ScottO,
|
| the value in Col A will always be greater than the value in Col B
|
|
message
| | > Mike, will the value in ColA ALWAYS be >= the value in ColB, like
in
| > your example. Or can the smaller number sometimes show up in
ColA?
| > Rgds,
| > ScottO
| >
| > | > | Hello All,
| > | I posted earlier today but there was some confusion on how I
posted
| > the
| > | question.
| > | What i have two rows of data with numbers in columns A & B.
What
| > I would
| > | like to do: check the numbers in A2:B2, which in this case is
57
| > and 24.
| > | And then I need to find out if any numbers in the range of 57
to 24
| > | (57,56,55,54,....etc...26,25,24) are in the range of numbers of
| > A1:B2, in
| > | this example 50 to 32. The formula would be something like
this:
| > | =if(a2:b2)=range(a1:b1),"ok","NO")
| > |
| > | For example:
| > |
| > | A B
| > | ----------------------
| > | 1 50 32
| > | 2 57 24
| > |
| > | so in this example the result would be "ok" because some of the
| > numbers in
| > | A2:B2 are in the range of A1:B1 (50.....to 32).
| > | If A2 was 66 and B2 was 53 then it would return "NO" as of none
of
| > those
| > | numbers are in the range of 50 to 32.
| > |
| > | There will be times when the number in A2 will be larger or
smaller
| > than A1
| > | and also B2 and B1. I am just looking for any numbers in the
range
| > of A2:B2
| > | are in the range of A1:B1.
| > |
| > | Thanks in advance,
| > | Mike
| > |
| > |
| >
| >
|
|

 

[Mike]

ScottO, The formula you made up works (I added the equal signs). I want
to thank you so much for your help, this was a very tough one, I really
appreciate it.

Mike

 

[ScottO]

Glad to help.
S

| ScottO, The formula you made up works (I added the equal signs).
I want
| to thank you so much for your help, this was a very tough one, I
really
| appreciate it.
|
| Mike
|
|
message
| | > Then try this out ...
| >
=IF(OR(AND(A2<A1,A2>B1),AND(A2>A1,B2<A1),AND(B2<B1,A2>B1)),"OK","NO")
| >
| > I think that catches all the possibilities, but I might have
tangled
| > myself up somewhere
| > I wasn't sure whether you wanted strictly 'greater/less than', or
if
| > you wanted to allow for 'equal to' as well, so I left out the =
signs
| > (assuming 'strictly').
| > If my assumption is wrong, then you can put them in as required.
| >
| > Rgds,
| > ScottO
| >
| > | > | ScottO,
| > |
| > | the value in Col A will always be greater than the value in Col
B
| > |
| > |
| > message
| > | | > | > Mike, will the value in ColA ALWAYS be >= the value in ColB,
like
| > in
| > | > your example. Or can the smaller number sometimes show up in
| > ColA?
| > | > Rgds,
| > | > ScottO
| > | >
| > | > | > | > | Hello All,
| > | > | I posted earlier today but there was some confusion on how
I
| > posted
| > | > the
| > | > | question.
| > | > | What i have two rows of data with numbers in columns A &
B.
| > What
| > | > I would
| > | > | like to do: check the numbers in A2:B2, which in this case
is
| > 57
| > | > and 24.
| > | > | And then I need to find out if any numbers in the range of
57
| > to 24
| > | > | (57,56,55,54,....etc...26,25,24) are in the range of
numbers of
| > | > A1:B2, in
| > | > | this example 50 to 32. The formula would be something like
| > this:
| > | > | =if(a2:b2)=range(a1:b1),"ok","NO")
| > | > |
| > | > | For example:
| > | > |
| > | > | A B
| > | > | ----------------------
| > | > | 1 50 32
| > | > | 2 57 24
| > | > |
| > | > | so in this example the result would be "ok" because some of
the
| > | > numbers in
| > | > | A2:B2 are in the range of A1:B1 (50.....to 32).
| > | > | If A2 was 66 and B2 was 53 then it would return "NO" as of
none
| > of
| > | > those
| > | > | numbers are in the range of 50 to 32.
| > | > |
| > | > | There will be times when the number in A2 will be larger or
| > smaller
| > | > than A1
| > | > | and also B2 and B1. I am just looking for any numbers in
the
| > range
| > | > of A2:B2
| > | > | are in the range of A1:B1.
| > | > |
| > | > | Thanks in advance,
| > | > | Mike
| > | > |
| > | > |
| > | >
| > | >
| > |
| > |
| >
| >
|
|