Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=DATEVALUE | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Date Calculations | Excel Worksheet Functions | |||
how do I make a word typed in a cell go to a specific cell in anot | Excel Discussion (Misc queries) |