Help with Gruss

[summit]

Hi there

I have a selection system that chooses two dogs from the normal six . This works fine, but occassionally for whatver reason it chooses three selections.

I have tried to put a IF count type trigger in the cells below the BET REF/BET SELECTION stating <3 then BET, but sometimes it places 3 bets.

Any ideas please on how to permanently stop it making three selections?

Thank you in advance.

[Captain Sensible]

The Bet reference only gets entered on the spreadsheet once the bets have been placed, you need to include any <3 inside your trigger criteria before it even gets to entering data in the trigger column Q

[MarkRussell]

Hi,

Put =COUNTA(T5:T10) in cell T3 which will give you a count of Bet Refs you already have in the market.

And then include in your trigger that this count must be <3

Regards,
Mark

[summit]

Thank you for responding so promptly.

However, this is the formula in the trigger column

=IF(AND($E$2="Not In Play",$F$2<>"Suspended",$T$12<3,$AA$23=1,$AB$23=1,$AC$23=1,$T$1<20,$B$3>2500,AA5<3,$X$1="BET"),"BACK","")

$T$12 is the Bet ref column, so surely I should only have two bets placed, but it 5% of time places 3 bets


Thanks for looking

[Captain Sensible]

The problem you'll have counting bet references is that sometimes your criteria might be met by more than 2 runners before any bets are placed so you $T$12 will equal 0 and the bets placed before it can count them.

I've no idea how you fill you stakes columns but if it were me I'd amend your code to only fill in the stake column once the criteria was met and then count the stakes column in $T$12 and change your trigger column code to something like =IF(AND(S5<>"",$T$12<3 ),"BACK","") that way you're counting the runners that meet the criteria before placing any bets if that makes sense to you.

[summit]

Great,

Will give it a go Captain and let you know.

Thanks a lot.

[Captain Sensible]

No problem, hopefully it'll work OK. Might be an idea to count your bet references in a separate cell say U12 and also reference that within your trigger because if you're using formulas they may change criteria after bets have been placed. ie =IF(AND(S5<>"",$T$12<3, $U$12<3),"BACK","")

Nothing wrong with being over cautious when it comes to bots.

[summit]

That's great, thank you very much for taking the time to help with this, I will implement both instructions and let you know.

However, on my journey through Gruss, I have come up with another slight problem and I wondered whether you or anyone else looking a the posts could help (Sorry to bore you all!)

I have the selections all going into the second sheet on my spreadsheet (Named RESULTS) and they all enter perfectly into CELLS A2:F2

Importantly, I have two selections every race - as suggested in my previous query.

However, I have a calculation in CELL H2 (could be anywhere on row two) that is essential to my staking plan. I need this calculation to remain the same and move down to H4 each time the results are updated. However, I cannot get the calculation to move down TWO SPACES in order to remain with the two selection result for that race. Now here's the problem. After have written a small script to move H2 to H4 based on the fact that A2 is changing (Basically if A2 changes then Cell H2 moves to H4 staying in the same line as the selection in A2) Hope this makes sense. It does not work. All the selections move down but H2 remains as H2. However, when I change the selections manually in A2 THEN the H2 moves to H4. It just does not recognise the change WHEN when A2 is changed by the program updating the results. ANY ideas ( Sorry for emphasis capitals)

Thanks agan for looking. It is much appreciated.

[Captain Sensible]

Might be a bit clearer if you just post up whatever script you're currently using. I'm not sure when you mean " I need this calculation to remain the same and move down to H4" do you mean the formula or the value? And will that H4 go to H6 on the next result update too?

If you're already using some VBA you're probably best off doing all the calcs in the script too rather than mixing and matching formulas and vba, you can use all the excel formulas within VBA anyway. I don't use the results sheet myself so not sure of the format or what data it displays but keeping track of the previous p&l or running total should be relatively simple in VBA.

[summit]

Once again, thank you so much for posting your thoughts.

Just to let you know that the initial query of three bets in the race appears to have been resolved having implemented both your suggestions. Thank you.

Second query update.
The SUM(D2:D3) formula in G2 is critical to the the next stake which is calculated in H2

D2 and D3 then move down two rows as the next two selections are imported. The formula in G2 is in G4/G4/G8 etx, so the sum is calculated correctly as the lines move down on each race - No problem.

However, I need H2 (The stake) to move down as well and whilst my script moves this when A2 is changed manually, It does not appear to recognise a change in A2 when the results are inputted automatically from the Gruss/Betfair results service? Basically, the previous calculated figure in H2 NEEDS TO STAY THE SAME AND MOVE TO H4 . The NEW STAKE in H2 is then calculated from the figure in H4. Sorry if this is long winded!!!

That said, your idea of following the P/L may prove to be the answer. I will have a go and let you know.

If you or anybody else has any thoughts on WHY the calculation/figure/stake (whatever you want to call it) will not recognise the automatic result, pleas let me know.

Thank you so much for your input so far. It has been invaluable.

[summit]

Update...

"do you mean the formula or the value? And will that H4 go to H6 on the next result update too?"

I mean the value, and yes, I need to to move to H6 on the next result update as well. It needs to stay with the initial line that it was on.

HTH and thanks again.

[Captain Sensible]

However, I need H2 (The stake) to move down as well and whilst my script moves this when A2 is changed manually, It does not appear to recognise a change in A2 when the results are inputted automatically from the Gruss/Betfair results service? Basically, the previous calculated figure in H2 NEEDS TO STAY THE SAME AND MOVE TO H4 . The NEW STAKE in H2 is then calculated from the figure in H4. Sorry if this is long winded!!!
.

Without seeing the code you're using to move the H2 value to H4 it's hard to know why it's not working, could be something simple maybe not referencing cell addresses fully.

Basically, the previous calculated figure in H2 NEEDS TO STAY THE SAME AND MOVE TO H4 . The NEW STAKE in H2 is then calculated from the figure in H4..

That can be a problem using circular references with formulas as H2 is calculated from H4 and vice versa so generally it's easier to do those calculations where you're storing data to use VBA rater than formulas. So basically your code would go as follows

Cell A2 changes -> copy data from H2 to H4 ->calculate H2 value and enter into H2.

Is the history trail of prices in H6 , H8 etc also used?

[summit]

"Is the history trail of prices in H6 , H8 etc also used?"

Yes, for the most part it would be used...

[summit]

This is the script....

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
Call shiftrow
End If
End Sub
Sub shiftrow()

Range("H2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

[Captain Sensible]

Have you checked that your worksheet_change routine is entered under the correct worksheet?