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: www.excelforum.com/member.php...oamp;userid=28295
View this thread: www.excelforum.com/showthread...hreadid=535181This seems to work
=IF(LEFT(C2,LEN($G2))=$G2,(IF($G2=quot;Drawquot;,5,3))*(IF (RIGHT(C2)=quot;jquot;,2,1)),IF(C2
=quot;Drawjquot;,-1,0))
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
quot;Sibwhistlequot; gt; wrote
in message ...
gt;
gt; I'm working on a spreadsheet for the office.
gt;
gt; In cells c1, d1, e1, f1, will be the names of the co-workers. (Will
gt; use four players as an example, Last pool had around 25-30 people).
gt;
gt; In cells c2, d2, e2, f2 will be their choice of team (first game is
gt; Germany vs Costa Rica) in g2 cell I'll input the outcome of the game
gt; (let say Germany won) in cell h2 will have a formula to compute for
gt; player 1 based on below.
gt;
gt; A player can choose Germany or Costa Rica or a Draw. Guessing the team
gt; correctly will earn 3 points and a draw correctly will earn 5 points.
gt; They can joker their pick then it'll look like Germanyj, Costa Ricaj or
gt; Drawj. Jokering can double their score. If they joker and get it wrong
gt; they lose one point.
gt;
gt; In the example below: Player 1 will earn 3, player 2 will earn 6, p3
gt; will earn 0 and p4 will lose 1 point.
gt;
gt; Player 1 Player 2 Player 3 Player 4 Game outcome P1 P2 P3
gt; P4
gt; Germany Germanyj Draw Drawj Germany 3 6 0
gt; (1)
gt;
gt; I'm having a difficult time making an IF statement. Is the IF
gt; statement the only formula to use? or use something else?
gt;
gt; Stewart
gt;
gt;
gt; --
gt; Sibwhistle
gt; ------------------------------------------------------------------------
gt; Sibwhistle's Profile:
www.excelforum.com/member.php...oamp;userid=28295
gt; View this thread: www.excelforum.com/showthread...hreadid=535181
gt;
Hi Stewart,
try this formula in H2 copied across and down as necessary
=IF(C2lt;gt;quot;quot;,IF(C2=$G2,IF(C2=quot;drawquot;,5,3),IF($G2amp;quot;jquot;= C2,IF(C2=quot;drawjquot;,10,6),IF(RIGHT(C2)=quot;jquot;,-1,0))),quot;quot;)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=535181
thxs guys... Both methods works.
Stewart--
Sibwhistle
------------------------------------------------------------------------
Sibwhistle's Profile: www.excelforum.com/member.php...oamp;userid=28295
View this thread: www.excelforum.com/showthread...hreadid=535181
