Re: Find the first lowest value in a column that exceeds $foo
- From: Ron Rosenfeld <ronrosenfeld@xxxxxxxxxx>
- Date: Sat, 21 Mar 2009 21:44:28 -0400
On Sat, 21 Mar 2009 17:54:50 -0700 (PDT), 2008@xxxxxxxxx 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
.
- Follow-Ups:
- Re: Find the first lowest value in a column that exceeds $foo
- From: Ron Rosenfeld
- Re: Find the first lowest value in a column that exceeds $foo
- Prev by Date: Re: Find the first lowest value in a column that exceeds $foo
- Next by Date: Re: Find the first lowest value in a column that exceeds $foo
- Previous by thread: Re: Find the first lowest value in a column that exceeds $foo
- Next by thread: Re: Find the first lowest value in a column that exceeds $foo
- Index(es):
Relevant Pages
|
