formulas for averaging

arfonce

New Member
Joined
Sep 6, 2004
Messages
46
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,424
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
4
583.16667
676.4
795
832.8
92
108
115
123
134
145
158
167
172
185
192
200
210
227
231
241
253
26
Averages
 

arfonce

New Member
Joined
Sep 6, 2004
Messages
46
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
BCDE
3
418
535.25
624
715
857.333333
966
1064.666667
1177
1298.5
1336
144
157
168
179
184
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

New Member
Joined
Sep 6, 2004
Messages
46

ADVERTISEMENT

Aladin,

That did the trick-thanks.

Arfonce
 

arfonce

New Member
Joined
Sep 6, 2004
Messages
46
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

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

New Member
Joined
Sep 6, 2004
Messages
46
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
OOOps! I'm sorry for the typo, it was, of course, ctr+shift+enter.
I'm glad it's working now!
 

Forum statistics

Threads
1,136,349
Messages
5,675,244
Members
419,557
Latest member
razlevav

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top