Formula Result

[Guest]

Hey All.

I run an football poll and I'm trying to get away from manually putting
checkmarks beside the players who select the correct team and was wondering
if it's possible to have this done automatically.

Here's spreadsheet layout example:

A : B C : D
2 Favorite Min
3 Spread 10
4 Underdog Buf

Player:
B C D
6 Pam Minn
7 Sam Buff √
8 John Buff √
9 Steve Minn

A : B C D
40 Score Buff 20
41 Minn 23

So the formula should be if the favorite either ties or beats the spread the
team who took the favorite should receive a checkmark in column D

If the favorite doesn’t cover the spread the player who selects the underdog
should receive a checkmark in column D

The person who didn't select the correct team column D should be left blank.

Thanks!

 

[T. Valko]

If you can post a link to your file (like you did last time) I'll fix ya up!

Biff

 

[Guest]

HEy Biff

Here's the link you have requested.

http://www.nghl.net/Downloads/Football_Pool.zip

Thanks

 

[Guest]

Sorry One last request, I have added an area to keep track of Favorite wins
and Doggie wins can a formula be created around this as well.

You'll see an example under week 1 I promise this will be the last request
from me.

Time to start reading or start paying your for your time.

Thanks

 

[T. Valko]

No problem.

I would recommend some changes in some of your formulas. Your file is
already "big" and you don't even have any data in it yet. If you kept things
the way they are by the time you reached week 17 this file would be almost
unusable. I'm going to replace all of those formulas on the Master sheet
(not efficient).

Give me a couple of days.

Biff

 

[Guest]

I have no idea that this could become a problem but you are know what you
talking about so please take your time. I look forward to this and also
learning from your work.

Thanks Again Biff

 

[T. Valko]

Here's your file:

http://cjoint.com/?fohSgkgqO3

I made some significant changes to most of the formulas on the Master sheet.
I deleted that Notes sheet.

On the W1 sheet...

I put formulas in the 2 columns that had sample data in them. On row 43 I
put in a formla that calculates the winner of the game (in the first 2
columns). Note that this formula will account for ties (although they are
very rare but if one happens and you didn't account for it, it could really
mess things up!).

This is important: when you put the teams and scores in rows 40:41, put them
in the same order as you have them in rows 2 and 4.

From a design standpoint, merged cells are a real PITA and usually cause
nothing but trouble. I think you'd be better off keeping the teams, the
scores, the line and the winner all in one area instead of having some of
this info at the top of the sheet and some of it at the bottom of the sheet.

Your file was originally 694kb. It is now 360kb. I reset the used range on
each sheet and deleted all of the unnecessary formatting, got rid of all
those volatile formulas on the Master sheet.

Biff

 

[Guest]

Hey Biff

Thanks again for your help there is one little problem I see in the outcome
if the favourite ties the spread. Right now you have the favourite gets the
win but actually I wanted so both teams involved would get the win seeing the
favourite didn’t actually beat the spread but tied it.


Take Care!

Rob

 

[Guest]

Sorry I missed one other thing as well the bottom right corner of the
spreadsheet that totals up the winners by favourite and underdogs so I can
keep track of this throughout the season.

Take Care!

 

[T. Valko]

Here's what you said. I added the ** for emphasis:

So the formula should be if the favorite either *ties* or beats
the spread the team who took the favorite should receive
a checkmark in column D

Let me take a look at it and I'll get back to you.

Biff

 

[T. Valko]

I must have missed that. I'll take a look.

Biff

Sorry I missed one other thing as well the bottom right corner of the
spreadsheet that totals up the winners by favourite and underdogs so I can
keep track of this throughout the season.

Take Care!

 

[Guest]

You’re right that is my mistake..... Sorry

The favorite must either tie or beat the spread to receive a win.
The underdog will receive a win if the favorite ties or doesn’t cover the
spread.

 

[T. Valko]

Ok, change the following formulas on the Weekly sheets.

On row 43, cell C43:

=IF(COUNT(C3,D4041)<3,"",IF(AND(C3=0,D40=D41),"Tie",IF(D40-C3=D41,"Push",IF(D40-C3>=D41,C40,C41))))

Copy across as needed.

If the spread is a tie *both* teams get credit for a win. Where I live we
call that a "push".

In cell D6:

=IF(C6="","",IF(OR(C6=C$43,C$43="Push"),"?",""))

Copy across and down as needed.

Now:

Sorry I missed one other thing as well the bottom right corner of the
spreadsheet that totals up the winners by favourite and underdogs so I can
keep track of this throughout the season.

I don't see where you have that. Are you talking about that being on the
Weekly sheet?

You want the total count (for the week) of the number of favs and dogs?

Biff

 

[T. Valko]

P.S.

For those games where the spread is a "pick" (no spread) you'll need to
enter the spread as 0.

Biff

 

[Guest]

I don't see where you have that. Are you talking about that being on the
Weekly sheet?

You want the total count (for the week) of the number of favs and dogs?

Biff
_____________________________________

Yes on weekly sheets I want to know how many Fav or dogs won for that week.

Thanks again Biff

 

[T. Valko]

Ok...

You'll need to move the formulas in row 43 down a couple of rows. (or
somewhere else) I just noticed how the table is not square at the bottom
right hand side. Let's assume you move the formulas in row 43 to row 45.

To count the favs:

=SUMPRODUCT(--(C2:AH2<>""),--(C2:AH2=C45:AH45))

To count the dogs:

=SUMPRODUCT(--(C4:AH4<>""),--(C4:AH4=C45:AH45))

To count the pushes:

=COUNTIF(C45:AH45,"push")

Design tip: formatting should be the very last thing you do!

Betting tip: always take the underdog! If you took every underdog in every
game at the end of the year you will be ahead.

Biff

 

[Guest]

Hey Biff I also notice that as well and had already made the correction.
The formulas worked prefect thank you for all your help towards this.

I'll keep that in mind next time I create something on making sure the
layout is completed first before formatting it.

As for your tip on who to take select, the season just past you’re totally
correct dogs won more then the favs and the fact the NFL teams are evenly
you're totally right that the dog are the better choice, but hey that would
be to simple to just select dogs each week now wouldn’t it….

Thanks again for your help and good luck in your upcoming season.

 

[T. Valko]

Good luck! Thanks for the feedback.

Biff

Hey Biff I also notice that as well and had already made the correction.
The formulas worked prefect thank you for all your help towards this.

I'll keep that in mind next time I create something on making sure the
layout is completed first before formatting it.

As for your tip on who to take select, the season just past you're totally
correct dogs won more then the favs and the fact the NFL teams are evenly
you're totally right that the dog are the better choice, but hey that
would
be to simple to just select dogs each week now wouldn't it..

Thanks again for your help and good luck in your upcoming season.