calculate average percentage of every 4th cell

[Arie Bos]

I have a column with 4 recurring formula's that repeat every 4th cell. Every 1st of 4 creates a percentage A: 3.00%, 3.51%, 3.59% and 3.57%. This column has over a 1000 of these pairs of 4 formulas. How can I calculate the average 3.42% of a range of this column (in this section below, between row 1083 and 1098)?
I tried :

=SUMPRODUCT((MOD(ROW(P1083:P1098);4)=0)*(P1083:P1098))/INT(ROWS(P1083:P1098)/4)

But that creates wrong results.​

​

1083 3.00%
1084 74,150.00
1085 70%
1086 2,509,942.50
1087 3.51%
1088 59,750.00
1089 50%
1090 2,569,692.50
1091 3.59%
1092 54,350.00
1093 43%
1094 2,624,042.50
1095 3.57%
1096 53,000.00
1097 42%
1098 2,677,042.50

 

[Arie Bos]

Hi Ron,

one more question on your magic formula....
If I also want to calc the ave of every 3rd item in the same column, instead of the first.. Which parameter in the formula should then change?

thanks,
Arie

 

[Ron Coderre]

If you want flexibility in the ordinal to average, try this (using my above example)

O1071: First row to include........ 1000
O1072: Last row to include......... 1067
O1073: The item number to include..    3

Then use this formula:

=AVERAGE(INDEX(LOOKUP($O$1071+(O1073-1)+(ROW(INDIRECT("1:"&(INT(($O$1072-$O$1071)/4)+1)))-1)*4,ROW(M:M),M:M),0))

In that situation, the 3rd item in every group of 4 will be averaged.

Is that something you can work with?

 

[Aladin Akyurek]

Hi Ron,

one more question on your magic formula....
If I also want to calc the ave of every 3rd item in the same column, instead of the first.. Which parameter in the formula should then change?

thanks,
Arie

Control+shift+enter, not just enter:

=AVERAGE(IF(MOD(ROW(P1083:P1098)-ROW(P1083),3)=0,P1083:P1098))​

which is robust against row insertions in front of the current row and not volatile.

​