HELP: Finding Person's Highest 6 Scores from Range

[Tommyslats]

Hi,

I need help trying to figure out the best way to achieve what I want.

I have a spreadsheet with two sheets.
The first sheet is a summary page, with names of people and a column that ultimately should TOTAL the person's highest 6 scores inside a 2 year period.

The second sheet is where all the scores are kept.

Each tournament has 3 columns. Place, Name, Score.

I need the forumla in the summary sheet, to identify the name of the competitor, then look into the scores sheet and find and then total the highest 6 scores attributed to that player.

[AliGW]

Your dataset isn't really big enough to be clear. Are the event results always listed in rank order? The layout of your data is really not optimal for this kind of thing.

[Tommyslats]

Yes the results are always listed in rank order. 1st to last.

Unfortunately I cannot think of a better way to display the data.
Each event the competitors might change. So we need to list competitors and their results for each event.

[PFDave]

I'm unable to download your sample spreadsheet but I've attached an example workbook which might help point you into the right direction.

C1 is the name you're looking for, D1 is the sum of top 6 results, A1:B32 is the data range

[PFDave]

Yes the results are always listed in rank order. 1st to last.

Unfortunately I cannot think of a better way to display the data.
Each event the competitors might change. So we need to list competitors and their results for each event.

This sounds like you don't have this in a usual table.

You should only need a Table with the fields for:

Event
Competitor
Score
Rank

[AliGW]

In the original file, Dave's formula would be this:

=SUMPRODUCT(LARGE(IF(Scores!$B$2:$AC$3=A2,Scores!$C$2:$AD$3),{1,2,3,4,5,6}))

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Is that giving the results you want?

[AliGW]

Dave - the layout is like this and so on to Event 10:

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I
1	Event 1			Event 2			Event 3
2	1st	JOHN SMITH	41	1st	JOHN SMITH	30	1st	JOHN SMITH	22
3	2nd	SMITH BROWN	40	2nd	SMITH BROWN	29	2nd	SMITH BROWN	21

Sheet: Scores

The summary table is like this:

Excel 2016 (Windows) 32 bit

	A	B	C
1	Angler Name	Best 6	Ranking
2	JOHN SMITH		1
3	SMITH BROWN		2

Sheet: Rankings

[PFDave]

In the original file, Dave's formula would be this:

=SUMPRODUCT(LARGE(IF(Scores!$B$2:$AC$3=A2,Scores!$C$2:$AD$3),{1,2,3,4,5,6}))

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.



Is that giving the results you want?

Thanks Ali

[AliGW]

It's a useless layout, really - see post #7.

[Tommyslats]

In the original file, Dave's formula would be this:

=SUMPRODUCT(LARGE(IF(Scores!$B$2:$AC$3=A2,Scores!$C$2:$AD$3),{1,2,3,4,5,6}))

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Is that giving the results you want?

That works halfway.

It gives me the best 6 results.
But I need to duplicate the forumla for each competitor, and when I copy the formula down to Smith Brown it tallies exactly the same count.

The formula needs to identify which scores belong to which competitor

[AliGW]

It doesn't do that here, Tommy, when entered correctly. You are entering it with ENTER, not C+S+E (and it gives the wrong result). Please follow the instructions below the formula in my post that you have quoted above.

[AliGW]

Are we there yet?

[Tommyslats]

Yes I think so, Thank you

EDIT: I'm getting a NUM error

[AliGW]

In that case, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

PS If you are considering awarding rep. points, then you need to click on one of Dave's posts as this was his solution, not mine.

[PFDave]

I'm getting a NUM error

Can you upload your problem file and I'll see if I can download this one?

[Tommyslats]

Can you upload your problem file and I'll see if I can download this one?

This is the actual file

[AliGW]

I'm getting a NUM error

Tommy - the forum rules state that you should NOT edit a post after it has received a response. Please do not do so again, as it makes the thread nonsesnsical. Thanks.

[Tommyslats]

Tommy - the forum rules state that you should NOT edit a post after it has received a response. Please do not do so again, as it makes the thread nonsesnsical. Thanks.

Sorry, I thought I had it
Edit: I hadn't refreshed so you're reply hadn't come through. Sorry again.

[AliGW]

There are errors in the formula, which should be this:

=SUMPRODUCT(LARGE(IF(Scores!$B$2:$BA$60=A2,Scores!$C$2:$BB$60),{1,2,3,4,5,6}))

entered with C+S+E.

HOWEVER, you will get a NUM error where a competitor has fewer than six scores.

[PFDave]

This is the actual file

You will receive this error where you don't have 6 score to sum as your TOP 6.

What would you like to do for those with less than 6? Your Total Sum would not then be comparable to the best 6 really if you just sum the total of their scores.

[Tommyslats]

There are errors in the formula, which should be this:

=SUMPRODUCT(LARGE(IF(Scores!$B$2:$BA$60=A2,Scores!$C$2:$BB$60),{1,2,3,4,5,6}))

entered with C+S+E.

HOWEVER, you will get a NUM error where a competitor has fewer than six scores.

Is there a way to display the total of the scores if a competitor has less than six?

[Tommyslats]

You will receive this error where you don't have 6 score to sum as your TOP 6.

What would you like to do for those with less than 6? Your Total Sum would not then be comparable to the best 6 really if you just sum the total of their scores.

Just a total of their scores would suffice, even if they only have 2 or 4 scores for example

[AliGW]

Try this:

=IF(COUNTIF(Scores!$B$2:$BA$60,A2)<6,SUMPRODUCT(--(Scores!$B$2:$BA$60=A2),(Scores!$C$2:$BB$60)),SUMPRODUCT(LARGE(IF(Scores!$B$2:$BA$60=A2,Scores!$C$2:$BB$60),{1,2,3,4,5,6})))

C+S+E.

[PFDave]

=IFERROR(IF(COUNTIF(Scores!$A$2:$BB$60,A2)>5,SUMPRODUCT(LARGE(IF(Scores!$B$2:$BA$60=A2,Scores!$C$2:$BB$60),{1,2,3,4,5,6})),SUM(IF(Scores!$A$2:$BB$60=A2,Scores!$B$2:$BB$60))),0)

Entered with CTRL SHIFT ENTER and will provide 0 score if there are no scores to sum at all

[Tommyslats]

=IFERROR(IF(COUNTIF(Scores!$A$2:$BB$60,A2)>5,SUMPRODUCT(LARGE(IF(Scores!$B$2:$BA$60=A2,Scores!$C$2:$BB$60),{1,2,3,4,5,6})),SUM(IF(Scores!$A$2:$BB$60=A2,Scores!$B$2:$BB$60))),0)

Entered with CTRL SHIFT ENTER and will provide 0 score if there are no scores to sum at all

Perfect!

You're a legend mate thanks very much

[AliGW]

You should find that both solutions work.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[davsth]

As the only numbers are those which wish to be counted the following could be added on the rankings tab cell b2 =LARGE(Scores!A2:AD2,1)+LARGE(Scores!A2:AD2,2)+LARGE(Scores!A2:AD2,3)+LARGE(Scores!A2:AD2,4)+LARGE(Scores!A2:AD2,5)+LARGE(Scores!A2:AD2,6)

assuming the order of anglers remains the same on both sheets. its a bad layout tho I agree with Ali

[AliGW]

What is your formula meant to be used for?