Daily Dose of Excel

Well, the college football season is quickly coming to a close. With no hockey this year, I don’t know what I’ll do until next August. Watch baseball? Not likely. In the few remaining days, I’ll be watching a lot of bowl games. Not necessarily because I like the match ups, but because I’m in a pool. The pool is arranged thusly:

Everyone gets -10 points for entering. Entrants pick 22 bowl games by the spread. Every loss is -10 points. The top 10 entrants get a percentage of the total points back at the end based on their position. Also, the person with the least points (the biggest loser) gets 1% of the total points at the end. Confusing? Here’s the spreadsheet

To keep track of what’s happening, I’ve added these columns

The formulas are as follows

$AA$8 =SUM((B8:W8=$B$5:$W$5)*1)
$AB$8 =IF($X$5=”",0,ABS(X8-$X$5))
$AC$8 =(RANK(AA8,$AA$8:$AA$651)*1000)+RANK(AB8,$AB$8:$AB$651,1)
$AD$8 =RANK(AC8,$AC$8:$AC$651,1)
$AE$8 =IF(AD8=MAX($AD$8:$AD$651),VLOOKUP(11,percent,2,FALSE),IF(AD8>10,0,VLOOKUP(AD8,percent,2,FALSE)))
$AF$8 =(AE8*($AG$653))-AG8

AA8 - an array formula comparing that entrants picks to the winners.
AB8 - If there’s a tie, the entrant has to predict the combined score of the championship game. If the championship game hasn’t been played, X5 will be blank and this column will be “uninitialized”. Otherwise it will show the tie breaker.
AC8 - This creates a ranking based on the two parameters, most wins and the tie breaker. The first rank is multiplied by 1,000 to keep it separate from the second rank. If there were more than 1,000 entrants, this would have to be made 10,000.
AD8 - This ranks the AC8 ranking to give the actual position of the entrant.
AE8 - This figures the percentage of the points won. The first part, AD8=MAX, tests to see if this person is the biggest loser. If so, the “11″ column is taken from the percent table. If it’s greater than 10, then they get zero, otherwise it gets the appropriate percentage from the table (shown below).
AF8 - Takes the percentage won times the total pot and subtracts AG8. AG8 is the number of losses for that entrant times 10 points.

The percent table is shown below:

AG655 has this formula which is filled down to AG664

=(SUM(OFFSET(AH655,0,0,IF(COUNTIF($AD$8:$AD$651,AF655)>(10-AF655), 11-AF655,COUNTIF($AD$8:$AD$651,AF655)),1)))/COUNTIF($AD$8:$AD$651,AF655)

Oh boy, that formula took a long time to get right. This computes the percentage won by rank. Since there is currently and 8-way tie for 1st, it adds up the percent won for the first 8 spots and divides it by the number of winners. There are at least two people tied for ninth, so positions 2-8 and 10 have no percentage.

The 11th spot uses this formula

=AH665/COUNTIF($AD$8:$AD$651,MAX(AD8:AD651))

which divides the 1% that the big loser wins by however many big losers there are.

You can look at the whole spreadsheet at Download Bash05Blog.zip.