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

 

[AhoyNC]

Try:

[TABLE="width: 1043"]
<colgroup><col width="1043"></colgroup><tbody>[TR]
   [TD="width: 1043"]=(SUMPRODUCT($B$1083:$B$1098,--(MOD(ROW($B$1083:$B$1098),4)-MOD(ROW(),4)=0)))/SUMPRODUCT(--(MOD(ROW($B$1083:$B$1098),4)-MOD(ROW(),4)=0))[/TD]
 [/TR]
</tbody>[/TABLE]

 

[sheetspread]

Excel 2010
	A	B
1	3.00%
2	74,150.00
3	70%
4	2,509,942.50
5	3.51%
6	59,750.00
7	50%
8	2,569,692.50
9	3.59%
10	54,350.00
11	43%
12	2,624,042.50
13	3.57%
14	53,000.00
15	42%
16	2,677,042.50
17
18		3.42%
Sheet18

Cell Formulas
Range	Formula
B18	{=AVERAGE(IF(MOD(ROW($A$1:$A$16)-1,4)=0,$A$1:$A$16))}
Press CTRL+SHIFT+ENTER to enter array formulas.

works too

 

[Ron Coderre]

Prefacing this suggestion by saying that I'm no fan of volatile functions (INDIRECT) and entire-column references....

With
A1: the start row.... 1083
A2: the end row..... 1098

This regular formula returns the average of the first item in each referenced quartet...

B1: =AVERAGE(INDEX(LOOKUP($A$1+(ROW(INDIRECT("1:"&(INT(($A$2-$A$1+1)/4)+1)))-1)*4,ROW(P:P),P:P),0))

 

[Ron Coderre]

Actually, I needed to remove the +1 from the INT section:

B1: =AVERAGE(INDEX(LOOKUP($A$1+(ROW(INDIRECT("1:"&(INT(($A$2-$A$1)/4)+1)))-1)*4,ROW(P:P),P:P),0))

 

[sheetspread]

2 other methods:

1. Put the modulo in a helper column then average from the pivot table (filtered)

or

2. If there's a consistent threshold (unlikely): {=AVERAGE(IF($A$2:$A$17<0.05,$A$2:$A$17))}

 

[Arie Bos]

Thank you AhoyNC, Spreadsheet and Ron,

I tried your suggestions on the following (actual) section of the column below with 17 pairs of 4 formula's. The average to be calculated is from the red numbers only, which should be 3.29%.

=(SUMPRODUCT($M$1000:$M$1067;--(MOD(ROW($M$1000:$M$1067);4)-MOD(ROW();4)=0)))/SUMPRODUCT(--(MOD(ROW($M$1000:$M$1067);4)-MOD(ROW();4)=0)) returned 19.93%

=AVERAGE(IF(MOD(ROW($M$1000:$M$1067)-1;4)=0;$M$1000:$M$1067)) returned 2660500%

=AVERAGE(INDEX(LOOKUP($O$1071+(ROW(INDIRECT("1:"&(INT(($O$1072-$O$1071)/4)+1)))-1)*4;ROW(P:P);P:P);0)) returned [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Value"]#Value[/URL] , but that is because I do not understand the last section of the formula, :eeek: ($O$1072 = M1000 and $O$1071 = M1067)

I did not realize how hard this would be ( to me, though, haha)

(Trying to get the table looking good, but it sows up wrong...)

[RANGE=cls:xl2bb-100][XR][XH=cs:2]Book1[/XH][/XR][XR][XH][/XH][XH]M[/XH][/XR][XR][XH]1000[/XH][XD=h:r|c:c00000]4.2%[/XD][/XR][XR][XH]1001[/XH][XD=h:r]49,775.00[/XD][/XR][XR][XH]1002[/XH][XD=h:r]39.7%[/XD][/XR][XR][XH]1003[/XH][XD=h:r]2,274,532.50[/XD][/XR][XR][XH]1004[/XH][XD=h:r|c:c00000]3.7%[/XD][/XR][XR][XH]1005[/XH][XD=h:r]55,900.00[/XD][/XR][XR][XH]1006[/XH][XD=h:r]46.9%[/XD][/XR][XR][XH]1007[/XH][XD=h:r]2,330,432.50[/XD][/XR][XR][XH]1008[/XH][XD=h:r|c:c00000]3.4%[/XD][/XR][XR][XH]1009[/XH][XD=h:r]-111,400.00[/XD][/XR][XR][XH]1010[/XH][XD=h:r]-100.0%[/XD][/XR][XR][XH]1011[/XH][XD=h:r]2,219,032.50[/XD][/XR][XR][XH]1012[/XH][XD=h:r|c:c00000]3.7%[/XD][/XR][XR][XH]1013[/XH][XD=h:r]46,225.00[/XD][/XR][XR][XH]1014[/XH][XD=h:r]37.3%[/XD][/XR][XR][XH]1015[/XH][XD=h:r]2,265,257.50[/XD][/XR][XR][XH]1016[/XH][XD=h:r|c:c00000]3.9%[/XD][/XR][XR][XH]1017[/XH][XD=h:r]44,100.00[/XD][/XR][XR][XH]1018[/XH][XD=h:r]35.0%[/XD][/XR][XR][XH]1019[/XH][XD=h:r]2,309,357.50[/XD][/XR][XR][XH]1020[/XH][XD=h:r|c:c00000]3.9%[/XD][/XR][XR][XH]1021[/XH][XD=h:r]49,635.00[/XD][/XR][XR][XH]1022[/XH][XD=h:r]39.6%[/XD][/XR][XR][XH]1023[/XH][XD=h:r]2,358,992.50[/XD][/XR][XR][XH]1024[/XH][XD=h:r|c:c00000]3.1%[/XD][/XR][XR][XH]1025[/XH][XD=h:r]66,200.00[/XD][/XR][XR][XH]1026[/XH][XD=h:r]63.8%[/XD][/XR][XR][XH]1027[/XH][XD=h:r]2,425,192.50[/XD][/XR][XR][XH]1028[/XH][XD=h:r|c:c00000]3.6%[/XD][/XR][XR][XH]1029[/XH][XD=h:r]47,925.00[/XD][/XR][XR][XH]1030[/XH][XD=h:r]39.3%[/XD][/XR][XR][XH]1031[/XH][XD=h:r]2,473,117.50[/XD][/XR][XR][XH]1032[/XH][XD=h:r|c:c00000]3.3%[/XD][/XR][XR][XH]1033[/XH][XD=h:r]61,150.00[/XD][/XR][XR][XH]1034[/XH][XD=h:r]53.7%[/XD][/XR][XR][XH]1035[/XH][XD=h:r]2,534,267.50[/XD][/XR][XR][XH]1036[/XH][XD=h:r|c:c00000]2.7%[/XD][/XR][XR][XH]1037[/XH][XD=h:r]-91,850.00[/XD][/XR][XR][XH]1038[/XH][XD=h:r]-100.0%[/XD][/XR][XR][XH]1039[/XH][XD=h:r]2,442,417.50[/XD][/XR][XR][XH]1040[/XH][XD=h:r|c:c00000]3.3%[/XD][/XR][XR][XH]1041[/XH][XD=h:r]53,450.00[/XD][/XR][XR][XH]1042[/XH][XD=h:r]45.9%[/XD][/XR][XR][XH]1043[/XH][XD=h:r]2,495,867.50[/XD][/XR][XR][XH]1044[/XH][XD=h:r|c:c00000]3.7%[/XD][/XR][XR][XH]1045[/XH][XD=h:r]41,975.00[/XD][/XR][XR][XH]1046[/XH][XD=h:r]32.8%[/XD][/XR][XR][XH]1047[/XH][XD=h:r]2,537,842.50[/XD][/XR][XR][XH]1048[/XH][XD=h:r|c:c00000]2.9%[/XD][/XR][XR][XH]1049[/XH][XD=h:r]-102,050.00[/XD][/XR][XR][XH]1050[/XH][XD=h:r]-100.0%[/XD][/XR][XR][XH]1051[/XH][XD=h:r]2,435,792.50[/XD][/XR][XR][XH]1052[/XH][XD=h:r|c:c00000]3.0%[/XD][/XR][XR][XH]1053[/XH][XD=h:r]74,150.00[/XD][/XR][XR][XH]1054[/XH][XD=h:r]70.1%[/XD][/XR][XR][XH]1055[/XH][XD=h:r]2,509,942.50[/XD][/XR][XR][XH]1056[/XH][XD=h:r|c:c00000]3.5%[/XD][/XR][XR][XH]1057[/XH][XD=h:r]59,750.00[/XD][/XR][XR][XH]1058[/XH][XD=h:r]49.7%[/XD][/XR][XR][XH]1059[/XH][XD=h:r]2,569,692.50[/XD][/XR][XR][XH]1060[/XH][XD=h:r|c:c00000]3.6%[/XD][/XR][XR][XH]1061[/XH][XD=h:r]54,350.00[/XD][/XR][XR][XH]1062[/XH][XD=h:r]43.3%[/XD][/XR][XR][XH]1063[/XH][XD=h:r]2,624,042.50[/XD][/XR][XR][XH]1064[/XH][XD=h:r|c:c00000]3.6%[/XD][/XR][XR][XH]1065[/XH][XD=h:r]53,000.00[/XD][/XR][XR][XH]1066[/XH][XD=h:r]41.7%[/XD][/XR][XR][XH]1067[/XH][XD=h:r]2,677,042.50[/XD][/XR][XR][XH=cs:2][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[Aladin Akyurek]

Book1
	P	Q	R
1083	3.00%		0.034175
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
Sheet1

Control+shift+enter, not just enter:

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

Note 1. Change comma to semi-colon for your system.
Note 2. The suggested formula is robust agains inserting rows in front of the current data.

 

[Ron Coderre]

That range you posted is in Col_M.
For the LOOKUP formula I posted...If you want to calculate the Row 1000 through 1067 range...try this
Where
O1071: 1000
O1072: 1067

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

Does that help?

Off-topic: I copied the html code from your post...Pasted it into Notepad...Saved the file as MyFile.html
...Opened it in a web browser

 

[Arie Bos]

It works!

Thank you Aladin and Ron (again). Makes my life easier and chances on mistakes much smaller.
Everybody thanks for the great help!

@Ron, good trick to paste into Notepad. I tried several times and one time it showed OK in the preview post, but not in the final one. May be the table had too many rows. ( I made it with MrExcel HTML maker).

best regards,

Arie