error #DIV/0!

[ML]

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

 

[JE McGimpsey]

One way:

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

 

[Peo Sjoblom]

One possible way

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



--


Regards,


Peo Sjoblom

 

[Thomas Hardy]

Try

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

Regards

Thomas

 

[ML]

Wow that was quick!
Thanks very much
X

Try

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

Regards

Thomas

 

[Dana DeLouis]

=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))

 

[JE McGimpsey]

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)

Hi. Just guessing, but since

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

would this work as an alternative

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

--
Dana DeLouis

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

[/QUOTE]

 

[Dana DeLouis]

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.


--
Dana DeLouis

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?



Hi. Just guessing, but since

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

would this work as an alternative

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

[/QUOTE]