I have the following example

A column with number of days:

days
3
7
31
61
91
181
271
365

Now i have a certain formula which returns to me a number of days -
for example 21. 21 belongs in between 7 and 31 in the days column. I
am looking for a formulas that would return this high and low number
of the interval in which my number belongs to:

- for 21, the solution should be 7 and 32,
- for 183, the solution should be 181 and 271
- 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

is there a way to do this. thanx for your help!

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

Hi,

I assume if there is an exact match you want that. with your lookup value in
D1

For the lower number

=LOOKUP(D1,A1:A8)

For the higher

=MIN(IF(A1:A8=D1,A1:A8))

The second formula is an array.
'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Mortir" wrote:



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

Hi Mortir
Did you try MAX and MIN eg: =MAX(A2:A3) same for MIN.
Regards
John
"Mortir" wrote in message
...
I have the following example

A column with number of days:

days
3
7
31
61
91
181
271
365

Now i have a certain formula which returns to me a number of days -
for example 21. 21 belongs in between 7 and 31 in the days column. I
am looking for a formulas that would return this high and low number
of the interval in which my number belongs to:

- for 21, the solution should be 7 and 32,
- for 183, the solution should be 181 and 271
- 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

is there a way to do this. thanx for your help!

On Sun, 25 Jan 2009 09:55:47 -0800 (PST), Mortir
wrote:

I have the following example

A column with number of days:

days
3
7
31
61
91
181
271
365

Now i have a certain formula which returns to me a number of days -
for example 21. 21 belongs in between 7 and 31 in the days column. I
am looking for a formulas that would return this high and low number
of the interval in which my number belongs to:

- for 21, the solution should be 7 and 32,
- for 183, the solution should be 181 and 271
- 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

is there a way to do this. thanx for your help!


If your table of days are in A2:A9 and your number is in B1 you may
try this formula (all in one line):

=IF(ISERROR(VLOOKUP(B1,A2:A9,1,FALSE)),VLOOKUP(B1, A2:A9,1,TRUE)&" and
"&INDEX(A2:A9,MATCH(B1,A2:A9,1)+1),B1)

Change the range A2:A9 to fit your days table.

Hope this helps / Lars-Åke

for low number
=INDEX(A1:A8,MATCH(B1,A1:A8,1),0)

for high number
=INDEX(A1:A8,MATCH(B1,A1:A8,1)+1,0)

On Jan 25, 10:55*pm, Mortir wrote:
I have the following example

A column with number of days:

days
3
7
31
61
91
181
271
365

Now i have a certain formula which returns to me a number of days *-
for example 21. 21 belongs in between 7 and 31 in the days column. I
am looking for a formulas that would return this high and low number
of the interval in which my number belongs to:

- for 21, the solution should be 7 and 32,
- for 183, the solution should be 181 and 271
- 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

is there a way to do this. thanx for your help!

On Jan 25, 7:35*pm, muddan madhu wrote:
for low number
=INDEX(A1:A8,MATCH(B1,A1:A8,1),0)

for high number
=INDEX(A1:A8,MATCH(B1,A1:A8,1)+1,0)

On Jan 25, 10:55*pm, Mortir wrote:

I have the following example

A column with number of days:

days
3
7
31
61
91
181
271
365

Now i have a certain formula which returns to me a number of days *-
for example 21. 21 belongs in between 7 and 31 in the days column. I
am looking for a formulas that would return this high and low number
of the interval in which my number belongs to:

- for 21, the solution should be 7 and 32,
- for 183, the solution should be 181 and 271
- 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

is there a way to do this. thanx for your help!

tnx guys. realy appreciate your help!!!!

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

Hi,

Take out my = sign in
=MAX(IF(A1:A8<=C1,A1:A8))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"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

And one more optional change, instead of reference the first formula in the
second one you could just use

=MIN(IF(A1:A8C1,A1:A8))

Where C1 is the number you are comparing to.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"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

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

for high number
=INDEX(A1:A8,MATCH(B1,A1:A8,1)+1,0)

If the lookup_value *is* the high number that will return an error.


--
Biff
Microsoft Excel MVP


"muddan madhu" wrote in message
...
for low number
=INDEX(A1:A8,MATCH(B1,A1:A8,1),0)

for high number
=INDEX(A1:A8,MATCH(B1,A1:A8,1)+1,0)

On Jan 25, 10:55 pm, Mortir wrote:
I have the following example

A column with number of days:

days
3
7
31
61
91
181
271
365

Now i have a certain formula which returns to me a number of days -
for example 21. 21 belongs in between 7 and 31 in the days column. I
am looking for a formulas that would return this high and low number
of the interval in which my number belongs to:

- for 21, the solution should be 7 and 32,
- for 183, the solution should be 181 and 271
- 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

is there a way to do this. thanx for your help!

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

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