Find the first lowest value in a column that exceeds $foo

[2008]

I've got a column with a list of ordered values like this:

Code:

100000000
50000000
33333333
25000000
20000000
16666667
14285714
12500000
11111111
10000000
9090909
8333333
7692308
7142857
6666667
6250000
5882353
5555556
5263158
5000000
4761905
4545455
4347826
4166667

And a user enters a value, which could be anything up to the highest
value in this column. I need a method of finding the lowest number in
the column that is larger than the user entered value.

for example, if the user entered 4500000, then the value I'd need
would be 4545455.

Any ideas on how I can accomplish this?

**Note: you'll notice that this list of numbers has a pattern. It is 1/
N*100000000

 

[Ron Coderre]

Was there an issue with the response you received from daddylonglegs in this
post?:
http://www.excelforum.com/showthread.php?p=2062877

Regards,

Ron Coderre
Microsoft MVP (Excel)

 

[Josh Rogers]

Found a solution from another venue. I was trying arrays and things
that were plainly too complicated. This is all I needed:


Assuming your list is in A1:A24 and lookup value in C1 you can use
this formula

=INDEX(A1:A24,MATCH(C1,A1:A24,-1))

 

[Ron Rosenfeld]

I've got a column with a list of ordered values like this:

Code:

100000000
50000000
33333333
25000000
20000000
16666667
14285714
12500000
11111111
10000000
9090909
8333333
7692308
7142857
6666667
6250000
5882353
5555556
5263158
5000000
4761905
4545455
4347826
4166667

And a user enters a value, which could be anything up to the highest
value in this column. I need a method of finding the lowest number in
the column that is larger than the user entered value.

for example, if the user entered 4500000, then the value I'd need
would be 4545455.

Any ideas on how I can accomplish this?

**Note: you'll notice that this list of numbers has a pattern. It is 1/
N*100000000

IF your list of values is in A1:A24; and your user entered value is in D1,
then:

=INDEX($A$1:$A$24,MATCH(D1,$A$1:$A$24,-1))

Or, without a list of values, merely:

=SUMPRODUCT(1/MATCH(D1,1/ROW(INDIRECT("1:24"))*100000000,-1)*100000000)

If you want to solve arithmetically, I believe the following will do that:

=1/FLOOR(1/D1*100000000,1)*100000000

You may need to round or adjust your display to get rid of any unwanted
decimals.
--ron

 

[Ron Rosenfeld]

=1/FLOOR(1/D1*100000000,1)*100000000

Simplified:

=100000000/INT(100000000/D1)


--ron

 

[Rick Rothstein]

Since the OP wants a value that appears in the column, I think you will need
another INT function call...

=INT(100000000/INT(100000000/D1))

 

[Ron Rosenfeld]

Since the OP wants a value that appears in the column, I think you will need
another INT function call...

=INT(100000000/INT(100000000/D1))

You MAY be correct BUT since the OP wrote:

" **Note: you'll notice that this list of numbers has a pattern. It is
1/N*100000000 "

I thought it likely that the display of integers is related to formatting
rather than rounding.
--ron

 

[Rick Rothstein]

Yes, I noted that... it is why I included the words "I think" in my reply.

 

[Bernd P]

Hello,

=LOOKUP(-B1,-A1:A24,A1:A24)

Regards,
Bernd