FIFA World Cup 2006 Office Pool

[Sibwhistle]

I'm working on a spreadsheet for the office.

In cells c1, d1, e1, f1, will be the names of the co-workers. (Wil
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 i
Germany vs Costa Rica) in g2 cell I'll input the outcome of the gam
(let say Germany won) in cell h2 will have a formula to compute fo
player 1 based on below.

A player can choose Germany or Costa Rica or a Draw. Guessing the tea
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 o
Drawj. Jokering can double their score. If they joker and get it wron
they lose one point.

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

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

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

Stewar

 

[Bob Phillips]

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)

 

[daddylonglegs]

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

 

[Sibwhistle]

thxs guys... Both methods works.

Stewart