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.

 

[kvsrinivasamurthy]

Try this

=SUMPRODUCT((MOD(ROW(C$2:C$100)-1,5)=0)*(C$2:C$100))/SUMPRODUCT((C$2:C$100<>"")*(MOD(ROW(C$2:C$100)-1,5)=0))

 

[Peter h]

Don't know if there is a way if there is a way to modify the formula, so if anyone knows of a way to do it, I'd love to hear it, but here is a work-around that I did.

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

 

[Peter h]

Try this

=SUMPRODUCT((MOD(ROW(C$2:C$100)-1,5)=0)*(C$2:C$100))/SUMPRODUCT((C$2:C$100<>"")*(MOD(ROW(C$2:C$100)-1,5)=0))

Thanks much. I'll play around with it.

 

[joeu2004]

{=AVERAGE(IF((MOD(ROW(C$2:C$100)-1,5)=0)*(C$2:C$100),C$2:C$100))}
[....] How can I modify this formula to count all cells that have a value, even if that value is 0?

The problem is: you overspecified the IF() condition. MOD(ROW(C$2:C$100)-1,5)=0 says "every 5th row starting with C6. "*C2:C100" says "but exclude when the value is zero".

So simply omit the term "*(C$2:C$100)", and simplify the syntax. Array-enter the following:

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

 

[Peter h]

The problem is: you overspecified the IF() condition. MOD(ROW(C$2:C$100)-1,5)=0 says "every 5th row starting with C6. "*C2:C100" says "but exclude when the value is zero".

So simply omit the term "*(C$2:C$100)", and simplify the syntax. Array-enter the following:

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

I really appreciate the explanation. I’ll admit, I did somegoogling and found a formula that I was able to adapt to fit my application,but don’t really understand how it works. Would you mind explaining a bit how MOD(ROW(C$2:C$100)-1,5)=0 says "every5th row starting with C6”? From what I’ve read about the MOD and ROW functions,I’m having a hard time understanding how it is referencing “C6”. The “-1” was a guess on my part to get it toreference the correct cell, because without it it was referencing C5, eventhough I’m specifying to begin 5 cells from C2 (Just my thought process,without really understanding the functions).

 

[Aladin Akyurek]

It should be:

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

Not

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

for the robustness' sake.

By the way, what would be the average for the following...

Book1
	C
2	3
3	2
4	5
5	2
6	1
7	4
8	6
9	3
10	2
11
12	1
13	4
14	2
15	1
16	6
Sheet1

you look for?

 

[joeu2004]

It should be:
MOD(ROW(C$2:C$100)-ROW(C$2),5)=0

I agree with you in principle. That guards against selecting the wrong cells if we move the range C2:C100.

However, your change is incorrect for Peter's purposes.

In an array-entered formula, ROW(C$2:C$100)-ROW(C$2) returns {0,1,2,3,4,5,6,...,98}. Thus, MOD(...)=0 returns {TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,...,FALSE}.

In the array-entered formula =AVERAGE(IF(MOD(...)=0,C$2:C$100)), that would select C2, C7, etc.

But Peter indicated that he wants to select C6, C11, etc.

Your expression could be corrected for Peter's purposes, namely: MOD(ROW(C$2:C$100)-ROW(C$2)+1,5)=0.

 

[joeu2004]

Would you mind explaining a bit how MOD(ROW(C$2:C$100)-1,5)=0 says "every5th row starting with C6”?

In an array-entered formula, ROW(C$2:C$100) returns the array {2,3,4,5,6,7,...,100} because you start with C$2.

As we'll see below, you want the array {1,2,3,4,5,6,...,99}. Hence, we can write MOD(ROW(C$2:C$100)-1,5).

Alternatively, we could write MOD(ROW(C$1:C$99)). But that is probably confusing, since you want to average selected cells in C2:C100.

Moreover, in principle, it is prudent to use the same range that the rest of the formula depends on in order to avoid needless recalculation when the referenced range is modified. Otherwise, if we wrote ROW(X$1:X$99), for example, the formula and any dependent formulas would be needlessly recalculated if any cell in X1:X99 were edited or recalculated.

MOD(x,y) returns zero when x is a multiple of y. So in an array-entered formula, MOD(ROW(C$2:C$100)-1,5) returns the array {FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,...,FALSE}.

That does not refer to C2, C3 etc directly.

But your formula selects the corresponding element from C$2:C$100, the range in the second parameter, whenever the conditional expression in the first parameter is TRUE. So effectively, your array-entered formula becomes:

=AVERAGE(IF({FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,...,FALSE}, (C2,C3,C4,C5,C6,...,C100)))

Thus, it selects C6, C11 etc for the average.

In contrast, your array-entered formula would select X13, X18 etc if it were =AVERAGE(IF(MOD(ROW(C$2:C$100)-1,5)=0,X9:X100). But again, that would be confusing, and it might cause needless recalculations. In that formula, ROW(X$9:X$100)-8 is the better choice, IMHO; or with Aladin's suggestion, corrected: ROW(X$9:X$100)-ROW(X$9)+1. Again, the only purpose is to return the array {1,2,3,4,5,6,...,99} in order to select every 5th cell starting with the X13, the 5th cell in the range.

 

[joeu2004]

Array-enter the following:
=AVERAGE(IF(MOD(ROW(C$2:C$100)-1,5)=0,C$2:C$100))

That corrects the unintended exclusion of zero values.

However, that causes empty cells (or cells that appear to be blank) to be treated as zeros, which will affect the average.

I suspect that is unintended. So array-enter the following in order to include zero values in the average, but exclude "blank" cells.

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

Following your original paradigm, that could be written as follows:

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

But I suspect that the nested IF() form is more efficient (unproven) -- although arguably, it probably makes no perceptible difference in this example.