Menu

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

Search this Thread

Prev

Previous Post

Next Post

Next

#4

Report Post

Posted to microsoft.public.excel.programming

Ron Rosenfeld Ron Rosenfeld is offline

Ron Rosenfeld is offline

external usenet poster

Posts: 5,651

Default

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

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

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Find the lowest number in a given column	smartin	Excel Worksheet Functions	8	June 28th 09 08:01 PM
Find Lowest Column Among Several	Dana DeLouis[_3_]	Excel Programming	0	September 18th 04 06:47 AM
Find Lowest Column Among Several	Myrna Larson	Excel Programming	0	September 18th 04 12:35 AM
Find Lowest Column Among Several	Tom Ogilvy	Excel Programming	0	September 17th 04 08:47 PM
Find Lowest Column Among Several	MDW	Excel Programming	0	September 17th 04 08:01 PM

All times are GMT +1. The time now is 09:36 AM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement