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 1^st, 2^nd, 3^rd and 4^th 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

Making a long series of dates in Excel without manually typing each day out is quite simple. The easiest way is to type out the first 2 dates that you want in the series, highlight both of them, then drag the small box-handler at the bottom-right down. Excel will automatically fill the subsequent cells below the first 2 cells until the point where you release the mouse button.

But what if you want to fill up the series with only workdays?

Method 1: Fill

Select the first 2 workdates and drag the box-handler down. In the small box that appears, click it and choose: Fill Weekdays.

Method 2: WORKDAY() Formula

Input =workday(A1,1) as a formula and Excel will add +1 workday to the day specified in cell A1. If you change the digit in the 2nd argument to 2, then Excel will add +2 workdays etc. Issue with this method is that you could in theory, get the same day listed twice in the series, per the example above.

Method 3: IF() Formula

Using a slightly more complex formula, construct an =IF() statement to compute the day of the week. In this case, the formula =IF(WEEKDAY(K2)=5,K2+3,K2+1) determines if the date above is a Friday, or weekday=6, and adds 3 to the date. Else, it will add 1 day.

Method 4: SUM()

This is perhaps the coolest method I’ve seen yet! It’s a lot more flexible than Method 3 above, as you can add/subtract various days to the series depending on what day of the week it is. To break it down:

=WEEKDAY(A2) will first determine what day of the week it is. Excel will return a number 1-7 with Monday=1 and Sunday=7.

=CHOOSE(WEEKDAY(A2),1,1,1,1,3) will then return the numbers 1 or 3 depending on the day of the week. Notice that we do not provide more than 5 choices for =CHOOSE(). We don’t need to because for our weekday only series, we never get to Saturday (weekday=6) or Sunday (weekday=7). It is only a Friday when we need to add 3 days to get the series back to Monday i.e. weekday=1.

=SUM(A2,1 or 3) just adds the variable from the above bits to the date in A2.

Note: =weekday(a,b) actually has 2 arguments: b is optional. Argument b can be used to determine the starting day of the week whereby Monday can then hold the values 0, 1 or 2.

***

PS: This how-to was done on Excel 2007 with default Malaysia regional settings where Monday = 1st day of the week.