Validate runners against chosen list.

[andyh]

Hi, I have been trying without success to trigger bets using a list of selected horses on Sheet 1 and a formula in the stake that checks the list and then calculates my stake based on the odds ( I bet to liability).

I have used the following in the stake cell -

=IF(ISNA(VLOOKUP(A5,Sheet1!$A$1:$A$100,1,FALSE)),"No bet",(200/R5))

where A5 contains the horse name, 200 is the stake and R5 the odds cell.

I hope somebody can help as I'm beginning to tear my hair out over this and I don't have much anyway!!

Cheers

Andy

[Captain Sensible]

What are the errors you're getting ? Nothing wrong with your vlookup formula. I assume that all the triggered betting is done from another sheet i.e sheet2 otherwise you need to amned Sheet1!$A$1:$A$100 as it's looking at itself. I also assume you have entered odds into R5

[Ian]

I can't see anything wrong with this as long as R5 contains a number.

What's going wrong ?

[andyh]

Hi guys, I get ERROR in Bet ref and the bet doesn't appear to fire.

[andyh]

Hi Guys looks like this -




Cheers

Andy

[Fixador]

Andyh - your TRIGGER cells have a problem - check this

[andyh]

The trigger cell contains the following -

=IF(AND(H5>=2,H5<=12,$A$40<0.0166),"LAY","WAIT")

with A40 containing

=($D$2-INT($D$2))*24

Can I also clarify that if the list of horses is on sheet 1 and the aforementioned formulas including the stake lookup are all on sheet 2 then this isn't causing an issue?

Thanks for all the help.

[Captain Sensible]

I assume the #value error in your picture is just because the race has finished and the sheet has tried to fire a bet . Might be due to the stake not being rounded, I always round mine as a matter of course in any sheets I do. Try using

=IF(ISNA(VLOOKUP(A5,Sheet1!$A$1:$A$100,1,FALSE)),"No bet",ROUNDDOWN(200/R5,2))

[Ian]

Shouldn't you be checking in your trigger formula that the horse in question is on the list ?

I think you may have triggered bets on some of the horses with a stake of "no bet".

In all my spreadsheets, I put all the criteria in the trigger formula and only work out the odds and the stake
if trigger cell is "LAY" leaving them blank if it isn't.

I also round the stake as the Capt suggests.

[andyh]

Hi Ian, would you therefore be using nested IF statements as the trigger formula?

Andy

[Captain Sensible]

andy your code seems to be firing bets on all runners when =IF(AND(H5>=2,H5<=12,$A$40<0.0166),"LAY","WAIT") is valid. As some of the runners wont have a stake entered it looks like the API is rejecting the whole request because of these 'duff' bets. You need to set your code to only fire bets only on the runners you want to i.e. the ones where you've entered a stake. Easiest way without amending the rest of your sheet would be to amend the trigger cell from

=IF(AND(H5>=2,H5<=12,$A$40<0.0166),"LAY","WAIT")

to

=IF(R5="No Bet","",IF(AND(H5>=2,H5<=12,$A$40<0.0166),"LAY","WAIT") )