My high-school batch has a football league going on the official Fantasy Football website. This year, the organizers proposed assigning points to teams representing our respective sports houses with bragging rights at the end of the season. Now, while the official league does a pretty good job at keeping track of individual scores, it doesn’t have a way to group teams into “groups” so I came up with this Excel spreadsheet.
We decided to assign 10/7/5/3 points to 1st, 2nd, 3rd and 4th placed houses each game week, with each house’s score each week being the average score of the top 3 performers in that house. At the end of the season, the house with the most points wins!
My initial thought process led me to think that the solution to this problem would be a combination of average() and rank(). The deliverable being, “return the average score of the top 3 teams in house X”. Turns out, rank() isn’t required at all! The solution was as follows, and must be entered using Shift+Control+Enter as it is an array formula:
If you’d like to toy around with the formulas, the excel spreadsheet is here: week1.xlsx