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


--
Sibwhistle
------------------------------------------------------------------------
Sibwhistle's Profile: http://www.excelforum.com/member.php...o&userid=28295
View this thread: http://www.excelforum.com/showthread...hreadid=535181

FIFA World Cup 2006 Office Pool
 

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" wrote
in message ...

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

FIFA World Cup 2006 Office Pool
 

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))),"")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=535181

FIFA World Cup 2006 Office Pool
 

thxs guys... Both methods works.

Stewart


--
Sibwhistle
------------------------------------------------------------------------
Sibwhistle's Profile: http://www.excelforum.com/member.php...o&userid=28295
View this thread: http://www.excelforum.com/showthread...hreadid=535181