 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
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 somethingion.
Or, same effect as [b]mossimus[/b] 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! 🙂