Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the first lowest value in a column that exceeds $foo
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the first lowest value in a column that exceeds $foo
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) wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the first lowest value in a column that exceeds $foo
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)) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the first lowest value in a column that exceeds $foo
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the first lowest value in a column that exceeds $foo
On Sat, 21 Mar 2009 21:44:28 -0400, Ron Rosenfeld
wrote: =1/FLOOR(1/D1*100000000,1)*100000000 Simplified: =100000000/INT(100000000/D1) --ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the first lowest value in a column that exceeds $foo
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)) -- Rick (MVP - Excel) "Ron Rosenfeld" wrote in message ... On Sat, 21 Mar 2009 21:44:28 -0400, Ron Rosenfeld wrote: =1/FLOOR(1/D1*100000000,1)*100000000 Simplified: =100000000/INT(100000000/D1) --ron |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the first lowest value in a column that exceeds $foo
On Sun, 22 Mar 2009 00:40:37 -0400, "Rick Rothstein"
wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the first lowest value in a column that exceeds $foo
Yes, I noted that... it is why I included the words "I think" in my reply.
-- Rick (MVP - Excel) "Ron Rosenfeld" wrote in message ... On Sun, 22 Mar 2009 00:40:37 -0400, "Rick Rothstein" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the first lowest value in a column that exceeds $foo
Hello,
=LOOKUP(-B1,-A1:A24,A1:A24) Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find the lowest number in a given column | Excel Worksheet Functions | |||
Find Lowest Column Among Several | Excel Programming | |||
Find Lowest Column Among Several | Excel Programming | |||
Find Lowest Column Among Several | Excel Programming | |||
Find Lowest Column Among Several | Excel Programming |