Searching for low and high number
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
|