Large function with criteria

Hi, looking for a little help here.

I need to keep track of points awarded throughout the year so I have set up a table that automatically ranks the individuals name, total points and weekly points in the order of highest numerical in accordance with their total score using the large function. All this works as the primary ordering function and I am happy with this however I would like it to add a secondary order to be based on the highest score within any particular week. i.e. Ben finishes on 437 points with a high of 32 points coming in week 6, Dave also finishes on 437 with a high of 26 points coming on week 12 and Gordon finishes on 437 with a high of 36 points coming on week 15.

I would like it to return this:

Gordon 437 weekly individual scores from 1 – 20
Ben 437 weekly individual scores from 1 – 20
Dave 437 weekly individual scores from 1 – 20

However, as names have been put in alphabetically, it orders the table as Ben, Dave and Gordon.

Is there a way to make this happen.

The large function I use is =LARGE($D$4:$D$13,ROWS($1:1))

I can supply the spreadsheet if it makes it easier to understand my query.

Thanks,
EMcK

The spreadsheet would definitely be helpful, especially if you show the data in the CURRENT form and how you'd like it to look AFTER.

Hi,

Thanks for the response. File attached still using the example I describe above.

Why did you pick the weeks that you did for the tie-breaker? You picked week 5 (30 pts) for Ben in Table 1 and then week 6 (32 pts) in Table 2. For Dave, you picked week 12 (26 pts) when his top score was in week 18 (28 pts).

Sorry, when I wrote the first post I did it quickly and didn't realise Dave's top score was 28 rather than 26 as I had previously noted. I then made a mistake when highlighting the top scores in cells (trying to do two things at once), the top scores for each player is as you've correctly noted above - Ben week 6 (32pts), Dave week 18 (28pts) and Gordon week 15 (36pts). I've attached a revised spreadsheet that reflects this.

Thanks.

Yes I would think that should be ok, I assume I would be able to hide the column if needs be.

Thanks nfsales, that's great. I appreciate the very quick response here.

I've recently noticed an issue with the way the formula is set up in table 1. It only appears to re-order scores if their maximum weekly scores differ.
I have now updated the scores that Craig was previously given, where he now has a total of 437 and also has a maximum score of 32 points in week 20. As Craig's second highest score is 31 on week 16 compared to Ben's second highest score of 30 in week 18 I would like the leaderboard to order Craig above Ben in table 1 now as his second highest score is 31 compared to Ben's 30. However the formula doesn't recognise Craig's score (assume it defaults back to alphabetical as both are on a total of 437) and inserts Ben's scores twice.
Can this be fixed and can the formula look at the 5 highest scores to order prioritise the final order in the leaderboard table as I've shown in table 2. It is feasible that different individuals will end up with the same number of points over the 20 weeks however I think 5 top scores will be enough to separate them should more than one person finish on the same total number of points.

I've attached the spreadsheet to show the issue I have discovered, thoughts how to fix this would be appreciated.

Thanks,
EMcK

That fixed it, thanks. Again I really appreciate the quick response.

Last question (for just now anyway!) is there a way to change the formula (I assume in the helper column in the points 2014 tab) to order the overall position of an individual on their 5 top scores rather their top 3 as it is just now when the total points are the same?

Thanks, I tried that before posting but realise I made a mistake when I did so, therefore it didn't work.

Again thank you very much for all your help and very quick response, its much appreciated.