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

"Dustywm" <[email protected]> wrote in message
news:[email protected]...
> 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

[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>)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Dustywm" <[email protected]> wrote in message
news:[email protected]...
> 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

[Dustywm]

"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

[Max]

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" <[email protected]> wrote in message
news:[email protected]...
>
>
> "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

[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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Dustywm]

"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?
> --
>
>
>

[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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
---