# Calculating NCAA pool score



## macfuller (Dec 17, 2022)

I'm getting ready for March Madness with an inherited spreadsheet but I'm struggling to come up with the right formula for getting a score based on points per round, as well as being able to compute the maximum remaining points.

Here's a sample of the table from last year.  Keeping it simple for now, Boise State and Gonzaga have played a couple of games.  Player 1 picked Boise St. to go 2 rounds and Gonzaga to go 5 rounds (e.g. losing in the final).  Based on the points per round the player has earned 10 points for Boise State's first-round win and 30 points for Gonzaga's round 1 and round 2 wins for a total of 40, which is what I would like to have appear in the Player Points row.
Player 1 also will have a maximum of 290 points remaining (total points for rounds 3-5) with Gonzaga and 0 for Boise St. since they lost in the 2nd round.  (This does not reflect the actual tournament!)

2022 MCC NCAA.xlsxABCDEFG12022 Player Picks2Points Per RoundScoringRoundPoints311042205340641007515086300910Player Points#VALUE!111213TeamsRounds WonPlayer 1Player 2Player 3Player 414Akron (13)015Alabama (6)016Arizona (1)017Arkansas (4)018Auburn (2)019Baylor (1)020Boise State (8)1221Chattanooga (13)022Colgate (14)023Colorado St (6)024CSU Fullerton (15)025Davidson (10)026Delaware (15)027Duke (2)028Georgia State (16)029Gonzaga (1)2530Houston (5)031Illinois (4)032Iowa (5)033Iowa State (11)034Jacksonville State (15)035Kansas (1)036Kentucky (2)0PPR Player Picks

I can get the points for an individual player and row such as Gonzaga for Player 1 with a formula along the lines of
=SUMPRODUCT(($F$3:$F$8) * (($E$3:$E$8) <= MIN($C29,D29)))
and similarly for the remaining points with
=SUMPRODUCT($F$3:$F$8, ($E$3:$E$8 > $C29) * ($E$3:$E$8 <= D29))

But since the round score array and the team arrays are different sizes I'm having trouble figuring out a single formula, rather than having a helper column for each player.  Thanks for the help!


----------



## DRSteele (Dec 17, 2022)

I think I've developed a formula that counts the Player Points.

It requires the addition of the Cumulative column to the points tableau. I added some dummy data as well. Specifically, there are times a team went a round or more that no player bet on. There are times a player bet more rounds than the team achieved. There are times the player bet the same number of rounds the team achieved. And there are time a player bet fewer rounds than the team achieved. There are times where no player bet and the team won nuttin. I think that covers it.

MrExcel posts19.xlsxABCDEFG12022 Player Picks2Points Per RoundScoringRoundPointsCumulative Points311010422030534060641001407515025086300450910Player Points4080260460111213TeamsRounds WonPlayer 1Player 2Player 3Player 414Akron (13)635615Alabama (6)316Arizona (1)017Arkansas (4)018Auburn (2)019Baylor (1)020Boise State (8)1231621Chattanooga (13)022Colgate (14)023Colorado St (6)024CSU Fullerton (15)225Davidson (10)026Delaware (15)027Duke (2)028Georgia State (16)029Gonzaga (1)25130Houston (5)031Illinois (4)032Iowa (5)033Iowa State (11)034Jacksonville State (15)035Kansas (1)036Kentucky (2)0Sheet36Cell FormulasRangeFormulaA1A1=YEAR(TODAY()) & " Player Picks"G3G3=F3G4:G8G4=F4+F3D10:G10D10=SUM(XLOOKUP(FILTER(BYROW(CHOOSECOLS($C$14:$G$36,1,COLUMNS($C$10:D10)),LAMBDA(a,MIN(IF(a="",0,a)))),BYROW(CHOOSECOLS($C$14:$G$36,1,COLUMNS($C$10:D10)),LAMBDA(a,MIN(IF(a="",0,a))))>0),$E$3:$E$8,$G$3:$G$8))


----------



## macfuller (Dec 17, 2022)

DRSteele said:


> I think I've developed a formula that counts the Player Points.
> 
> It requires the addition of the Cumulative column to the points tableau. I added some dummy data as well. Specifically, there are times a team went a round or more that no player bet on. There are times a player bet more rounds than the team achieved. There are times the player bet the same number of rounds the team achieved. And there are time a player bet fewer rounds than the team achieved. There are times where no player bet and the team won nuttin. I think that covers it.
> 
> MrExcel posts19.xlsxABCDEFG12022 Player Picks2Points Per RoundScoringRoundPointsCumulative Points311010422030534060641001407515025086300450910Player Points4080260460111213TeamsRounds WonPlayer 1Player 2Player 3Player 414Akron (13)635615Alabama (6)316Arizona (1)017Arkansas (4)018Auburn (2)019Baylor (1)020Boise State (8)1231621Chattanooga (13)022Colgate (14)023Colorado St (6)024CSU Fullerton (15)225Davidson (10)026Delaware (15)027Duke (2)028Georgia State (16)029Gonzaga (1)25130Houston (5)031Illinois (4)032Iowa (5)033Iowa State (11)034Jacksonville State (15)035Kansas (1)036Kentucky (2)0Sheet36Cell FormulasRangeFormulaA1A1=YEAR(TODAY()) & " Player Picks"G3G3=F3G4:G8G4=F4+F3D10:G10D10=SUM(XLOOKUP(FILTER(BYROW(CHOOSECOLS($C$14:$G$36,1,COLUMNS($C$10:D10)),LAMBDA(a,MIN(IF(a="",0,a)))),BYROW(CHOOSECOLS($C$14:$G$36,1,COLUMNS($C$10:D10)),LAMBDA(a,MIN(IF(a="",0,a))))>0),$E$3:$E$8,$G$3:$G$8))



Interesting...

That helped a lot, but I think I've got something a bit shorter that doesn't need a new column.  I'm also not sure about the Player 3 total in your sample.  If Akron has 6 wins and Player 3 had them for 5 rounds, that's 320 points right there?  I think your cumulative total might be off if not the formula.

I couldn't figure out the BYROW without an extra column, but I think the MAP() function works?  People have been running pools since Dan Bricklin and VisiCalc, so I thought there might be a non-LAMBDA solution 


```
=SUM(
    MAP(
             tblPPRPicks[Rounds Won], tblPPRPicks[Player 1]+0,
                  LAMBDA(a,b,SUMPRODUCT(
                                        (RndPoints) * ((RndName) <= MIN(a,b))
                                                            )
                                  )
            )
         )
```

Where I've named the table tblPPRPicks.  I added the +0 to the Player column to make sure any blank cells were interpreted as numeric, in case another user doesn't bother to enter zeros for the first-round losers.

The Max Remaining Points required a new column to indicate whether a team had crashed out or just hadn't reported.  So Boise State was showing they'd won the first round, but how does the formula know they've lost and shouldn't try to calculate any future points for them?


```
=SUM(
      MAP(tblPPRPicks[Rounds Won], tblPPRPicks[Still Playing], tblPPRPicks[Player 1],
          LAMBDA(a,b,c,
             SUMPRODUCT(
             RndPoints, (RndName > a) * ((RndName <= c) * b)
                                      )
                        )
      )
)
```

Here's the table with the new column.

2022 MCC NCAA.xlsxBCDEF10Player Points40011Max Remaining Pts29001213TeamsRounds WonStill PlayingPlayer 1Player 214Akron (13)0TRUE15Alabama (6)0TRUE16Arizona (1)0TRUE17Arkansas (4)0TRUE18Auburn (2)0TRUE19Baylor (1)0TRUE20Boise State (8)1FALSE221Chattanooga (13)0TRUE22Colgate (14)0TRUE23Colorado St (6)0TRUE24CSU Fullerton (15)0TRUE25Davidson (10)0TRUE26Delaware (15)0TRUE27Duke (2)0TRUE28Georgia State (16)0FALSE29Gonzaga (1)2TRUE530Houston (5)0TRUEPPR Player Picks


----------



## DRSteele (Dec 19, 2022)

Player 3 got 250 for 5 of Akron's 6 rounds and 10 for Boise's 1 rounds - that's 260.


----------

