Forum Discussion
Excel formula for finding, ranking and returning nth value
Can anyone explain what formula or array formula I'd need for the following:
Scenario - school cross country results
Each school may have six runners but only the top four score.
Column A lists finishing position ascending from 1.
Column B lists the school number worn by each runner (all runners from a school wear the same number)
I would like a formula which finds, from Column B, a specific school number, identifies the relative nth position for that school, and returns the overall finishing position from Column A.
e.g. Average School (number 15) students finished 6th, 10th, 12th, 21st, 28th and 30th.
Formula function:
Find 15 in Column B, identify 2nd lowest = 10.
Thank you!
I'm sure there are folks here--including me--who'd like to help. You could help us help you by providing a worksheet. It sounds like you must have one; if not, you could readily create a more complete example than the verbal example you've given (just by entering into Excel the actual results from whatever paper tally sheet you've been using heretofore).
Put that sample worksheet on OneDrive or GoogleDrive and paste a link here that grants access to it.
In the process, please describe just a bit more fully this formula (or the overall outcome) that you're seeking.
I found what you have written to be a bit confusing and we need to be really clear in order to write any formulas.
Here are what I saw as ambiguous (or needing more clarification) in your description.
Each school may have six runners but only the top four score. Top four from each school, right?
Column A lists finishing position ascending from 1. Clear
Column B lists the school number worn by each runner (all runners from a school wear the same number) Clear
I would like a formula which finds, from Column B, a specific school number, identifies the relative nth position for that school, and returns the overall finishing position from Column A. See below
e.g. Average School (number 15) students finished 6th, 10th, 12th, 21st, 28th and 30th. Clear enough
Formula function:
Find 15 in Column B, Clear enough
identify 2nd lowest = 10. Second lowest? Where did that "Second" come from in the first place? I see it could an arbitrary nth, but that's not clear.
Do we need to identify 1st, 2nd, 3rd and 4th for each school?
If not, what connection does "2nd lowest" have with "only the top 4 score"?
How does any of it connect with calculating the "overall finishing position" and some kind of overall score for each school?
I'm sure it's all clear in your mind, and maybe it would be to anybody else who's done this kind of scoring and ranking....but it seems to me that you're taking some of the process for granted (unless I'm just missing something very obvious).
I did not upload the file initially because I would have been breaching data protection law.
Additionally the layout of the document is perhaps not the most intuitive, and I think it requires even more explanation.
Each year group (race category) has a different tab.
The columns on the left are as described - finishing position and school number.
The other table lists the finishing position of the top four runners for each school. I would like a formula which finds that data rather than me manually going through the finishing position data and entering that in to the table.
Yes - I need the 1st, 2nd, 3rd and 4th position runners for each school.
e.g. if School A runners finished 4th, 8th, 19th and 32nd
The 2nd runner (or second lowest number because surely that's the language that the formula would use - Excel does not have that context or meaning) is 8.
As you can see from the table, once I have those positions I can sum and rank the totals for each school.
- Yes - I need the 1st, 2nd, 3rd and 4th position runners for each school.
Which tabel is your expected result according to those raw datas?
Which tables are raw datas?
There are many worksheets and each sheet also includes 3 seperate range/tables.
