Spreadsheet Formula Help

[Jonny2621]

Having purchased Keiths Lay Em System I am now using the spreadsheet he provided to record my other W2W banks. Much better than my rubbishy record keeping.

However Id also like to record a few fun win bets of my own. I want to convert the formula that works out the profit on a lay to fixed stake minus commission to one that works out the profit / loss on a winning bet

Can anyone help me with this ?

Should I post the formula I want to change ?

thanks in advance

Jon

[mathare]

Yeah, bung it up Jon. I'm off to bed but I'm sure someone around will suggest modifications for you.

[presto]

=IF(B567="","",IF(G567=1, 50-50*0.035, -J567))

KEY:
B collum - 1 if lay was matched. blank if not matched
g collum - if is winning bet (ie lay loses) type in 1 - if lay wins - leave blank
j collum - = stake (FS)
- the 0.035 displayed is the commish %
- the 50 is the stake

[presto]

hmmm - quiet hard to explain.
thats my formula for FS anyway - i also have a colum for FL - so i only need to imput data once.

[Jonny2621]

=IF(E8<>1,G8-(G8*$H$5),0-(G8*F8)) is the formula to record lays

H5 is commission rate
E8 is result, ie 1 for winner
G8 is stake
F8 is odds

This calculates the loss if result 1 on a lay
I want it to calculate instead the winnings if result 1 on a win bet

Hope Ive clarified this

Thanks anyone who can help me
Jon

[Jonny2621]

=IF(E8<>1,G8-(G8*$H$5),0-(G8*F8)) is the formula to record lays

H5 is commission rate
E8 is result, ie 1 for winner
G8 is stake
F8 is odds

This calculates the loss if result 1 on a lay
I want it to calculate instead the winnings if result 1 on a win bet

Hope Ive clarified this

Thanks anyone who can help me
Jon

Guess I'll have to wait for Mat to come in this morning :)

[mathare]

Guess I'll have to wait for Mat to come in this morning :)Here I am :waver

[sukie]

Jon

This works

=IF(E8<>1,-G8,G8*(F8-1)*(1-$H$5))

Hope it helps.

[mathare]

=IF(E8<>1,G8-(G8*$H$5),0-(G8*F8)) is the formula to record lays

H5 is commission rate
E8 is result, ie 1 for winner
G8 is stake
F8 is odds

This calculates the loss if result 1 on a lay
I want it to calculate instead the winnings if result 1 on a win bet

Hope Ive clarified this

Thanks anyone who can help me
JonYou want the same formula to work for back and lay bets? Or are you using different sheets for backs and lays?

If it's on a different sheet with the same layout then:

=IF(E8=1,G8*F8*$H$5),-G8))

will give you the return on the bet, if you want the profit and are using decimal odds (which I assume from this you are) then you need to replace F8 with (F8-1), i.e.

=IF(E8=1,G8*(F8-1)*$H$5),-G8))

If you like Jon I could give your spreadsheet a quick lick of paint and spruce up. My boss is out of the office today and I'm really not in the mood for work. Plus I really believe that having an easy-to-use-and-maintain set of records is one of the keys to making this game work. After all if the records are hard to maintain human nature is such that you won't bother much after a while.

PM me and I'll give you an e-mail addy if you're interested. I'm sure a few people on here would vouch for my ability to make improvements

[Jonny2621]

thanks Mat, PM on the way :)

[mathare]

=IF(E8<>1,G8-(G8*$H$5),0-(G8*F8)) is the formula to record lays...then it's wrong :yikes:

As you are using decimal odds you are miscomputing your liability here. If the horse wins you lose G8*(F8-1), not G8*F8.

A £20 stake lay on a horse at 6.0 costs you £100 if it wins, not £120.

[Jonny2621]

Anyone know the cheapest place to buy Excel, a ligit one ? Mat has created an all-conquering spreadsheet for me and Microsoft Works is crap and wont open it .

thanks

[Street cry]

Download office 2000 through a media share program like bearshare

[mathare]

Mat has created an all-conquering spreadsheet for meOh hell no. I stuck to using formulae as I thought Works would cope. With full Excel I can make it far more impressive :)

[Jonny2621]

Download office 2000 through a media share program like bearshare

Its going on the company laptop, which is audited, so it has to be legit SC

[Jonny2621]

Oh hell no. I stuck to using formulae as I thought Works would cope. With full Excel I can make it far more impressive :)

Thats it then, Im definitely getting Excel :D

[mathare]

Its going on the company laptop, which is audited, so it has to be legit SCWho do you work for Jon? A lot of places with corporate MS Office licences can sign up for a scheme whereby the employees can install Office in their home PCs and be covered by the company licence. Or at least they can purchase an Office licence for next to nothing.

[Jonny2621]

Who do you work for Jon? A lot of places with corporate MS Office licences can sign up for a scheme whereby the employees can install Office in their home PCs and be covered by the company licence. Or at least they can purchase an Office licence for next to nothing.

I work for me Mat :D Its my business and I made the rule about not copying company software on to home PCs etc and not using duff copies :splapme :D Quite funny really, we are not big enough for corporate licenses.

Ive just found it for £154 so its probably worth it. Im going to load Ecel temporarily on to the laptop just to open the files then i can replace it with the copy when it arrives Monday. Everyones happy, especially Mr Gates :wink

[Jonny2621]

Such a law abiding citizen :)

[Jonny2621]

Wahey, theyre open, nice job Mat :spinning Looks great, I'll have a play and let you know if i get confused !