** Fun ** NFL Playoff Pool - Outcome predition

[Jimmyray007]

Greetings!

I run a NFL Playoffs bracket pool, and would like to take my spreadsheet up a notch. I'm using nested If statements to calculate each players total points for the bracket. For example:

=SUM((IF(B4=$B$2,7)),...,(IF(L4=$L$2,7)))

Each correct pick (matching text) gives them an assigned point value. Those values are summed up, sortable, easy peasy.



Now, what I'd like to do is calculate who the pool winner will be based on each of the remaining possible outcomes (128 total). In other words, if these 7 teams actually win the games being played, who will have the highest points total?

I could manually enter the 7 correct picks for each outcome, then the next, etc., 128 times, but there must be a smarter way. But, I haven't been able to find another scenario equivalent to it online. I've already added all 128 possible outcomes to my spreadsheet, thinking that I could reference each scenario by row, then reference the cell that has the highest points total since it'd below to the same row as the Participant's name. But, that's where I'm stuck.

Perhaps there's an alternative way of thinking this up.



A sample file is attached. Thanks in advance for any and all assistance.

[daddylonglegs]

Hello Jimmyray,

If you put this formula in V4

=INDEX(A$4:A$9,MATCH(MAX(MMULT((F$4:L$9=O4:U4)*{10,10,10,10,15,15,22},{1;1;1;1;1;1;1})+M$4:M$9),MMULT((F$4:L$9=O4:U4)*{10,10,10,10,15,15,22},{1;1;1;1;1;1;1})+M$4:M$9,0))

confirm with CTRL+SHIFT+ENTER and fill down the column that will give you the winner for each scenario - if there are ties you will just get the first name

Note that if you add more results the formula will stop working...

see attached

[Jimmyray007]

That worked perfectly. I expanded the formula and added more entries, and it worked perfectly. Didn't realize excel could do matrix multiplication. Awesome.

So, with that, is there a way to determine if there is a tie for the Max values, and if so list those as well? Or, indicate that duplicate Max values exist? Then I'd know to process those scenarios manually.