AVERAGEIF without counting zero's

**jlr4** · 08-24-2011, 05:32 PM

I'm trying to average a value in b2 plus a different sheet (bills) c2:ab2 and not average in cells that contain a zero. Here is how I have the formula:
=averageif(b2,bills!c2:ab2,"<>0") what I get is a dialog box saying error in formula and it highlights b2 in the formula. If I remove b2 from the formula it works.
=averageif(bills!c2:ab2,"<>0") . Any idea on whats going on?

jim

**Bob Phillips** · 08-24-2011, 05:36 PM

Yeah, the first form is wrong, the second is correct.

How can you average a value, you average an array.

**Colin Legg** · 08-24-2011, 05:36 PM

Hi Jim,

Does this work for you?

Please Login or Register  to view this content.

**abousetta** · 08-24-2011, 05:41 PM

Maybe something like this:

Please Login or Register  to view this content.

Warning: If there are cells that are empy then the formula will give you the wrong result.

abousetta

**abousetta** · 08-24-2011, 05:45 PM

I would go with Colin's approach. Works regardless of whether there are blank cells or not.

abousetta

**jlr4** · 08-25-2011, 10:40 AM

Thanks everyone, I used Colin's approach and it worked perfect. I spent many hours trying to figure this out, don't know why I was so stuck on AVERAGEIF. I didn't get to abousetta's reply but I will.

Best regards
jim