Tricky Excel Formula Required!

[Guest]

I've created a spreadsheet for my upcoming football season and need a formula
created.

It consists of a Master Sheet and 17 weeks. Each week there will be a winner
of $140 cash, under each week I want it to list the top winner by wins if
there’s tie in wins it should go by the tie breaker total closet to the total
without going over is considered the winner and should be rewarded the $140
in cash.

Under week one I have you'll an example.

Under the master I want it the total up all money won over the course of the
17 weeks.

I have added the actual spreadsheet so you get a better idea of what is
required, any help would be appreciated.

http://www.nghl.ca/Football_Spreadsheet.zip

Take Care!

 

[T. Valko]

I would use a helper column to calculate the differential for the tie
breaker.

Enter this formula in AP6 and copy down to AP38:

=IF(AND(AI6=MAX(AI$6:AI$38),COUNTIF(AI$6:AI$38,MAX(AI$6:AI$38))=1),0,IF(AI6=MAX(AI$6:AI$38),IF(AN$40-AN6>=0,AN$40-AN6,""),""))

Then use this formula in AK42 to get the name of the winner:

=IF(COUNT(AP6:AP38),INDEX(B6:B38,MATCH(MIN(AP6:AP38),AP6:AP38,0)),"")

This still leaves a potential problem for a tie breaker. What if multiple
people have the max number of wins and all of their tie breakers are over?
For example:

......Wins......MNT.....Actual Total
.......10..........55...............44
.......10..........60

Biff

 

[T. Valko]

This still leaves a potential problem for a tie breaker. What if multiple

people have the max number of wins and all of their tie breakers are over?

As is, the formula I've suggested ignores *ALL* tie break scores greater
than the actual score if *multiple* people have the max wins.

Another possibilty is a tie with the score differential.

Tie breakers can be a real PITA!

Biff

 

[Guest]

Some thoughts for a simpler, possibly palatable approaches
implemented into your sample:
http://www.savefile.com/files/695379
Football_Pool.xls

For your 1st Q ..

In a typical weekly sheet, say in W1,

Compute max wins
In AI43: =MAX(AI6:AI38)

Count no. of "max wins" winners
In AI44: =COUNTIF(AI6:AI38,AI43)

Now to set-up to extract the "max wins" winner(s) and calc the winnings per
winner automatically

In AP6: =IF(AI6=$AI$43,ROW(A1),"")
Copy AP6 down to AP38

In AK44: =IF(AL44="","",ROW(A1))

In AL44:
=IF(ROW(A1)>COUNT($AP$6:$AP$38),"",INDEX($B$6:$B$38,SMALL($AP$6:$AP$38,ROW(A1))))

In AM44:
=IF(AL44="","",140/SUMPRODUCT(--($AL$44:$AL$49<>"")))

Select AK44:AM44, copy down by say, 5 rows to AM49, presuming that this
suffices to cover the maximum expected number of "max wins" winners.
AK44:AM49 will display all the winner(s) and the equally apportioned prize
winnings depending on whether there are any ties in the max wins (multiple
winners).

Make copies of W1 to produce the other 16 identically structured sheets,
renamed as: W2 to W17

Then as to your 2nd Q ...

Under the master I want it the total up all money
won over the course of the 17 weeks.

In Master,
Put in Z3: =C3
Copy Z3 to AP3

Then place in Z4:
=SUMIF(INDIRECT("'"&Z$3&"'!AL44:AL49"),$B4,INDIRECT("'"&Z$3&"'!AM44:AM49"))
Copy Z4 across to AP4, fill down to AP36 to populate the grid. This picks up
all the winnings per player in col B from each of the 17 sheets W1 ... W17.
Then just use a simple SUM formula in Y4: =SUM(Z29:AP29), with Y4 copied down
to gather the required total winnings per player.

---

 

[T. Valko]

I think you missed the point about the tie breaker. There is only one winner
per week. Although there are still potential problems that I raised in my
other reply.

On the weekly sheets the "Monday Nighter Total" in AN6:AN38 is used as the
tie breaker. This is a prediction of the total points scored in the game.
This is to be compared to the "Actual Total" (actual score) in cell AN40.
This is like an over/under bet on the final score but in this case the OP
wants the closest *without going over*.

As I pointed out in my other reply, what if multiple people have the max
wins and their "over/under's" are all over?

Biff

 

[Guest]

Thank You Biff & Max you both have been a great help...

Biff you're actually right, I never thought about if both teams are over the
over under on the Monday Nigh Game.

I'm guessing this would be the best system:

The closet without going over is awarded the winner.
If both are over then the closet one too the actually over under is
considered the winner.
Can this be done?

Biff is right there's only one winner each week of $140. No matter if both
end with the same amount of wins. There's no split in this pot.

Max I like your idea of breaking down the money based on each week.

Thanks guys for your help.

 

[T. Valko]

Using a helper column to calcuate the tie breaker score differential....

Enter this formula in AP6 and copy down to AP38:

=IF(AI6=MAX(AI$6:AI$38),IF(COUNTIF(AI$6:AI$38,MAX(AI$6:AI$38))=1,0,AN$40-AN6),"")

Enter this array** formula AK42 to get the name of the winner:

=INDEX(B6:B38,IF(COUNT(AP6:AP38)=COUNTIF(AP6:AP38,"<0"),MATCH(MAX(AP6:AP38),AP6:AP38,0),MATCH(MIN(IF(AP6:AP38>=0,AP6:AP38)),AP6:AP38,0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Now, that still leaves one possible problem with ties. If multiple people
have the max wins *AND* multiple people have the same tie break score!

I told ya tie breakers are a PITA!

Biff

 

[T. Valko]

Now, that still leaves one possible problem with ties. If multiple people

have the max wins *AND* multiple people have the same tie break score!

That would mean you'd need to come up with a second tie breaker.

Biff

 

[Guest]

Well the odds of that happening are slim but I see your point.... If it
really came down to that then I guess I would have to split the $140...

Rules:
The winner is the highest wins if there’s a tie in wins then it comes down
to over under, the closest to the over under is considered the winner.
If both are over the actually over the over under then it would be the
closest to the over under.
If both have the same amount of wins and there over under are the same the
$140 would be split pot along those people.

Again thank you for you’re assistant in this…

Rob

 

[T. Valko]

Just another thing to consider....

I run a (NFL) pool myself. I'll bet you never thought of games ending in
ties!!!!

Although very rare, it's still a possibility that can cause all kinds of
problems if you didn't design for that possibility.

Pick the Steelers!

Good luck!

Biff

 

[Guest]

Thanks Biuff your help

Using a helper column to calcuate the tie breaker score differential....

Enter this formula in AP6 and copy down to AP38:

=IF(AI6=MAX(AI$6:AI$38),IF(COUNTIF(AI$6:AI$38,MAX(AI$6:AI$38))=1,0,AN$40-AN6),"")

Enter this array** formula AK42 to get the name of the winner:

=INDEX(B6:B38,IF(COUNT(AP6:AP38)=COUNTIF(AP6:AP38,"<0"),MATCH(MAX(AP6:AP38),AP6:AP38,0),MATCH(MIN(IF(AP6:AP38>=0,AP6:AP38)),AP6:AP38,0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Now, that still leaves one possible problem with ties. If multiple people
have the max wins *AND* multiple people have the same tie break score!

I told ya tie breakers are a PITA!

Biff