Excell formula

[Dustywm]

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

 

[Anne Troy]

What do you want to calculate, Dusty? For instance, =if(a2>a3,"W","L") could
work, but I have no idea what constitutes a win or loss.
************
Anne Troy
www.OfficeArticles.com

 

[Max]

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

 

[Dustywm]

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

 

[Max]

One way to cover it ..

Try instead in J2: =IF(H2="L",-G2,IF(H2="W",G2*E2/F2,""))

 

[Max]

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

 

[Dustywm]

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

Fantatsic, your genius, Thank Max it works, really grateful.

One further question. Is there away to 'hide' the formula in a saved
worksheet?

 

[Max]

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

 

[Route2391]

I got a lot of my begginer help on this site... cool
They teach you by making a, Time sheet, Invoice and cheek book fro
scratch on Windows media Player very cool

www.MDOTutorials.co