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 lis...
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.Again - this is why I didn't want to upload the original document.
Use the document attached instead please.
rjtomkinson Provided that you are using Excel for 365 the basic formula for your example would be:
=TAKE(SORT(FILTER(A2:A45,B2:B45=E1)),4)Though note the slight modifications to cells D1 and E1 where the latter now only contains the the school number, thus not "School 13".
Edit:
Added your file with two alternative formulas that should work in older Excel versions.
I did not upload the file initially because I would have been breaching data protection law.
Nope, so long as no names of actual people are involved--and they're not--you're fine.
But I'm on vacation--just checking in--and even if that weren't the case, I doubt I'd have the most efficient solution. So I'm going to invite a few others -- Sergei Baklan Hans Vogelaar Riny_van_Eekelen NikolinoDE Peter Bartholomew -- to chime in. I'm confident that at least one of them will have your solution.
