Help with betting on the book and using the CLEAR ref

[easternpromise]

Hi Guys

At the moment for horse racing i place a back bet on all runners before the off.

Can you advise on how to add the clear ref to my formula so the Q column (and column T-W if required) prevents my spreadsheet continuously betting.

then also

I would like to amend my tactics by placing back bets on the all runners again only when the previous bets have all been matched?

I would like to use formula if possible because my excel knowledge is pretty poor when it come to macros?

Many Thanks

[Captain Sensible]

You just need to use a few IF statements and the CLEAR trigger,

if you want to use formulas only you can always use a MyBets sheet and monitor the matched status of your existing bets to ensure you don't refire bets whilst some are still unmatched.

[doris_day]

I must admit I had a few issues regarding where and how to place the "CLEAR" trigger but here is an example. Say you want to clear any bet references if your liability on any runner is less than £20, which would show as -20 in the X column, you'd write is like this IF(X>-20,"CLEAR","LAY")
assuming you wanted to LAY. Obviously if you wanted to back then you'd put "BACK" instead of the "LAY". Naturally your IF statements will be more complex than this but its a simple example to show where you put the trigger "CLEAR". Hope that helps

[easternpromise]

=IF(T5>0,"CLEAR",IF(AND(COUNTIF(MyBets!$E$2:$E$50,"F")+COUNTBLANK(MyBets!$E$2:$E$50)=49,BACK,""))

I haven't looked at how the MyBets sheet works, but i'm going to try a clear and countif statment like this. Do you foresee any problems with this?

Thanks

[MarkRussell]

Hi,

If you wanted to count the number of Unmatched bets from the MyBets sheet for a given runner you can use the following Excel formula.
Put the formula in a spare column.

=SUMPRODUCT((MyBets!B:B=A5)*(MyBets!E:E="U"))

The formula works for the runner in row 5 and can be copied down for other runners.

Regards,
Mark