• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding Row Number

LAMcCub

New Member
I have a series of data in the first column (DATA 1) that moves to a maximum value before decaying towards zero. I need to find the row number of a particular value to perform calculations.
When locating the maximum data point, I can readily find the row number as shown in Column D by finding the point where the Data is equal to the maximum and then returning the row # suing the MAX function.

In column A, I have used an IF statement to locate the row number where DATA 1 reaches a value of 2. As the remaining data points are greater than 2, I can use the maximum of the column to return my row number.

My problem can be seen in Column C. In this instance, I am looking for the point where the data reaches nearest 2.6 BEFORE the data moves to its maximum. In this case row 977 is the one I need. However, because of the decay, values at and below 2.6 return again after the maximum is reached (row 2335 and up). So I can't use the maximum of column C to return my row number.

Thanks for the help solving!
 

Attachments

  • Sample Book1.xlsx
    191.3 KB · Views: 4
=MATCH(2.6,$A$1:$A$3009)
will give you the answer but I wouldn't trust it.
This is more complex but more reliable:
=XMATCH(2.6,A1:INDEX(A1:A3009,XMATCH(D1,A1:A3009)),-1)
In the middle of that formula you have
XMATCH(D1,A1:A3009)
which finds the row of the max value.
 
Back
Top