How can I create a formula that can identify a cell after the data has been sorted?

[Bermudez]

Good morning everyone, again I come seeking answers....


In the attached file I have a series of competitors, sorted by class with there class position and associated points on the far right. You will notice in the Class Position column there are 7 competitors that did not finish the race... (DNF) This was calculated by creating a formula that sits in the P column that identifies any value (Time) that was Less than the winner's total time, (C23). This value is not the lowest value but rather the value of the the person who came in first as noted by column B.

My problem is this table gets rearranged after being imported so I can not give the formula in the P column a specific cell address.

Is there some way create a formula that would look for the competitor who is first overall (B23) and then use the cell that is adjacent (C23) to calculate anyone who did not finish. (a Value Less the C23)


As always thank you

For forum.xls

[shg]

Please take a few minutes to re-read the forum rules, and then amend your thread title accordingly.

Thanks.

[Bermudez]

Sorry.... I cant figure out how to edit the thread name...

[shg]

That too is explained in the forum rules ...

[shg]

Thank you.

How about copying the winning time to a separate cell outside the table and reference that?

It seems odd to me that "DNF" needs to be calculated rather than manually entered. What if a DNF concidentally had the same time as the winner?