Excel =0/div

C

cmeb4uby

I have seven cells that I am trying to average the total in the eighth cell.
The worksheet is will not necessarely have an amount in each of the seven
cells I am trying to average therefore I get a =0/div in the total cell
unless there is at least one cell with content. How can I clean up the
worksheet so the =0/div does not appear when there is no activity in any of
the seven cells.
This is the formula I have tried but it will not work.
=IF(P3=0),"",(AVERAGE(D3,F3,H3,J3,K3,L3,N3)

Thanks
 
N

Nick Hodge

=IF(ISERR(AVERAGE(D3,F3,H3,J3,K3,L3,N3),0,AVERAGE(D3,F3,H3,J3,K3,L3,N3))
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
N

Nick Hodge

Sorry typo

=IF(ISERR(AVERAGE(D3,F3,H3,J3,K3,L3,N3)),0,AVERAGE(D3,F3,H3,J3,K3,L3,N3))


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
M

Myrna Larson

Hi, Bob:

Your assumption is that if there is data in at least one cell, the sum will
never be 0, i.e. you won't have just 2 entries, +10 and -10. I don't know
whether that's a safe assumption or not.

How about COUNT instead?

=IF(COUNT(D3,F3,H3,J3,K3,L3,N3)=0,0,AVERAGE(D3,F3,H3,J3,K3,L3,N3))

Myrna Larson
 
M

Myrna Larson

This is the formula I have tried but it will not work.
=IF(P3=0),"",(AVERAGE(D3,F3,H3,J3,K3,L3,N3)

PS: I don't know what is in P3 (the sum?), but the problem with the above
formula is the parentheses aren't correct. It should be

=IF(P3=0,"",AVERAGE(D3,F3,H3,J3,K3,L3,N3))
 
H

Harlan Grove

Myrna Larson said:
How about COUNT instead?

=IF(COUNT(D3,F3,H3,J3,K3,L3,N3)=0,0,AVERAGE(D3,F3,H3,J3,K3,L3,N3))
....

Or use brute force.

=SUM(D3,F3,H3,J3,K3,L3,N3)/MAX(1,COUNT(D3,F3,H3,J3,K3,L3,N3))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top