Tl’dr I’d like to calculate the maximum possible points left for a player in the NCAA pool without using Power Query or iterative LAMBDA/LET functions for (most of) the pool players who are on Excel 2013 or 2016. I’ve gotten as far as coming up with a solution proposal (I think) using matrices, but my MMULT() wizardry days are long in the past. Hopefully someone has a simpler solution ready to go!
A bit late for this year, but my participants have asked…
Our pool runs on assigning points to a team (10-1 only, but could be higher) and you get those points every time a team wins.
At the start we can identify what games each team will play. If we assign game #’s 1-32 to the first round, 33-48 to the 2nd etc., we can say that starting each round count at the West Region, Gonzaga and Georgia State would have an expected game path of 01_33_49_57_61_63. Ending in the South region Villanova and Delaware would have game paths of 32_48_56_60_62_63.
I assume it’s possible to construct a (virtual?) matrix of the games and identify who the player picked. So each player would have a matrix of teams and games along the lines…
This is the generic matrix, so depending on the points assigned to each team by each player, or the points-per-round value, the “1” above would be changed to the actual points. The matrix is sparse for the earlier games and full for the later ones, since we’re just entering the paths for now. So giving Gonzaga 2 points would show 2’s across that top row. The MAX() value in each column/game will be the number of points potentially earned by the team.
So the question becomes when constructing the virtual matrix how we can set row values to zero when a team has been knocked out (e.g. # wins < the current round) and column values to zero when a game has been played, so that only future games and remaining teams would be in the matrix. Gonzaga's row would be set to zero after the 2nd round (thanks Arkansas!). (Or the matrix can be shrunk to only those teams and games remaining, so if I only have 3 picks left in the Elite 8 I would have just 3 rows, and only those columns representing those games my teams will play in. But that seems harder than flipping a value in the matrix to be zero?)
I also think using MAX is sufficient without having to figure out if I have 2 teams in a game and one of them gets knocked out, and thus having to set their future games to zero, since the MAX value simply carries forward to the next game and we’re assuming my team always wins a game that it’s in for the maximum remaining points.
This solution can be done by physically creating a matrix for each player on a separate tab and having the cell rules reflect the logic above, but I’m wondering if it can be done virtually through a formula. Excel real estate is cheap, but a formula saves a lot of copy/paste formatting each year.
A bit late for this year, but my participants have asked…
Our pool runs on assigning points to a team (10-1 only, but could be higher) and you get those points every time a team wins.
At the start we can identify what games each team will play. If we assign game #’s 1-32 to the first round, 33-48 to the 2nd etc., we can say that starting each round count at the West Region, Gonzaga and Georgia State would have an expected game path of 01_33_49_57_61_63. Ending in the South region Villanova and Delaware would have game paths of 32_48_56_60_62_63.
I assume it’s possible to construct a (virtual?) matrix of the games and identify who the player picked. So each player would have a matrix of teams and games along the lines…
| Game 1 | … | Game 33 | … | Game 49 | … | Game 57 | … | |
| Gonzaga | 1 | 1 | 1 | 1 | ||||
| Georgia State | 1 | 1 | 1 | 1 | ||||
| Boise State | 0 | 1 | 1 | 1 | ||||
| Memphis | 0 | 1 | 1 | 1 | ||||
| … | ||||||||
| Duke | 0 | 0 | 0 | 1 | ||||
| etc |
This is the generic matrix, so depending on the points assigned to each team by each player, or the points-per-round value, the “1” above would be changed to the actual points. The matrix is sparse for the earlier games and full for the later ones, since we’re just entering the paths for now. So giving Gonzaga 2 points would show 2’s across that top row. The MAX() value in each column/game will be the number of points potentially earned by the team.
So the question becomes when constructing the virtual matrix how we can set row values to zero when a team has been knocked out (e.g. # wins < the current round) and column values to zero when a game has been played, so that only future games and remaining teams would be in the matrix. Gonzaga's row would be set to zero after the 2nd round (thanks Arkansas!). (Or the matrix can be shrunk to only those teams and games remaining, so if I only have 3 picks left in the Elite 8 I would have just 3 rows, and only those columns representing those games my teams will play in. But that seems harder than flipping a value in the matrix to be zero?)
I also think using MAX is sufficient without having to figure out if I have 2 teams in a game and one of them gets knocked out, and thus having to set their future games to zero, since the MAX value simply carries forward to the next game and we’re assuming my team always wins a game that it’s in for the maximum remaining points.
This solution can be done by physically creating a matrix for each player on a separate tab and having the cell rules reflect the logic above, but I’m wondering if it can be done virtually through a formula. Excel real estate is cheap, but a formula saves a lot of copy/paste formatting each year.
