#N/A in formula cell

Discuss anything related to using the program (eg. triggered betting tactics)

Moderator: 2020vision

#N/A in formula cell

Postby slochin » Sat Jan 09, 2016 1:22 pm

In my sheet I have either two or three selections entered as a result of a formula placed in either AB13,AC13 or AD13.
Now sometimes because the horse or horses are not in the list that I obtain the entries from, #N/A is placed in one or two of the cells .
As I wish to use these cells with other formulas I would like some assistance please in two areas.
1.Is there a way to have 'nothing' placed in the cell if the horse is not in my list?
2. As I wish to use these cells with other formulas it appears I need to do something with IF ISNA is that correct.
This is the next step.
I would like to be able to work out a procedure where I can check a list of horses that I place in my Gruss excel sheet in AD16 down and then check them against the horses in AB13,AC13,AD13.
If there is a match then I would like the horses name deleted from that cell.
slochin
 
Posts: 65
Joined: Sat Nov 28, 2015 1:20 am

Re: #N/A in formula cell

Postby Captain Sensible » Sat Jan 09, 2016 2:59 pm

You can just use the ISNA() function with an IF statement to have the Cell show as nothing, it just returns either TRUE or FALSE so a simple IF will clear it for you. Say our formula is in A1 that's returning the #N/A your IF would simply be

=IF(ISNA(A1),"",A1)

Obviously you replace the A1 with your actual formula

Not sure what you're asking by saying deleted from that cell, are these entered from a formula? I.e. you just don't want them displayed, if so a simle IF and vlookup should sort that or entered using VBA and you want the deleted?

You might just be overcomplicating things by mixing and matching VBA and formulas, alot of us will use VBA routines to set up market variables etc ready for betting and the routine only runs once then resets upon market change rather than Formulas which run continually. MIght be worth considering using VBA to do all the name matching if this routine only needs to run once.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: #N/A in formula cell

Postby slochin » Sun Jan 10, 2016 1:19 pm

Hello @Captain Sensible,
Thanks again for your efforts to assist me. As an aside some time ago I mentioned about making sure my bets were placed. I have noticed while I am trialling that in some of the smaller markets my bets might not be completed in full.
To get matched at BSP you either use "BACK-SP" and any unmatched part (as long as it meets the minimum BSP requirements) will go into the BSP and get matched at whatever odds the BSP returns.
This is my trigger at the moment.
=IF(AND(S5<>"",R5<11,$AA$1="Yes"),"BACK","")
So is the correct set up for making sure any balance of the bet that has not been taken at the off is then placed at BFSP.

=IF(AND(S5<>"",R5<11,$AA$1="Yes"),"BACK-SP","")
I would never have known that as looking through the manual this is the only trigger report I can find :

Back
BACK, BACKR, BACKSP

I have started to learn VBA so hopefully in the future I might be able to convert my project into same.
slochin
 
Posts: 65
Joined: Sat Nov 28, 2015 1:20 am

Re: #N/A in formula cell

Postby Captain Sensible » Sun Jan 10, 2016 1:59 pm

They're listed under the trigger modifiers as they're adding Keep options to the bet same as fill/kill, stop loss etc

http://www.gruss-software.co.uk/Betting ... ifiers.htm

BACKSP goes straight into the BSP pool and you can set minimum odds you're prepared to accept
BACK-SP means any unmatched portion of the bet at the off goes into the BSP with no minimum odds

You have to weigh up the pro's and cons to decide if you think you'll beat the BSP cos of the size of your bets and BACK-SP is better for you than using BACKSP with the ability to set minimum odds. You can easily switch between the two depending on market conditions/course etc and just add criteria to choose which is better suited to liquidty in the market if you have probems down the line.

http://www.gruss-software.co.uk/Betting ... e/help.htm
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: #N/A in formula cell

Postby slochin » Mon Jan 11, 2016 10:26 am

Hello @Captain Sensible,
Thank you for the information re Back-Sp. Something I have noticed today when betting with my timing at D2<=TIMEVALUE("00:02:00") and my odds restricted <11 during the two minutes before the race the odds can change and perhaps only one bet is made with the full stake and then later another horse qualifies and a bet is placed on that meaning I have exceeded my stake. If I place my timing at exactly 2 mins would that mean only bets which qualify at that particular time would be placed?
A further query I have relates to preliminary work.
For each race meeting I have races that I delete according to either track conditions or field size. I do this manually before entering into my excel sheet in Gruss. I am wondering if it is possible to have this automated as it would save a lot of time.
The two criteria are :
Delete the complete race and horses if the race is classified as Soft 6,Soft 7, Heavy 8,Heavy 9, Heavy 10.
and
Eliminate all races that have less than 8 runners or greater than 18 runners.
Below is a small sample worksheet, formatting received not the best.
Image
slochin
 
Posts: 65
Joined: Sat Nov 28, 2015 1:20 am

Re: #N/A in formula cell

Postby Captain Sensible » Mon Jan 11, 2016 2:18 pm

slochin wrote:Hello @Captain Sensible,
Thank you for the information re Back-Sp. Something I have noticed today when betting with my timing at D2<=TIMEVALUE("00:02:00") and my odds restricted <11 during the two minutes before the race the odds can change and perhaps only one bet is made with the full stake and then later another horse qualifies and a bet is placed on that meaning I have exceeded my stake. If I place my timing at exactly 2 mins would that mean only bets which qualify at that particular time would be placed?
A further query I have relates to preliminary work.
For each race meeting I have races that I delete according to either track conditions or field size. I do this manually before entering into my excel sheet in Gruss. I am wondering if it is possible to have this automated as it would save a lot of time.
The two criteria are :
Delete the complete race and horses if the race is classified as Soft 6,Soft 7, Heavy 8,Heavy 9, Heavy 10.
and
Eliminate all races that have less than 8 runners or greater than 18 runners.
Below is a small sample worksheet, formatting received not the best.
Image



Generally the bulk of the liquidity comes close to the off so that's usually when the more stable and smallest market book percentages are mening you should be beating SP, but obviously if you think you'll get better odds,and matched, 2 minutes out then bet then. I've no idea what your system does so no idea if later qualifiers should be backed or not. A lot of people will just count the betref column if they want to restrict further bets. I.e. you just add another criteria to your trigger counting the betref column to see how many bets have been made and it's more than the max bets you want on the market don't bet.

Easy enough to delete markets from the quickpick list but might just be simpler to add additional criteria to your trigger not to bet when those conditions are met i.e. just count the number of runners and restrict the trigger from firing greater than 18 less than 8. Deleting them at the start of the day may mean you miss out or back markets later on where later non runners come along.

I've no idea where you'd get the Soft/Heavy data from so unless it can be automated you may just have to enter those courses somewhere on the sheet for the program to reference with something like VLOOKUP and your criteria avoid those courses for betting.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: #N/A in formula cell

Postby slochin » Wed Jan 13, 2016 5:36 am

Hello Captain Sensible,
Thanks for response. That was a nice and simple way to sort out the field sizes.
With the track conditions they are always in the header at the far right. Unfortunately they do not format nicely into the one column. As shown in the worksheet the first row has the track condition at the end in M+N (Good 4)and then the next race it is in K+L (Heavy 10).
That's why I wondered if perhaps VBA would be able to go through the Excel sheet I have before I load it into Gruss and locate the track condition and delete the races and horses according to the track criteria listed earlier.

Everything is running smoothly apart from the odd occasion where more bets are placed than what I want. It is not a matter of exceeding a total number of bets. I am using my own Dutch bet staking.
What appears to me to be happening after watching it closely today is that it is due to the fact I have a <11 odds restriction. I have the race loaded immediately after the previous race has started and then in AA1 I have my markers such as
=IF(AND(E2="Not InPlay",F2<>"Suspended",OR(D2=TIMEVALUE("00:01:00"),
ISTEXT(D2))),"Yes","No").
I think what happens is that at the one minute mark say one of my selections is <11 and the other two are over, so a bet for the full stake is placed on that selection at $10.
Now if between that bet being placed and the start of the race one of the other horses comes in under 11 this triggers another smaller bet which I do not want as that horse at the 1 minute mark was over the odds 11 and that also takes my total stake over $10.
This is an example from my betting today :
Showing 1 - 2 of 2 Selections
Selection Odds Stake($) Bid type Placed Profit/loss($)
4. Accidental Journey 10.50 2.55 Back 13-Jan-16 14:06 -2.55
5. Bakers Dozen 4.20 10.00 Back 13-Jan-16 14:03 32.00
This shows the bet placed at 14.03 at my full stake 10.Then three minutes later at 14.06 another bet is placed @2.55 with the odds being 10.5.Which seems to agree with my opinion ,perhaps that horse was >11 prior.
What I suppose I really want is when the one minute comes into focus then bets are only placed once at that time according to my criteria and then no further bets are placed.
slochin
 
Posts: 65
Joined: Sat Nov 28, 2015 1:20 am

Re: #N/A in formula cell

Postby Captain Sensible » Wed Jan 13, 2016 1:28 pm

If you only want your selection routine to bet once, either add another criteria to your formulas that counts the betref column and only fires if it's 0, it will still allow more than one bet at that time as the bets get sent in one submission noot one at a time. If you're using VBA to fire in the bets just add a flag into your bet routine to only fire when that's false, set it to true after firing bets then reset it back to false when the market changes so your routine can refire.

You should be able to come up with some routine to delete the Heavy courses but I'd have a look at how you're getting the data onto the sheet first as it seems very jumbled and I'd guess it's sent to you formatted into neat columns. I'd imagine the current format is very risky to use automated as even the names seem to be split over various cells which could easily mess up your vlookup routines if you use them.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: #N/A in formula cell

Postby Captain Sensible » Wed Jan 13, 2016 1:34 pm

Have a look at the various paste options if that's how you're putting the data into the sheet, seems everything is being separated by spaces, rather than the table format I guess it was sent to you in. At the end of the day a regular format make life a lot easier but you do have options within VBA like Instr to start picking out markets you'd want to avoid
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: #N/A in formula cell

Postby slochin » Sat Jan 16, 2016 12:05 pm

Hello Captain Sensible,
I have tried various ways to have the information set up neatly to no avail. I think the problem may be that the heading with the race information is in one line.
Then the rest of the data below seems to fit neatly. Below is what happens if I paste it direct into sheet. The track condition is always at the end of the line that has the time of race etc.

12:25 Flemington Race 1 16Jan16 1100m Opn 2YO SWP $80000 Good 4

No Form Horse Wodds Marg PrzCh Hcap BP WPC PPC A# Wnet Othr TCDWHB
1 1 Hey Doc 1.9 0.8 +40 57.0 3 100.0 100.0 1 43.8
2 41 Bringaroo 8.3 4.3 +60 56.0 6 50.0 50.0 4 34.1 DW

Perhaps it might be easier to have some VBA code to sort out the info from the heading which is a complete string?
My selection process allows for up to bets on three different horses. One of the criteria is -no bets if odds >11. Now say at the two minute mark I have three possible selections listed and they show odds of $4-$8 and $13.
What is happening is the bets are triggered immediately and placed $7 for the $4 odds and $3 for the $8 odds. Now that should be it as I have out laid my full stake $10.
The problem arises when the odds of the third selection reduce to under 11 after those bets are placed and therefore it now qualifies as a bet and a bet is then placed on this third horse before the race starts.
slochin
 
Posts: 65
Joined: Sat Nov 28, 2015 1:20 am

Re: #N/A in formula cell

Postby Captain Sensible » Sat Jan 16, 2016 4:14 pm

slochin wrote:
Perhaps it might be easier to have some VBA code to sort out the info from the heading which is a complete string?.


Is it all jumbled when you recieve it in the email? I'm surprised your VLOOKUP routines are working to pick out the horse names considering they're spread over various cells, tbh.

If thats the best format you can get it to I'd go down the route of using VBA to combine the cells to one string then process them with Instr to pick out the ones with colons i.e. the times, check the last two words in the string if they're on your heavy list, then flag up those times as races not to bet on, again just by checking against the Race details in A1.


slochin wrote:
My selection process allows for up to bets on three different horses. One of the criteria is -no bets if odds >11. Now say at the two minute mark I have three possible selections listed and they show odds of $4-$8 and $13.
What is happening is the bets are triggered immediately and placed $7 for the $4 odds and $3 for the $8 odds. Now that should be it as I have out laid my full stake $10.
The problem arises when the odds of the third selection reduce to under 11 after those bets are placed and therefore it now qualifies as a bet and a bet is then placed on this third horse before the race starts.



Like I mentioned earlier if you only want your bet selection routine to fire once just add another condition to either set a flag in your VBA betting routine to say it's fired or just add another criteria to your trigger cel not to bet if there is more than one bet reference in the bet ref column

=IF(AND(COUNT(T5:T60)>0,other conditions…),"BACK","")
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: #N/A in formula cell

Postby Captain Sensible » Sat Jan 16, 2016 4:28 pm

Should be

=IF(AND(COUNT(T5:T60)=0,other conditions…),"BACK","")
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: #N/A in formula cell

Postby Captain Sensible » Sat Jan 16, 2016 6:02 pm

Looking at the data you posted I'm guessing the prefix of heavy or soft isn't actually relevant just when the number is 6 or above you don't want to bet?

If the data is all on one line (if not it's not hard to combine it) all you need to do is search for the time of the race from the data in A1 against your pasted data and display the last number, then if that's 6 or above don't bet.

To get the number you already have the code findword on your sheet to get the distance so adding that to some simple VLOOKUP you should be able to get the last number by using the following code, just change Sheet2! to whatever your sheet is called.

Code: Select all

=RIGHT(VLOOKUP(findword(A1,6)&"*",Sheet2!A1:A500,1,FALSE),2)*1
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: #N/A in formula cell

Postby slochin » Sat Jan 30, 2016 11:44 am

Hello Captain Sensible,
Thanks again for response. My project is now working along nicely thank mainly to your willingness to assist me.
The only part I have not been able to automate is the elimination of certain races according to track conditions or as you pointed out according to numbers.
With the format in its early stage of pasting into Excel the heading is in one long line with the number at the end.
However I do some work on this and end up with three different groups, one has only the two horses per race I am interested in and they do not need to show the race headings,so I really need to be able to eliminate the races prior to putting the sheet into my Gruss sheet.
This is where I have run into the problem of trying to download from the website into a decent format.
I have tried pasting using the delimiter with tab,space etc and also tried fixed and download from web Excel.
The closest I have reached is having the race info in neat columns but the heading is fragmented as per sample below.
I have reduced the number of horses .

Image



If there was a way to have the heading in neat columns without rearranging the other rows this would be the ideal position particularly in relation the end cells where the number is.
Now I am reading up on VBA and I imagine the instring would be able to isolate the number.
The next step would then if possible be to eliminate that line if it was over the certain number and also eliminate the horses down until the next listed race and then continue checking for the number.
This of course is well beyond my capabilities at present.
I also now have two separate groups to eliminate.
1. In one sheet all those races over 6.
2. In the other all those races over 8.
slochin
 
Posts: 65
Joined: Sat Nov 28, 2015 1:20 am

Re: #N/A in formula cell

Postby Captain Sensible » Sat Jan 30, 2016 2:58 pm

I'm surprised if you downloaded the data via an excel webquery it still came out garbled, Does the data look formatted into a table on the website you're accessing?

Not sure why you feel the need to do all this pre-processing before you paste into excel as excel can proably do all that donkey work a lot quicker, If you can dump it into excel all on one line maybe that's your best option and use VBA to sort it out into a better format. If you don't want to go that route you have options like the CONCATENATE function that'll allow you to join cells together in one long string
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Next

Return to Discussion

Who is online

Users browsing this forum: No registered users and 23 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.