how do I average cells if a cell is at #div/0!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

delete automatically - 24 Sep 2007 22:16 GMT

ok heres what I have.
in cell D6 I have formula of =AVERAGE(D7:D10) which its at #div/0!
in cell D11 = AVERAGE(D12:D15) which its at 85
in cell D16 =AVERAGE(D17:D210) which its at 50

in cell D3 I just want the average of cell D6,D11,and D16
is this possible and how?

Reply to this Message

JW - 24 Sep 2007 22:52 GMT

On Sep 24, 5:16 pm, delete automatically
<deleteautomatica...@discussions.microsoft.com> wrote:

> ok heres what I have.
> in cell D6 I have formula of =AVERAGE(D7:D10) which its at #div/0!
[quoted text clipped - 3 lines]
> in cell D3 I just want the average of cell D6,D11,and D16
> is this possible and how?

Possible, yes. But why allow errors in your worksheet? Handle the
errors in your formulas and you won't have this problem.
=IF(ISERR0R(AVERAGE(D7:D10),0,AVERAGE(D7:D10))

Reply to this Message

David Biddulph - 24 Sep 2007 23:03 GMT

I assume you mean
=IF(ISERROR(AVERAGE(D7:D10)),0,AVERAGE(D7:D10))
and not
=IF(ISERR0R(AVERAGE(D7:D10),0,AVERAGE(D7:D10))
(with the missing closing parenthesis, and with an O where you had a zero),
but do you really want to return a zero in the error case, or wouldn't
=IF(ISERROR(AVERAGE(D7:D10)),"",AVERAGE(D7:D10)) be better so that it
doesn't affect the next stage average in D3?

Signature

David Biddulph

> On Sep 24, 5:16 pm, delete automatically
> <deleteautomatica...@discussions.microsoft.com> wrote:
[quoted text clipped - 9 lines]
> errors in your formulas and you won't have this problem.
> =IF(ISERR0R(AVERAGE(D7:D10),0,AVERAGE(D7:D10))

Reply to this Message

JW - 25 Sep 2007 00:14 GMT

On Sep 24, 6:03 pm, "David Biddulph" <groups [at] biddulph.org.uk>
wrote:

> I assume you mean
> =IF(ISERROR(AVERAGE(D7:D10)),0,AVERAGE(D7:D10))
[quoted text clipped - 20 lines]
> > errors in your formulas and you won't have this problem.
> > =IF(ISERR0R(AVERAGE(D7:D10),0,AVERAGE(D7:D10))

Yes, left a parenthesis out. Typo on my part. As for the average
portion, could be either way. In my line of work, those zeros need to
be included in the grand average or I'd be reporting false data and
get kicked to the curb!

Reply to this Message

delete automatically - 24 Sep 2007 23:12 GMT

Thanks for the help, that worked fine but I think I have another problem. I
will post it when I figure what I need

Reply to this Message

David Biddulph - 24 Sep 2007 22:54 GMT

Change the D6 formula to =IF(ISERROR(AVERAGE(D7:D10)),"",AVERAGE(D7:D10))

But be careful taking the average of averages. Are you sure that you don't
need a weighted mean?

Signature

David Biddulph

Reply to this Message