Help With Excel Error

[xptdriver]

Gday Excel Gurus ,

When I do my prices I do them in Excel... I have no problems importing the data or formulating the prices.. My problem is when I enter the formua that gives me the final price, I get an error in the cells that should be empty.. I have to go back and manually clear the error from the cells.. just to make it look better i guess.. the error i get is #DIV/0!

My question is how can I either stop this error from displaying or alternatively remove all of them in one hit instead of doing it cell by cell. I have used the find/replace command but it does not recognise the error as even being there.. Any help would be greatly appreciated... it is really only needed to speed things up for me is all, it isnt a huge prob but more of a nuisance

Thanks in advance

[La Mer]

Gday Excel Gurus ,

When I do my prices I do them in Excel... I have no problems importing the data or formulating the prices.. My problem is when I enter the formua that gives me the final price, I get an error in the cells that should be empty.. I have to go back and manually clear the error from the cells.. just to make it look better i guess.. the error i get is #DIV/0!

My question is how can I either stop this error from displaying or alternatively remove all of them in one hit instead of doing it cell by cell. I have used the find/replace command but it does not recognise the error as even being there.. Any help would be greatly appreciated... it is really only needed to speed things up for me is all, it isnt a huge prob but more of a nuisance advance

Format your cells along the lines of the following:

=IF(A5="","",A5/A4)

which should leave all nil/zero value cells blank.

You could also probably use the ISBLANK function (have a read of the help file re this function).

[Shaun]

you beat me to it La Mer
i would have to say that formula is the most used in my excels sheets i use it all the time to generate blank cells when needed

[xptdriver]

Gday gurus,

thanks for that... but i must be slightly retarded (or maybe severely). But the way I read that, I still have to do each cell individually.. I tried it and cant run down a column and only get rid of the errors.. all the data goes.. I must be doing something very very wrong.. :( Unless I have totally misunderstood you blokes

[Chrome Prince]

xptdriver,

What you do is use the formula offered and place it in the top cell you start on, then just copy and paste it all the way down the column. That will fix your error and still provide the calculation you require.

[La Mer]

Gday gurus,

thanks for that... but i must be slightly retarded (or maybe severely). But the way I read that, I still have to do each cell individually.. I tried it and cant run down a column and only get rid of the errors.. all the data goes.. I must be doing something very very wrong.. :( Unless I have totally misunderstood you blokes

It depends on how you've done your formatting, but say for instance that your prices are appearing in column D, which are the results of calcs in columns A B & C, then what you can do as an option is in column E write the following formula:

If(d1="","",d1) etc in all the cells where you want the prices to be shown. column D then can be hidden so that you only ever see the final outcomes in column E - if that makes sense. There should be no need to continually run down a column to get rid of the errors unless you are using macros or VBA code that is overwriting the column in which your prices are being shown.

I generate prices of this nature every day and never have a problem re nil entry cells.

[xptdriver]

Gday All...

I don't think I have explained what I do properly (my fault).

I arrive at my final price using this formula =F6/E6*0.86 That is arrived at from data also in C and D ... F and E are just totals.. ( confusing myself here)

Column A Holds the race name example :
Race 1 2:00 PM Sky Channel Rating Services Maiden Handicap 2yo

Also in Column a (Next lines down) are the horse numbers

In column B are the horses name

I leave a 1 line space between each race

When I grab the formula (above) and fill down column G, where there is the Race name, and where the 1 line space is.. that #DIV/0! error appears..

So i was wondering how to get rid of it.. what i do at the moment is just work my way up or down the sheet hi lighting errors and using "clear contents". It works but I was hoping there was another way..

Sorry to be such a dill

[La Mer]

Gday All...

I don't think I have explained what I do properly (my fault).

I arrive at my final price using this formula =F6/E6*0.86 That is arrived at from data also in C and D ... F and E are just totals.. ( confusing myself here)

Column A Holds the race name example :
Race 1 2:00 PM Sky Channel Rating Services Maiden Handicap 2yo

Also in Column a (Next lines down) are the horse numbers

In column B are the horses name

I leave a 1 line space between each race

When I grab the formula (above) and fill down column G, where there is the Race name, and where the 1 line space is.. that #DIV/0! error appears..

So i was wondering how to get rid of it.. what i do at the moment is just work my way up or down the sheet hi lighting errors and using "clear contents". It works but I was hoping there was another way..

Sorry to be such a dill

Try this:

=IF(B6="","",F6/E6*0.86)

What this does is test cell b6 for a horse's name and if there isn't one return a blank in cell g6, assuming that if you have a horse's name entered there will be corresponding entries in cells e6 and f6.

[xptdriver]

Gday la Mer,

Sorry for being a pest and a dill... but thank you very much that did the trick... very much appreciated.. Again thanks heaps..