Calculating NCAA pool score

[macfuller]

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.xlsx
	A	B	C	D	E	F	G
1	2022 Player Picks
2	Points Per Round			Scoring	Round	Points
3					1	10
4					2	20
5					3	40
6					4	100
7					5	150
8					6	300
9
10			Player Points	#VALUE!
11
12
13		Teams	Rounds Won	Player 1	Player 2	Player 3	Player 4
14		Akron (13)	0
15		Alabama (6)	0
16		Arizona (1)	0
17		Arkansas (4)	0
18		Auburn (2)	0
19		Baylor (1)	0
20		Boise State (8)	1	2
21		Chattanooga (13)	0
22		Colgate (14)	0
23		Colorado St (6)	0
24		CSU Fullerton (15)	0
25		Davidson (10)	0
26		Delaware (15)	0
27		Duke (2)	0
28		Georgia State (16)	0
29		Gonzaga (1)	2	5
30		Houston (5)	0
31		Illinois (4)	0
32		Iowa (5)	0
33		Iowa State (11)	0
34		Jacksonville State (15)	0
35		Kansas (1)	0
36		Kentucky (2)	0
PPR 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]

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.xlsx
	A	B	C	D	E	F	G
1	2022 Player Picks
2	Points Per Round			Scoring	Round	Points	Cumulative Points
3					1	10	10
4					2	20	30
5					3	40	60
6					4	100	140
7					5	150	250
8					6	300	450
9
10			Player Points	40	80	260	460
11
12
13		Teams	Rounds Won	Player 1	Player 2	Player 3	Player 4
14		Akron (13)	6		3	5	6
15		Alabama (6)	3
16		Arizona (1)	0
17		Arkansas (4)	0
18		Auburn (2)	0
19		Baylor (1)	0
20		Boise State (8)	1	2	3	1	6
21		Chattanooga (13)	0
22		Colgate (14)	0
23		Colorado St (6)	0
24		CSU Fullerton (15)	2
25		Davidson (10)	0
26		Delaware (15)	0
27		Duke (2)	0
28		Georgia State (16)	0
29		Gonzaga (1)	2	5	1
30		Houston (5)	0
31		Illinois (4)	0
32		Iowa (5)	0
33		Iowa State (11)	0
34		Jacksonville State (15)	0
35		Kansas (1)	0
36		Kentucky (2)	0
Sheet36

Cell Formulas
Range	Formula
A1	A1	=YEAR(TODAY()) & " Player Picks"
G3	G3	=F3
G4:G8	G4	=F4+F3
D10:G10	D10	=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]

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.xlsx
	A	B	C	D	E	F	G
1	2022 Player Picks
2	Points Per Round			Scoring	Round	Points	Cumulative Points
3					1	10	10
4					2	20	30
5					3	40	60
6					4	100	140
7					5	150	250
8					6	300	450
9
10			Player Points	40	80	260	460
11
12
13		Teams	Rounds Won	Player 1	Player 2	Player 3	Player 4
14		Akron (13)	6		3	5	6
15		Alabama (6)	3
16		Arizona (1)	0
17		Arkansas (4)	0
18		Auburn (2)	0
19		Baylor (1)	0
20		Boise State (8)	1	2	3	1	6
21		Chattanooga (13)	0
22		Colgate (14)	0
23		Colorado St (6)	0
24		CSU Fullerton (15)	2
25		Davidson (10)	0
26		Delaware (15)	0
27		Duke (2)	0
28		Georgia State (16)	0
29		Gonzaga (1)	2	5	1
30		Houston (5)	0
31		Illinois (4)	0
32		Iowa (5)	0
33		Iowa State (11)	0
34		Jacksonville State (15)	0
35		Kansas (1)	0
36		Kentucky (2)	0
Sheet36

Cell Formulas
Range	Formula
A1	A1	=YEAR(TODAY()) & " Player Picks"
G3	G3	=F3
G4:G8	G4	=F4+F3
D10:G10	D10	=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.xlsx
	B	C	D	E	F
10		Player Points		40	0
11		Max Remaining Pts		290	0
12
13	Teams	Rounds Won	Still Playing	Player 1	Player 2
14	Akron (13)	0	TRUE
15	Alabama (6)	0	TRUE
16	Arizona (1)	0	TRUE
17	Arkansas (4)	0	TRUE
18	Auburn (2)	0	TRUE
19	Baylor (1)	0	TRUE
20	Boise State (8)	1	FALSE	2
21	Chattanooga (13)	0	TRUE
22	Colgate (14)	0	TRUE
23	Colorado St (6)	0	TRUE
24	CSU Fullerton (15)	0	TRUE
25	Davidson (10)	0	TRUE
26	Delaware (15)	0	TRUE
27	Duke (2)	0	TRUE
28	Georgia State (16)	0	FALSE
29	Gonzaga (1)	2	TRUE	5
30	Houston (5)	0	TRUE
PPR Player Picks

 

[DRSteele]

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