Shane,

Ah I see, you mean a formula like mine which copes with duplicates and
random sorted numbers.

Mike

"Shane Devenshire" wrote:

Absolutely, I just wanted to make this point which might be useful if others
are looking for a slightly different answer or the OP misstated the goal.
For example, I don't believe the OP stated that the numbers had to be in
ascending order nor that there were no duplicates, nor how to handle
duplicated if they occured.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Mike H" wrote:

Shane

since by definition you don't want the min and max to be the same.

By definition that's precisely what the OP wanted

and if the number equals one of the numbers in the column the
solution should be the exact number if i have 31 the solution should
be 31

Mike


"Shane Devenshire" wrote:

Hi,

It would be nice to use the BETWEEN function, ha! but there isn't one in
Excel.

You can make a minute simplification to the previous suggestion
=MIN(IF(A1:A8D1,A1:A8))
since by definition you don't want the min and max to be the same. This
assumes that your first formula is in D1

Of course these suggestions assume that the data is in Ascending order and
my simplification assumes that there are no duplicates.

If the numbers are not in order:
Use the following array to get the lower number and
=MAX(IF(A1:A8<=C1,A1:A8))
and
=MIN(IF(A1:A8D1,A1:A8))
to get the upper number

These two formulas assume the number you want to check for is in C1 and the
first formula is entered in D1

These must be entered by pressing Shift+Ctrl+Enter

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Mortir" wrote:



sorry made a slight mistake above:
- for 21, the solution should be 7 and 31 not 32