Excel help please

[hmmm]

Could someone please have a look at attached Excel Zip File and tell me how to get rid of this " #DIV/0! ".
Thank you so much.

[liam858]

Is it because the Cell you are trying to use contains no data, e.g. it has a 0 in cells B12:B36??


Liam

[hmmm]

Thank you for your answer Liam.
I might be wrong but I thought there was a way of not showing these #DIV/0! cell values????
If you have noticed because of these #DIV/0!s I am not able to see the Totals in cells N37, P37 and V37.
Basically I'd like to add quantities to B12 to B36 as I need them.
Sorry to be a pain.

[liam858]

The help file says:

Possible causes and solutions

Entering a formula that contains explicit division by zero (0)— for example, =5/0

Change the divisor to a number other than zero.

Using the cell reference to a blank cell or to a cell that contains zero as a divisor

Note If the operand is a cell that is blank, Microsoft Excel interprets the blank as zero.

Change the cell reference to another cell.

Enter a value other than zero in the cell used as a divisor.

Enter the value #N/A into the cell referenced as the divisor, which will change the result of the formula to #N/A from #DIV/0! to denote that the divisor value is not available.

Prevent the error value from displaying, using the IF worksheet function. For example, if the formula that creates the error is =A5/B5, use =IF(B5=0,"",A5/B5) instead. The two quotation marks represent an empty text string.

But i think as you go through and change the values in B12:B36 it will not display "#DIV/0!" in each according cell across the column.....and when you change the totals in the Profit column, e.g. N5:N36 it will change the overall total accordingly.

Liam

[hmmm]

Point taken Liam...Appreciate it!

[PC Solutions]

There is
You can trap a division by zero or other errors by using Excels iserr function in conjunction with an IF statement

For example
if(iserr(condition, what if true, what if false))

If condition true tell it to do nothing that would be "" a nul string
I use it all the time

[hmmm]

Forgive my novice knowladge PC Solutions, could you please give me an example relating to one of my #DIV/0! cells?
I'll be honest I have never used ISERR.
Cheers

[hmmm]

WOW!!!!!!!
I can't belive it PC Solitions!!!!!!!!
It DID WORK!!!!! If you look at from Cell L12 to L36 (changed it to ISERR format) it worked...
This link explains how to use ISERR clearly.
http://www.techonthenet.com/excel/formulas/iserr.php
Thank a lot.

[hmmm]

Sorry I forgat to attach the file.

[PC Solutions]

I havent looked at your spreadsheet at all
The link you provided was very good, Except for one thing, if you want it to do nothing if the condition tests true.

For example
if(iserr(a division by zero or other bad condition, "", otherwise caclulate it))
Watch the parenthesis. They are important

Basically the way the formula work is this:
If check for an error condition
What do you want to do if the error condition is true?
Most likely nothing, so give it a nul string
Thats ""

Else, what do you want to do if the error condition is false.
Like I said, Iserr is a great Excel function. I use it all the time






Glad it helped

[liam858]

Cool, glad it's sorted, Nice one PC Solutions.


Liam