FIFA World Cup 2006 Office Pool

I'm working on a spreadsheet for the office.

In cells c1, d1, e1, f1, will be the names of the co-workers. (Will use four players as an example, Last pool had around 25-30 people).

In cells c2, d2, e2, f2 will be their choice of team (first game is Germany vs Costa Rica) in g2 cell I'll input the outcome of the game (let say Germany won) in cell h2 will have a formula to compute for player 1 based on below.

A player can choose Germany or Costa Rica or a Draw. Guessing the team correctly will earn 3 points and a draw correctly will earn 5 points. They can joker their pick then it'll look like Germanyj, Costa Ricaj or Drawj. Jokering can double their score. If they joker and get it wrong they lose one point.

In the example below: Player 1 will earn 3, player 2 will earn 6, p3 will earn 0 and p4 will lose 1 point.

Player 1 Player 2 Player 3 Player 4 Game outcome P1 P2 P3 P4
Germany Germanyj Draw Drawj Germany 3 6 0 (1)

I'm having a difficult time making an IF statement. Is the IF statement the only formula to use? or use something else?

Stewart

This seems to work

=IF(LEFT(C2,LEN($G2))=$G2,(IF($G2="Draw",5,3))*(IF(RIGHT(C2)="j",2,1)),IF(C2
="Drawj",-1,0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sibwhistle" <[email protected]> wrote
in message news:[email protected]...
>
> I'm working on a spreadsheet for the office.
>
> In cells c1, d1, e1, f1, will be the names of the co-workers. (Will
> use four players as an example, Last pool had around 25-30 people).
>
> In cells c2, d2, e2, f2 will be their choice of team (first game is
> Germany vs Costa Rica) in g2 cell I'll input the outcome of the game
> (let say Germany won) in cell h2 will have a formula to compute for
> player 1 based on below.
>
> A player can choose Germany or Costa Rica or a Draw. Guessing the team
> correctly will earn 3 points and a draw correctly will earn 5 points.
> They can joker their pick then it'll look like Germanyj, Costa Ricaj or
> Drawj. Jokering can double their score. If they joker and get it wrong
> they lose one point.
>
> In the example below: Player 1 will earn 3, player 2 will earn 6, p3
> will earn 0 and p4 will lose 1 point.
>
> Player 1 Player 2 Player 3 Player 4 Game outcome P1 P2 P3
> P4
> Germany Germanyj Draw Drawj Germany 3 6 0
> (1)
>
> I'm having a difficult time making an IF statement. Is the IF
> statement the only formula to use? or use something else?
>
> Stewart
>
>
> --
> Sibwhistle
> ------------------------------------------------------------------------
> Sibwhistle's Profile:
http://www.excelforum.com/member.php...o&userid=28295
> View this thread: http://www.excelforum.com/showthread...hreadid=535181
>

Hi Stewart,

try this formula in H2 copied across and down as necessary

=IF(C2<>"",IF(C2=$G2,IF(C2="draw",5,3),IF($G2&"j"=C2,IF(C2="drawj",10,6),IF(RIGHT(C2)="j",-1,0))),"")

thxs guys... Both methods works.

Stewart