If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excell formula
How do I set a Excel fomula for Win/Lose calculation please ie in a racing
betting record, entering W or L in a colunm. Im usuing Excel 2002 |
#2
|
|||
|
|||
What do you want to calculate, Dusty? For instance, =if(a2a3,"W","L") could
work, but I have no idea what constitutes a win or loss. ************ Anne Troy www.OfficeArticles.com "Dustywm" wrote in message ... How do I set a Excel fomula for Win/Lose calculation please ie in a racing betting record, entering W or L in a colunm. Im usuing Excel 2002 |
#3
|
|||
|
|||
Another play could run along these lines ..
Assume you have in A1:B4 where A1:B1 holds the players' names A2:B2, A3:B3 etc holds the scores of each round's play AA BB 50 60 60 50 60 60 etc We could put in C2: =IF(OR(A2="",B2=""),"",VLOOKUP(SIGN(A2-B2),{0,"Draw";1,"AA wins";-1,"BB wins"},2,0)) and copy C2 down, to yield the results, viz.: AA BB 50 60 BB wins 60 50 AA wins 60 60 Draw etc (Usually, there's also a "Draw" situation to cater for g) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Dustywm" wrote in message ... How do I set a Excel fomula for Win/Lose calculation please ie in a racing betting record, entering W or L in a colunm. Im usuing Excel 2002 |
#4
|
|||
|
|||
"Dustywm" wrote: How do I set a Excel formula for Win/Lose calculation please ie in a racing betting record, entering W or L in a colunm. Im usuing Excel 2002 Sorry didnt make my self clear. formula requried :- D2 = Horse. E2/F2 Oddds ie 2/1. G2=Stake. H2="W" or"L" I2=Profit/Loss. J2=Balance. =Sum(G2*E2)/F2 gives Profit loss when H2 is "W" How do i expand this formula to result in minus G2 in J2 when H2 is "L" loss Your help appreciated Thanks |
#5
|
|||
|
|||
One way to cover it ..
Try instead in J2: =IF(H2="L",-G2,IF(H2="W",G2*E2/F2,"")) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Dustywm" wrote in message ... "Dustywm" wrote: How do I set a Excel formula for Win/Lose calculation please ie in a racing betting record, entering W or L in a colunm. Im usuing Excel 2002 Sorry didnt make my self clear. formula requried :- D2 = Horse. E2/F2 Oddds ie 2/1. G2=Stake. H2="W" or"L" I2=Profit/Loss. J2=Balance. =Sum(G2*E2)/F2 gives Profit loss when H2 is "W" How do i expand this formula to result in minus G2 in J2 when H2 is "L" loss Your help appreciated Thanks |
#6
|
|||
|
|||
Try instead in J2: =IF(H2="L",-G2,IF(H2="W",G2*E2/F2,""))
Sorry, the formula above should be in I2, not J2 And to avoid potential downstream calculation problems in col J, think it's better to make the formula return zero if FALSE (instead of it returning blanks: ""), so .. Put in I2: =IF(H2="L",-G2,IF(H2="W",G2*E2/F2,0)) Copy I2 down -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#7
|
|||
|
|||
"Max" wrote: Try instead in J2: =IF(H2="L",-G2,IF(H2="W",G2*E2/F2,"")) Sorry, the formula above should be in I2, not J2 And to avoid potential downstream calculation problems in col J, think it's better to make the formula return zero if FALSE (instead of it returning blanks: ""), so .. Put in I2: =IF(H2="L",-G2,IF(H2="W",G2*E2/F2,0)) Copy I2 down -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 Fantatsic, your genius, Thank Max it works, really grateful. One further question. Is there away to 'hide' the formula in a saved worksheet? -- |
#8
|
|||
|
|||
Glad it worked !
.. Is there away to 'hide' the formula in a saved worksheet? Try this: Press CTRL+A to select the entire sheet Click Format Cells Protection tab Uncheck "Locked" Check "Hidden" Click OK Now just protect the sheet via: Click Tools Protection Protect Sheet Passwrd? OK The above will hide all formulas on the sheet, while leaving the entire sheet unlocked (i.e. unprotected) for normal use -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 --- |
#9
|
|||
|
|||
I got a lot of my begginer help on this site... cool They teach you by making a, Time sheet, Invoice and cheek book from scratch on Windows media Player very cool www.MDOTutorials.com -- Route2391 ------------------------------------------------------------------------ Route2391's Profile: http://www.excelforum.com/member.php...o&userid=26774 View this thread: http://www.excelforum.com/showthread...hreadid=396267 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Creating a check mark box | MarthaSue | Setting up and Configuration | 18 | April 28th, 2005 12:31 AM |
Excell question about formula copying!!! | Excell question about formula copying!!! | Worksheet Functions | 1 | March 3rd, 2005 03:31 AM |
Unable to get formula to work in Excell Please help | Matt | General Discussion | 4 | September 9th, 2004 08:08 PM |
Excell Formula help | TBD | Worksheet Functions | 3 | February 6th, 2004 06:47 PM |
Can someone Decipher this Excell Formula for me ??? | Andy Yeo | Setting up and Configuration | 3 | January 29th, 2004 05:04 PM |