formulas for averaging

[arfonce]

Column B is filled with numbers from b5 to b473. Each week a new number is added to the bottom. I need a formula to average every 4th number starting with b5. In other words, I need to average b5,b9,b13, and so on. Need to do the same with next set-b6,b10,b14,etc.
Any help is appreciated.

 

[Peter_SSs]

arfonce

See if this is what you want. I have used the range B5:B25 but you can increase the range to suit your needs.

Formula in D5 (copied down to D8):
=AVERAGE(IF(MOD(ROW($B$5:$B$25),4)=MOD(ROWS(D$5:D5),4),$B$5:$B$25))
which must be confirmed with Ctrl+Shift+Enter not just Enter

Mr Excel.xls
	A	B	C	D	E
4
5		8		3.16667
6		7		6.4
7		9		5
8		3		2.8
9		2
10		8
11		5
12		3
13		4
14		5
15		8
16		7
17		2
18		5
19		2
20		0
21		0
22		7
23		1
24		1
25		3
26
Averages

 

[arfonce]

Peter,

Thanks for helping. That is what I'm trying to do. I copied the formula you had in d5,pasted it ,copied it down to d8,punched ctrl,shift,and enter at the same time,but nothing happened. The result in d5 is a little off(should be 94.57(567.40/6) but it gives 93.55 instead) and d6,d7,and d8 have 0's in them.
Also, the formula in d5 doesn't have{} around it.
Is that the problem? Here's the formula in d5:

=AVERAGE(IF(MOD(ROW($B$5:$B$25),4)=MOD(ROWS(D$5:D5),4),$B$5:$B$25))

 

[Aladin Akyurek]

Column B is filled with numbers from b5 to b473. Each week a new number is added to the bottom. I need a formula to average every 4th number starting with b5. In other words, I need to average b5,b9,b13, and so on. Need to do the same with next set-b6,b10,b14,etc.
Any help is appreciated.

aaRunningAverageOfEvery4th arfonce.xls
	B	C	D	E
3
4			18
5	3		5.25
6	2		4
7	1		5
8	5		7.333333
9	6		6
10	6		4.666667
11	7		7
12	9		8.5
13	3		6
14	4
15	7
16	8
17	9
18	4
19
Sheet1

D4:

=MAX(ROW(B5),MATCH(9.99999999999999E+307,B:B))

D5:

=AVERAGE(IF(MOD(ROW(B5:INDEX(B:B,$D$4))-ROW(B5),4)=0,IF(ISNUMBER(B5:INDEX(B:B,$D$4)),B5:INDEX(B:B,$D$4))))

which is confirmed with control+shift+enter (not just with enter) then copied down.

 

[arfonce]

Aladin,

That did the trick-thanks.

Arfonce

 

[arfonce]

Upon further review the results aren't correct. They were close to what I was expecting but after checking them manually I find that they're not correct. When I paste the formula into d5 the incorrect result shows up and then when I press ctrl,shift,enter nothing happens.

The incorrect answer in d5 is actually the average of all the cells from b5 on down-not every 4th cell. D6 has the average of b5 on down.

The example that Aladin gave does contain the correct results.

Am I having a problem with entering an array formula?

Thanks in advance for any help.

 

[pgc01]

Hi arfonce

Am I having a problem with entering an array formula?

Yes, that seems to be the case.

It's very easy to check. If you entered the formula as an array formula then excel will display it between curly braces.

Check Aladin's formula in the formula bar. You should see

{=AVERAGE(IF(MOD(ROW(B5:INDEX(B:B,$D$4))-ROW(B5),4)=0,IF(ISNUMBER(B5:INDEX(B:B,$D$4)),B5:INDEX(B:B,$D$4))))}

Those curly braces at the beginning and at the end is what tells you that the formula was entered correctly. You cannot enter them yourself or it won't even be recognized as a formula.

So, press both CTRL and ALT, keep them both down and hit ENTER.

Hope this helps
PGC

 

[arfonce]

pcg01

Thanks for offering to help. Instead of copying and pasting I manually entered the formula and pressed ctrl,shift and enter instead of ctrl,alt, and enter. That worked. It didn't work when I copied and pasted the formula though.

 

[pgc01]

OOOps! I'm sorry for the typo, it was, of course, ctr+shift+enter.
I'm glad it's working now!