AVG Ignore Zero - Non adjacent cells

**sabrinigreen** · 11-01-2011, 01:53 PM

Hello,
So, I need to do an average of cells:
WeeklySummary!BZ32:BZ36,WeeklySummary!DD32:DD36

but not include zeros in the average -

I tried
=AVERAGE(IF(WeeklySummary!BZ32:BZ36,WeeklySummary!DD32:DD36>0,WeeklySummary!BZ32:BZ36,WeeklySummary! DD32:DD36)) hitting ctrl shift enter and I get a msg saying I've entered too many arguments.

Meanwhile, in another cell, I am able to use:
=AVERAGE(IF(WeeklySummary!EH32:EH36>0,WeeklySummary!EH32:EH36)) hitting cntrl shift enter just fine - just can't seem to do it with multiple cell ranges.

And for the record, this is an average of an average, but the person who needs the data would rather have it that way since the amount percentage of total waste, which is total feet, minus actual feet divided by total feet. I'm open to setting it up to have the actual average, but there again, it would be adding a subtracting a large number of cells and ignoring blank or zero cells.

Thank you.

**NBVC** · 11-01-2011, 02:05 PM

Try:

=SUM(SUMIF(INDIRECT({"WeeklySummary!BZ32:BZ36","WeeklySummary!DD32:DD3"}),">0"))/SUM(COUNTIF(INDIRECT({"WeeklySummary!BZ32:BZ36","WeeklySummary!DD32:DD3"}),">0"))

**TMShucks** · 11-01-2011, 02:09 PM

I'd suggest you do two x SUMIF and two x COUNTIF greater than zero. Add the two SUMIFs and add the two COUNTIFs and divide the latter into the former.

Regards

**sabrinigreen** · 11-02-2011, 12:50 PM

Originally Posted by NBVC

Try:

=SUM(SUMIF(INDIRECT({"WeeklySummary!BZ32:BZ36","WeeklySummary!DD32:DD3"}),">0"))/SUM(COUNTIF(INDIRECT({"WeeklySummary!BZ32:BZ36","WeeklySummary!DD32:DD3"}),">0"))

This worked! thanks (I had to change the second DD cell reference to DD36, but yeah, it worked ;-)

**sabrinigreen** · 11-02-2011, 12:51 PM

Originally Posted by TMShucks

I'd suggest you do two x SUMIF and two x COUNTIF greater than zero. Add the two SUMIFs and add the two COUNTIFs and divide the latter into the former.

Regards

I kind of understand what you mean, but I am lost as to how to execute it.
Do you mean finding out the real value versus the average of the average?

Like adding all the total feet and adding all the actual feet, subtracting it and then dividing it by total feet? (that's how it is set up to determine each day's waste)

**NBVC** · 11-02-2011, 12:59 PM

Originally Posted by sabrinigreen

This worked! thanks (I had to change the second DD cell reference to DD36, but yeah, it worked ;-)

ooops.. i copied the reference without the final 6..

**sabrinigreen** · 11-02-2011, 01:35 PM

Originally Posted by NBVC

ooops.. i copied the reference without the final 6..

I totally appreciate your help ;-)

**TMShucks** · 11-02-2011, 05:46 PM

I guess I was just being a bit lazy. What I meant was, do two SUMIFs and add them together and two COUNTIFs and add them together, and then divide the total SUMIFs by the total COUNTIFs.

=(SUMIF(WeeklySummary!BZ32:BZ36,">0")+SUMIF(WeeklySummary!DD32:DD36,">0")) / (COUNTIF(WeeklySummary!BZ32:BZ36,">0")+COUNTIF(WeeklySummary!DD32:DD36,">0"))

Regards