Formula / Football Pool / HELP
 

In Case You Missed My First Post !!!
I'm producing a template for a football pool I am in. It's called a "Margin
Of Victory" pool. Just like most, the person with the most points at the end
of the NFL season wins. It works just like the name. Each person chooses on
NFL team each week. Points are awarded or deducted for the "Margin" of
points the NFL team the person chose won by. "Victory" ex: I chose the
Philadelphia Eagles this week and they beat the Carolina Panthers 28 to 7.
I, for choosing the Eagles, am awarded 21 points. Another person, who had
the Panthers this week would have -21, or 21 points deducted from his/her
running total.
Heres the trick, and my problem for this post. Each person in the pool can
choose the same team from each of the 32 NFL teams once. In other words, my
choice of the Eagles is available to me only once in the 17 week season.
I need a formula that notifies the input person that I can't have the Eagles
this week because I used them in week 10. Surely there is a formula that
recognizes PHI, (the abbreviation for Philadelphia) and notifies the input
person. I was thinking perhaps a "True" or "False" value in the column next
to each persons name.
Can Anyone Help ???

Formula / Football Pool / HELP
 

The formula you are asking for will only work for the current week and not
previous weeks. You are adding either a row or column every week so the
formula has to adjust to the week number

you can use
=if(countif(A5:A20,A20)1,true,false))

A20 is the latest pick and A5:A20 the range of all the picks

to make it adjustable

=if(countif(A5:A20,indirect(address(B2+4,1))1,tru e,false))

where B2 is the week number. Because the range starts at A5, you have to
add four to the week number to get the row number.





"JamesInNeedFootballInDeed" wrote:

In Case You Missed My First Post !!!
I'm producing a template for a football pool I am in. It's called a "Margin
Of Victory" pool. Just like most, the person with the most points at the end
of the NFL season wins. It works just like the name. Each person chooses on
NFL team each week. Points are awarded or deducted for the "Margin" of
points the NFL team the person chose won by. "Victory" ex: I chose the
Philadelphia Eagles this week and they beat the Carolina Panthers 28 to 7.
I, for choosing the Eagles, am awarded 21 points. Another person, who had
the Panthers this week would have -21, or 21 points deducted from his/her
running total.
Heres the trick, and my problem for this post. Each person in the pool can
choose the same team from each of the 32 NFL teams once. In other words, my
choice of the Eagles is available to me only once in the 17 week season.
I need a formula that notifies the input person that I can't have the Eagles
this week because I used them in week 10. Surely there is a formula that
recognizes PHI, (the abbreviation for Philadelphia) and notifies the input
person. I was thinking perhaps a "True" or "False" value in the column next
to each persons name.
Can Anyone Help ???