Handling #DIV/0!

Hi,

sorry if I put this in the wrong part of the forum but wondered if anyone could help me.

I have a large quite complicated spreadsheet which works fine when there are numbers in the relevant cells. However the blank sheet (which is going to be given blank and filled in weekly) creates the #DIV/0! in empty total boxes further down my sheet. I have read about the IF option but am not sure quite how to apply it in my case.

My problem area is this:

=SUM(S29-110)*1.28 (when blank this creates a (-) minus value in T29
so:
Column U29 contains =SUM(T29+110)/O29 so I have the #DIV/0!

can someone help me work this out ??

thank you boffins in advance lol

The #DIV/0 is related primarily to the divisor.

The below:

=SUM(T29+110)/O29

will generate a #DIV/0 !if either

a) O29 is 0

b) Either O29/T29 contain #DIV/0!
Given we know the formula for T29 we know this can only be the case if S29 is #DIV/0! -- we don't know the formula for that cell nor O29 so can't comment further on those.

We will assume that O29 (being 0) is causing the error - in which case

=IF(SUM(O29),SUM(T29+100)/O29,"")

You can use an all encompassing error handler but I would suggest validating only O29 in the above so to avoid masking potentially important errors which would require separate investigation.

On a final note, i revised your title - please keep titles specific to the problem so as to not to detract from the search functionality on the board itself.

Julie,

We will assume that O29 (being 0) is causing the error - in which case

=IF(SUM(O29),SUM(T29+100)/O29,"")

The formula DonkeyOte provided is a very good example of being brief and concise in Excel, to avoid superfluous calculations.

SUM(O29) will return TRUE only when O29 contains a numeric value greater than 0. If O29 contains zero, the test will return FALSE, if O29 contains text, the condition also evaluates to FALSE, so the overall formula will return a blank.

It is not even required to spell out

IF(SUM(O29)>0 ...

or some such, because the result will be either

0 - if the cell contains a zero or text. Zero is equivalent to FALSE
a number greater than zero. Anything greater than 0 in an environment that expects TRUE or FALSE only, will evaluate to TRUE.

So a verbatim comparison with zero is not required.

hth

SUM(O29) will return TRUE only when O29 contains a numeric value greater than 0

Not greater than, rather does not equal - ie only (exactly) 0 equates to FALSE in Excel.

But thanks for adding that point Teylyn, I should probably have elaborated on it myself.

If you're getting #VALUE! then the implication is that T29 is non numeric which will be down to S29 based on your earlier posting -- ie T29 must be generating a #VALUE! error, no ?

T29: =SUM(S29-110)*1.28

The only way for the above to generate a non-numeric value is if S29 is non-numeric, so you could revise to a non-explicit coercion approach

T29: =SUM(S29,-110)*1.28

However if S29 contains a #VALUE! error (!) then you need to investigate that formula - as is we don't know it so can't comment.

What's in T29?

T29: =SUM(S29,-110)*1.28

Tried this (adding the ,) I assume is the only difference, however that didnt seem to help.

Originally Posted by D.O

However if S29 contains a #VALUE! error (!) then you need to investigate that formula - as is we don't know it so can't comment.

S29 doesnt contain a #VALUE error - the forumula is:

=SUM(R29*O29)+M29

Sorry I cant be of more help, I have only been using Excel a few days

I could email the spreadsheet to you lol and you could see whats what then, OR will I get another warning for this, is it not allowed?

If you want people to review your file upload an attachment here directly.

And reiterating my earlier PM you are under no obligation to use ExcelForum.com thus if you prefer non-moderated forums don't use one that is.

Tried this (adding the ,) I assume is the only difference,...

Yes the use of , was the difference. One method explicitly coerces whereas the other does not. In short there is no value in using SUM with embedded coercion, ie:

=SUM(A1+C1)

given the same can be achieved using

=A1+C1

the point of using SUM is that unlike the above it will avoid explicitly coercing:

=SUM(A1,C1)

thus if A1 is "apple" and C1 is 10

=A1+C1 --> #VALUE! as apple is non-numeric and can not be coereced

whereas

=SUM(A1,C1) --> 10, the non-numeric values are ignored (ie XL does not try to convert "apple" to a number)


Regards the problem itself, do you get circular references warning ?

Glad to hear you have resolved. I will close your account for you.