average values in non-contiguous cells, ignoring 0 values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RWormdahl - 30 Oct 2006 01:11 GMT

How do I average the values in non-contiguous cells, while ignoring cells
with 0 values? I tried the average function, but the IF conditional seems to
recognize only contiguos cells. I keep getting the "The formula you entered
contains an error..." message, and cannot execute the F2 ctl+shift+enter
command.
Any ideas?

Reply to this Message

JMB - 30 Oct 2006 01:37 GMT

One way, assuming the noncontiguous range is G7:G9,G12:G14

=SUM(G7:G9,G12:G14)/SUMPRODUCT((G7:G9<>0)+(G12:G14<>0))

> How do I average the values in non-contiguous cells, while ignoring cells
> with 0 values? I tried the average function, but the IF conditional seems to
> recognize only contiguos cells. I keep getting the "The formula you entered
> contains an error..." message, and cannot execute the F2 ctl+shift+enter
> command.
> Any ideas?

Reply to this Message

JMB - 30 Oct 2006 02:01 GMT

Sorry - that doesn't work if the ranges are not the same size.

See Ragdyer's post here - I think your question is that same as this one:

http://www.microsoft.com/office/community/en-us/default.mspx?&lang=en&cr=US&guid
=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&p=1&tid=85a75973-16b5
-44b9-8731-a176771b3353&mid=505324da-5176-4023-bab6-53b7536f6155

> One way, assuming the noncontiguous range is G7:G9,G12:G14
>
[quoted text clipped - 6 lines]
> > command.
> > Any ideas?

Reply to this Message

JMB - 30 Oct 2006 02:06 GMT

Or it could be modified

SUM(G7:G9,G12:G15)/(SUMPRODUCT(--(G7:G9<>0))+SUMPRODUCT(--(G12:G15<>0)))

> One way, assuming the noncontiguous range is G7:G9,G12:G14
>
[quoted text clipped - 6 lines]
> > command.
> > Any ideas?

Reply to this Message