Excel help help!
 

Hey all,

I have a challenge that my Excel skills don't seem to be up to. It involves inputting the odds (i.e 11/2), multiplying that result (i.e, 5.5) by the stake, which is 5% of the betting bank, adding the initial stake and getting the result, and THEn adding THAT result back onto the inital bank, increasing it, and therefore increasing the stake.

Example: Initial bank of €100, stake of 5%, i.e €5. Odds (to win) are 5/1. The horsey wins, and you get 5x5=25 + 5 (the initial stake) = €30. Now the bank has grown to €130 (100 + 30)

So on the next race 5% of €130 = €6.50, odds are 5/1, horsey wins again, therefore €6.50 x 5 = €32.50 + €6.50 (intial stake) = €39.

So €130 + €39 = €169, etc etc...

I've got to the point where I can get the result, but can't add it back to the initial sum - it creates a circular reference somewhere, and that's where my Excel skills end! Any Excel expert have a solution?

Confab

:ok: :D

Hi,

I can do this, but it is11.30 pm and Mrs EG will kill me if I do it tonight - I will post something tomorrow am.

Yours

EG

Ah the fabled helpfulness of my fellow PPRuNers - thanks in advance EG! :ok: :)

Wot, you mean something like this ?

As promised
 

Thanks Keef - you have saved quite a bit of the the 'grunt work' !!

Taking Keef's excellent work, it could be improved as follows:

Select the odds column D8:D29, hit delete. With D8:D29 still selected click Format, Cells..., Number. Click the Custom category and in the Type box enter: ?/? and click OK. This allows you to enter odds as fractions eg 5/1. However it will reduce odds to their lowest value ie 15/3 would appear as 5/1. To make odds appear exactly as entered is possible, but more long winded.

At this stage I have broken Keef's spreadsheet, so the following repairs need to be made:

Select cell E8 and enter the formula: =C8*D8. Copy this down (Ctrl+Shift +Down Arrow together, then Ctrl+D).

As a further enhancement you could add a 'Race Won?' test:

In G6 enter the text: Race Won?

In cells G8:G29 add: TRUE or FALSE as appropriate.

Then change the formula in F8 to: =IF(G8,B8+C8+E8,B8-C8) and copy it down using the technique above. (This assumes that if you lose you only lose the stake and not the whole pot).

Hope this helps

EG

Thanks guys, that's perfect :ok: :8