Excel help please (re: isblank() )

[BJ]

Importing dividends into excel from a fixed betting site....

I end up with a column of dividends. Next column contains the amount needed to bet to return $100.
My problem is that if there is a scratching, there is a blank cell and I am trying to divide by zero.

=if(isblank(a1),,100/a1)

This formula obviously leaves a value of zero if a1 is blank. I need it to return a blank cell for other formulas to work.

I formatted the number cells to 0;-0;;@ which apparantly hides the zero but the cell still has a value.

Can anybody tell me the correct formula...?
Thankyou everybody for your time...

[Shaun]

leave the formating to this $0.00 that is currency say cell A1 contains the blank try this
=if(A1=0.00,"",100/A1)

[BJ]

Thanks Shaun... Did consider that, but for some reason I had it in my head that "" left a value in the cell.... My spreadsheet is now working.

[Shaun]

Np...you will find that the IF statemnet becomes your best friend...lol i use it all the time....i will uplaod a file containing all the stuff that i have had to search for when setting up my excels sheets for punting and getting information from sites....at this time i can get form from ozeform,cyberhorse,spotingbet and am currently working on a program to get betfair prices in real time...well trying anyways.

[BJ]

Was just coming in to ask a question about the possibility of realtime betfair prices.... I know there is a program that does it but fairly pricey.....

[Shaun]

well i am not 100% sure it is possible...but i can certanly read the page and put it in excel....but doing it automaticly at a rate that is of use to us...thats a different story

[BJ]

As far as betfair is concerned for Australian racing, the only time their prices are relevant is when they are getting matched. This is usually only the last 3-4 minutes of betting... Just not much action except for Sats....

Another question if I can pick your brain again....

When I import the odds, most of my formulas become irrelevant. For example:
$8 $12.5
$3 #Ref!
$2 #Ref!
$4 #Ref!
$5 #Ref!

The autorefresh works, but as soon as I edit the query to choose the next race, this happens...
Sorry but that is the best I can explain it... All I have to do is drag the formula from 12.5 down, but I am presuming there is a setting I can choose....

It seems to me that it is inserting new cells, therefore leaving references to what was there before irrelevant....

[Shaun]

I do have a solution to that problem....just have to dig it up

=INDIRECT("FORM!A1")

or

=INDEX(FORM!A:A,1)

use the top one for single cells and the bottom for multiple cells

[BJ]

Sorry, been busy for a couple of days so am only now reading your response.
I will have a look at your suggestion and see if I can get it going. If I can't, at least now I know where to look in excel help.

[BJ]

Alright, so I am admitting defeat regarding those 2 formulas...

Here is now what I am trying to do....
I want to import data from ias. I am running excel 2003 so have the yellow arrows next to the tables when running a query...

The first table has fixed price divs open price, fluc1, fluc2... in 1 table...
The next has current fixed price.
Then current SP win div.
Then current SP place divs....

If I try and import these in 1 query, They are all lined up in 1 column seperated by spaces...
I want them as seen on the website. But to do this I need to run 4 different queries, and update these 4 queries every race which is no good....


I want to convert the place div to a win div ( placeDiv - 1 )*4, then add it to the fixed and SP win price to get an average win div.... This will be the market I use to find current prices.
But, by the time I have written the formula to average these, the query is updated and my formula wiped out leaving a blank cell.

How do I import these 4 tables as 1 query in the same format as seen on the website, and how do I then use this data?

[Shaun]

I will have a look and see...one way to fix your reference problem is to import your page to the same work sheet but import it away from your work say start at X1 then to get the data where you want it just do an = to that cell in the imported information.

I run Microsoft 2000 pro...things must be different in your version because when i import the page they are put in there own columns....only problem is jockey and trainer are in one colum...but thats because of the little switch they have on the IAS site for viewing them.....can you explain the procedure you use for importing please

[BJ]

Data --> Import external data --> New web query...
Then I just go to the website and click on the yellow arrow next to the corresponding table...
I did try and import the page but ended up with stuff everywhere. Probably could work around it but I thought there might be an easier way. I mean I only want the divs....

As far as the problem of data in the cell disappearing on refresh, I have fixed it.. Other problems all still stand though...

[Shaun]

To be honest i don't know if this is the correct way...but what i do is import the page then extraxt what i need from the page...might be a bit more work involved but then i can place the data where ever i want on the page and do with it what i need.

[BJ]

Yeah probably the way to go... I will try and set it up like that... Don't really have any format to my spreadsheet yet anyway, just trying to get things working...