ExcelBanter - View Single Post - Find the first lowest value in a column that exceeds $foo

Ron Rosenfeld

On Sat, 21 Mar 2009 17:54:50 -0700 (PDT), wrote:

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