error #DIV/0!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ML - 25 Sep 2007 17:21 GMT

Hi.
I'm using this formula
=100*SUM(D11:F11)/SUM(D11:K11)
but if none of the cells in the range have any data, the result is #DIV/0!
I'd like to add to the formula that if nothing is entered to return blank. I
was trying with =IF but I'm getting mixed up with where and what should have
brackets.
Can anyone help.
Thanks

Reply to this Message

JE McGimpsey - 25 Sep 2007 17:33 GMT

One way:

=IF(SUM(D11:K11)=0,"",100*SUM(D11:F11)/SUM(D11:K11))

> Hi.
> I'm using this formula
[quoted text clipped - 5 lines]
> Can anyone help.
> Thanks

Reply to this Message

Peo Sjoblom - 25 Sep 2007 17:34 GMT

One possible way

=IF(SUM(D11:K11)=0,"",100*SUM(D11:F11)/SUM(D11:K11))

Signature

Regards,

Peo Sjoblom

> Hi.
> I'm using this formula
[quoted text clipped - 5 lines]
> Can anyone help.
> Thanks

Reply to this Message

Thomas Hardy - 25 Sep 2007 17:34 GMT

Try

=If(SUM(D11:K11) = 0 , "" , 100*SUM(D11:F11)/SUM(D11:K11))

Regards

Thomas

> Hi.
> I'm using this formula
[quoted text clipped - 5 lines]
> Can anyone help.
> Thanks

Reply to this Message

ML - 25 Sep 2007 17:41 GMT

Wow that was quick!
Thanks very much
X

> Try
>
[quoted text clipped - 14 lines]
>> Can anyone help.
>> Thanks

Reply to this Message

Dana DeLouis - 27 Sep 2007 14:15 GMT

> =100*SUM(D11:F11)/SUM(D11:K11)

Hi. Just guessing, but since

(d + e + f)/(d + e + f) = 1

would this work as an alternative

=100*SIGN(AVERAGE(D11:F11))

Signature

Dana DeLouis

> Hi.
> I'm using this formula
[quoted text clipped - 5 lines]
> Can anyone help.
> Thanks

Reply to this Message

JE McGimpsey - 27 Sep 2007 16:14 GMT

I'm curious:

=100*SIGN(AVERAGE(D11:F11))

still gives a #DIV/0 error if D11:F11 are unpopulated.

And it gives a very different answer than the OP's

=100*SUM(D11:F11)/SUM(D11:K11)

if G11:K11 have non-zero values, so I don't see why it would be a good
alternative.

Am I missing something obvious?

> > =100*SUM(D11:F11)/SUM(D11:K11)
>
[quoted text clipped - 15 lines]
> > Can anyone help.
> > Thanks

Reply to this Message

Dana DeLouis - 27 Sep 2007 17:50 GMT

Oh my! Please ignore that. Yes, that is an error.
I double checked that range the op was using, and only saw 1 range. I
tripple checked it! I don't know how I missed that visually. I was trying
to figure out a short cut, or why the op was using 1 range.
Here's what I did. I copied the equation the op was using, and pasted it
into Excel.
I then clicked "Trace Precedents" to make sure I was seeing it correctly.
It was only showing me the range D11:F11. I was only looking at the
highlighted range D11:F11 and was trying to figure out why the summing of
the same range.

So, is this a Bug? Now that I see there are two ranges (w overlap)
shouldn't the "Trace Precedents" show the range out to K11?

It caused me to miss it. If I double click the formula, I get the two
referenced ranges highlighted in color, as it should.

Signature

Dana DeLouis

> I'm curious:
>
[quoted text clipped - 33 lines]
>> > Can anyone help.
>> > Thanks

Reply to this Message

Rate this thread: