Excel help help!

[Confabulous]

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

[ExGrunt]

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

[Confabulous]

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

[Keef]

Wot, you mean something like this (http://www.theology.dsl.pipex.com/horses.xls)?

[ExGrunt]

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

[Confabulous]

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