Location of the first greater than cell

9214 views
Skip to first unread message

crazyquasar

unread,
Jan 18, 2010, 1:22:01 AM1/18/10
to
I have a huge unsorted list of numbers (more than 100,000). I want to locate
the first instance of a number greater than the first number in this list.
any help will be greatly appreciated.

thank you,

crazyquasar

unread,
Jan 18, 2010, 2:24:01 AM1/18/10
to
T Valko's answer in "locate first value greater than" solves the issue. His
answer is

"Another one:

=INDEX(B1:F1,MATCH(TRUE,INDEX(B2:F2>0,0),0))

Format as Date

--
Biff
Microsoft Excel MVP"

Nikki Scheerer

unread,
Sep 14, 2010, 9:49:56 AM9/14/10
to
I am looking to do the same thing, except instead of returning a value I would like excel to tell me the cell location of the first value greater than a certain value.

In my case I have cells A2:ZY2 full of data, and I am looking to find the first cell that has a greater value than the value in cell B6.

So far I have entered:

=INDEX(JA2:ZY2,MATCH(TRUE,INDEX(JA2:ZY2>B6,0),0))

but like I said this is returning a value when what I am looking for is a cell location.

Any help would be appreciated!

Thank you!

> On Monday, January 18, 2010 1:22 AM crazyquasar wrote:

> I have a huge unsorted list of numbers (more than 100,000). I want to locate
> the first instance of a number greater than the first number in this list.
> any help will be greatly appreciated.
>
> thank you,


>> On Monday, January 18, 2010 2:24 AM crazyquasar wrote:

>> T Valko's answer in "locate first value greater than" solves the issue. His
>> answer is
>>
>> "Another one:
>>
>> =INDEX(B1:F1,MATCH(TRUE,INDEX(B2:F2>0,0),0))
>>
>> Format as Date
>>
>> --
>> Biff
>> Microsoft Excel MVP"
>>
>>
>> "crazyquasar" wrote:


>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>> MongoDb vs SQL Server Basic Speed Tests
>> http://www.eggheadcafe.com/tutorials/aspnet/6f573869-c8eb-40c3-9946-2f61e0163966/mongodb-vs-sql-server-basic-speed-tests.aspx

Pete_UK

unread,
Sep 14, 2010, 10:17:06 AM9/14/10
to
If you reduce the formula to this:

=MATCH(TRUE,INDEX(JA2:ZY2>B6,0),0)

it will return the (relative) column number, but as your range begins
with column A then it is also the absolute column number. Try wrapping
an ADDRESS function around this (check it out in Excel Help).

Hope this helps.

Pete

> >>http://www.eggheadcafe.com/tutorials/aspnet/6f573869-c8eb-40c3-9946-2...- Hide quoted text -
>
> - Show quoted text -

Todd Parker

unread,
Sep 1, 2021, 6:24:44 PM9/1/21
to


Just letting you know it is now 9/1/2021 this post from 2010 was EXTREMELY useful. Thx tons!!!!! Dig the spreadsheet genius's out there.

Graeme Erickson

unread,
Apr 2, 2022, 1:51:13 PMApr 2
to
On Wednesday, September 1, 2021 at 3:24:44 PM UTC-7, Todd Parker wrote:
> Just letting you know it is now 9/1/2021 this post from 2010 was EXTREMELY useful. Thx tons!!!!! Dig the spreadsheet genius's out there.

Hello from 2022. Just helped me, too. Thanks!
Reply all
Reply to author
Forward
0 new messages