Fellas….. ( and ladies ) ….

I help run a championship where riders score points at various rounds over the years but only the best X number count.

So for example there may be 30 races over a year, with the best 20 being what the championship is calculated on.

do we have any spreadsheet guru types who could work out a formula that could be used to automatically calculate a total from the highest 20 scores ( and disregard the rest ) …. I have an example spreadsheet which I can email to anyone who could help? )

Its currently being done manually which is very time consuming!

Please pm if you are able to help and I can email you an example of what I need to try and work out the formula for!

I could take a look, email me over a copy of the spreadsheet martin.somerfield (at) gmail (dot) com

=SUM(LARGE(A1:Z1,ROW(INDIRECT((“1:20”)))))

Replace A1:Z1 with the range containing the 30 races.

Note this is an array formula so once entered you will need to save it by using Ctrl-Shift and Enter

A quick way would be to have a second row/colum for each competitor and then use something like…

=IF(RANK(“score cell”,”range of cells with all scores”)<=20,”score cell”,0)

This will return the score for the round if the cell in the highest 20 scores and 0 if its not. Then sum this row/column to get the total score for these 20 rounds.

EDIT: too slow, mossimus is there already, although I’d do it with a new column (or row), just so you can see the top 20 results before the summation.

Or, same effect as mossimus but easier to understand:
assuming your data is in columns A to AY, from row 2 down, put the following in AZ2 (with the middle bit filled in!) and drag it down the table. Look up LARGE if in doubt.

=LARGE(A2:AY2,1)+LARGE(A2:AY3,2)+LARGE(A2:AY3,3)+…+LARGE(A2:AY3,20)

Thanks fellas…. i accidentally left my laptop at the office so can’t have a play … but will do so tomorrow……. fingers crossed STWexcel gurus will rescue me! 🙂