Seeking spreadsheet formula to calculate the sum of Top 7 scores for each player

[TD_Host]

In our league, a player's best 7 scores/performances are used to calculate his standing on The Leaderboard.

I'm using a google spreadsheet but I think the formula in excel would be the same.

Can someone assist me with the formula necessary to calculate the SUM of a player's top 7 scores?

I've included a screen shot of what our spreadsheet looks like. Each event will be listed to the right with a 'rank' & 'points' column.  


The 'Best 7' column is where the formula with go.

[TD_Host]

I should perhaps clarify: We're going to add more events/columns to the right (rank/points for each event) as we play more tournaments. From those columns, I will have to perform the 'top 7' calculations.

[Martini]

I would do a Google search for the terms "excel formula sum top values" and that will turn up many links which show you how to do what you are looking for.

[TD_Host]

I would do a Google search for the terms "excel formula sum top values" and that will turn up many links which show you how to do what you are looking for.

Thanks, Martini. From a google search, I work it out (for excel) as:

=SUM(LARGE(the_data_range,{1,2,3,4,5,6,7}))

Now I just need to test it in google spreadsheets (for which the syntax looks very similar).

Sometimes I start out a little slow and need a nudge

[TD_Host]

I came up with the following for google spreadsheets:

=SUM(LARGE((E3,G3,I3,K3,M3,O3,Q3,S3,U3,W3,Y3),{1,2,3,4,5,6,7}))


Which gave me an ERROR! LOL...

So, I tried

=SUM(LARGE(E3:U3,{1,2,3,4,5,6,7}))

Which gave me another error: "Argument out of range: 2"

Maybe it can't take an array like that...oh, well, back to the drawing board!

[Chip Gate]

Good question.  I as well wanted to find out how to get a sum for so many top points so I came here to find out how.  Well you started the ball rolling and I think I find out what your problem is.  the formula  =SUM(LARGE(H6:P6,{1,2,3,4,5,6,7})) should work for you, it works for me. The one thing that i found out about this formula is that it is an array formula and you have to press Ctrl+Shift+Enter instead of Enter after you type in the formula. 

After i did that I went back and looked at the formula and it put brackets on both ends of the formula {=SUM(LARGE(H2:P2,{1,2,3,4,5,6,7}))} 

Try Ctrl+Shift+Enter if you haven't and if doesn't work try putting brackets on both ends of the formula.

Good luck and thanks for the question it helped answer mine.

[TD_Host]

Good question.  I as well wanted to find out how to get a sum for so many top points so I came here to find out how.  Well you started the ball rolling and I think I find out what your problem is.  the formula  =SUM(LARGE(H6:P6,{1,2,3,4,5,6,7})) should work for you, it works for me. The one thing that i found out about this formula is that it is an array formula and you have to press Ctrl+Shift+Enter instead of Enter after you type in the formula. 

After i did that I went back and looked at the formula and it put brackets on both ends of the formula {=SUM(LARGE(H2:P2,{1,2,3,4,5,6,7}))} 

Try Ctrl+Shift+Enter if you haven't and if doesn't work try putting brackets on both ends of the formula.

Good luck and thanks for the question it helped answer mine.

Thanks. I tried Ctr-Shift-Enter (in google spreadsheet) and instead of adding brackets, it resulted in this format/change:

=ArrayFormula(SUM(LARGE(E3:U3,{1,2,3,4,5,6,7})))

..and this error: "Argument out of range: 2"

Of course, I have absolutely NO IDEA what that means

[Martini]

If you look up the list of Google spreadsheet functions (https://docs.google.com/support/bin/static.py?page=table.cs&topic=25273) it looks like LARGE does not support multiple values. I would try making seven different cells and put each individual rank in each cell then sum up the seven different cells instead.

[William]

Why not keep things simple?

=LARGE(E3:U3,1)+LARGE(E3:U3,2)+LARGE(E3:U3,3)+LARGE(E3:U3,4)+LARGE(E3:U3,5)+LARGE(E3:U3,6)+LARGE(E3:U3,7)

[TD_Host]

If you look up the list of Google spreadsheet functions (https://docs.google.com/support/bin/static.py?page=table.cs&topic=25273) it looks like LARGE does not support multiple values. I would try making seven different cells and put each individual rank in each cell then sum up the seven different cells instead.

Not sure what you mean, exactly.

For example, I tried the following (with just one value for the 2nd argument after the specified cell data range) with no joy:

 =LARGE(E3:U3,1)+LARGE(E3:U3,2)

=(LARGE(E3:U3,1))+(LARGE(E3:U3,2))

They both returned "Argument out of range: 2"

Is something wrong with my syntax, or am I not understanding something?

Maybe this works with excel but google spreadsheets is giving me a headache!

[Martini]

Why not keep things simple?

=LARGE(E3:U3,1)+LARGE(E3:U3,2)+LARGE(E3:U3,3)+LARGE(E3:U3,4)+LARGE(E3:U3,5)+LARGE(E3:U3,6)+LARGE(E3:U3,7)

I like this approach too and probably would have gotten to this answer eventually. But when things aren't working I like to break things down into the simplest formulas to help with debugging.

[Martini]

If you look up the list of Google spreadsheet functions (https://docs.google.com/support/bin/static.py?page=table.cs&topic=25273) it looks like LARGE does not support multiple values. I would try making seven different cells and put each individual rank in each cell then sum up the seven different cells instead.

Not sure what you mean, exactly.

For example, I tried the following (with just one value for the 2nd argument after the specified cell data range) with no joy:

 =LARGE(E3:U3,1)+LARGE(E3:U3,2)

=(LARGE(E3:U3,1))+(LARGE(E3:U3,2))

They both returned "Argument out of range: 2"

Is something wrong with my syntax, or am I not understanding something?

Maybe this works with excel but google spreadsheets is giving me a headache!

I don't know why you are getting that error. How about a link to your spreadsheet? And what data do you have in the E3:U3 range? Do all cells have valid data in them?

[TD_Host]

This does not produce an error:

=LARGE(E3:U3,1)


But this returns the error previously mentioned:


=LARGE(E3:U3,1)+LARGE(E3:U3,2)


It does not like the addition of multiple LARGE functions in the equation. Maybe it is not possible in google spreadsheets? Or, maybe my syntax is wrong.

[William]

You can't really go wrong with that syntax.

Try using only =LARGE(E3:U3,2) to see what happens then. If that works you should be able to add them up.

If it doesn't maybe there's a problem with the 2nd largest value.

[TD_Host]

You can't really go wrong with that syntax.

Try using only =LARGE(E3:U3,2) to see what happens then. If that works you should be able to add them up.

If it doesn't maybe there's a problem with the 2nd largest value.

The formula works - except it is in fact breaking as a result of the fact that there is a lack of data still in the subsequent cells. I didn't understand how the data could 'break' the formula !!

That's the problem. It doesn't like the fact there is no 2nd value/data (yet) in the row. The new scores are not in yet for the new league season.

How can I tell the formula to DISREGARD missing values after the first, second, etc? I'd like to plug in the formula and forget it.

[TD_Host]

It looks like all I have to do is plug in '0' for values for those cells for which we don't yet have scores; then the error goes away. Sheesh...what a lot of trouble because the formula can't handle cells with no data in them yet.

Now I just have to figure out how to get google to allow me to have columns past U...it seems to just stop there...

[Martini]

In my test spreadsheet blank cells are allowed. No idea why it isn't working in yours.

Have you considered using a site that manages leagues for you?

If you want to add more columns try googling for "google spreadsheet add columns" and you'll find instructions to put in more columns.

[TD_Host]

In my test spreadsheet blank cells are allowed. No idea why it isn't working in yours.

Have you considered using a site that manages leagues for you?

If you want to add more columns try googling for "google spreadsheet add columns" and you'll find instructions to put in more columns.

Martini: Are you using Excel? In my google spreadsheet, I had to put 0's in for blank cells to stop the error.

I'd consider a league site to manage the stats,etc, but most that I've seen require my players/users to create an account, etc., and we already have a 'group site' (similar to meetup) which handles everything (except league stats, leaderboard, etc). It was hard enough getting most of them to that site and signed up (some still aren't and won't).

[Martini]

In my test spreadsheet blank cells are allowed. No idea why it isn't working in yours.

Have you considered using a site that manages leagues for you?

If you want to add more columns try googling for "google spreadsheet add columns" and you'll find instructions to put in more columns.

Martini: Are you using Excel? In my google spreadsheet, I had to put 0's in for blank cells to stop the error.

I'd consider a league site to manage the stats,etc, but most that I've seen require my players/users to create an account, etc., and we already have a 'group site' (similar to meetup) which handles everything (except league stats, leaderboard, etc). It was hard enough getting most of them to that site and signed up (some still aren't and won't).

I am not using Excel. I'm in Google spreadsheet as well. I'm using Chrome browser though I don't think that would matter. I think I see the issue though because I had holes in sample data which worked until I had fewer data points than being added up. If you want your spreadsheet to not have an error value for blank cells then one way to handle it is to put in conditionals in your formula. I think just seeding all cells with zeroes will be the much easier route to go.

[TD_Host]

I just got this formula to work:

=SUM( LARGE({E3,G3,I3,K3,M3,O3,Q3,S3,U3,W3,Y3},{1,2,3,4,5,6,7}) )  

I think I know why I couldn't get it to work before - I didn't use the {} brackets. The LARGE does indeed take multiple values! It was a syntax mistake on my part (documentation on this stuff is pretty poor, so I couldn't figure it out right away.

This is a MUCH simpler formula to my mind (easier to write, anyway).

[William]

Well found out

I tried it in Excel, but the syntax there is slightly different:

=SUM( LARGE((E3,G3,I3,K3,M3,O3,Q3,S3,U3,W3,Y3),{1,2,3,4,5,6,7}) )