Ok, change the following formulas on the Weekly sheets.
On row 43, cell C43:
=IF(COUNT(C3,D40:D41)<3,"",IF(AND(C3=0,D40=D41),"T ie",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
"Killer" wrote in message
...
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" wrote:
I must have missed that. I'll take a look.
Biff
"Killer" wrote in message
...
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" wrote:
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
"Killer" wrote in message
...
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" wrote:
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
"Killer" wrote in message
...
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
"Killer" wrote:
HEy Biff
Here's the link you have requested.
http://www.nghl.net/Downloads/Football_Pool.zip
Thanks
"T. Valko" wrote:
If you can post a link to your file (like you did last time)
I'll
fix
ya up!
Biff
"Killer" wrote in message
...
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!