Find the First Instance of a Number

**Phillycheese5** · 03-17-2008, 02:51 PM

Hi,
I have a number, let's say 100, and i'm trying to find the row of the first instance in column B that is greater or equal to that number.
Suggestions?
Thanks,
Phillycheese

**Ron Coderre** · 03-17-2008, 03:18 PM

Try this:

Please Login or Register  to view this content.

Adjust range references to suit your situation.

Is that something you can work with?

**Phillycheese5** · 03-17-2008, 03:56 PM

The formula tells me the count of how many cells down in the range the data point is, but I'm not sure how to modify the formula to have it give me the row() of the data point.

**Ron Coderre** · 03-17-2008, 04:46 PM

Well, if your data range doesn't begin in Row_1,
then....perhaps something like this:

Please Login or Register  to view this content.

Does that help?

**Phillycheese5** · 03-17-2008, 05:01 PM

I realized that the parenthesis I put into my message didn't work. I'm looking to get the actual row, as in cell reference row, not the number of rows relative to a particular cell. Sorry if I didn't explain it correctly.

What I'm ultimately trying to accomplish is this: I have daily returns for 1 year and I'm showing the top 25 worst drawdowns, as well as how many days it takes to recover. I need this to help me get the recovery period.

Thanks again.

**Ron Coderre** · 03-17-2008, 06:25 PM

I believe the last formula I posted *does* return the row number of the matching item. Are you getting different results?

**Phillycheese5** · 03-18-2008, 10:24 AM

Ron,
The formula you provided does in fact work. There was some operator failure on this end.
Thanks,
Phillycheese

**Ron Coderre** · 03-18-2008, 10:59 AM

I'm glad you got the formula to work.

Find the First Instance of a Number

LinkBack

Thread Tools

Rate This Thread

Display