Can I get some help with this AVERAGE formula?

[Peter h]

I need to get the average of every 5th cell in column "C", so I'm using This Formula:

{=AVERAGE(IF((MOD(ROW(C$2:C$100)-1,5)=0)*(C$2:C$100),C$2:C$100))}

It seems to work exactly how I want it, unless a cell contains a value of 0. If the cell contains a value of 0, the formula doesn't count that cell in the average. How can I modify this formula to count all cells that have a value, even if that value is 0?

In testing this, I did just a normal average formula:

=Average(C6,C11,C16,...)

And I got the correct answer.

Thanks for any help.

 

[Aladin Akyurek]

@ joeu2004

FYI, I invented myself the approach to every nth as:

(a) MOD(ROW(C$2:C$100)-ROW(C$2),5)=0 [act starting with the first cell of the reference]

and

(b) MOD(ROW(C$2:C$100)-ROW(C$2)+1,5)=0 [act strating with the first nth cell of the reference]

eons ago.

If the OP means with

MOD(ROW(C$2:C$100)-1,5)=0

the (b) option or not, he can clarify that using the sample I posted.