# formulas for averaging

#### arfonce

##### New Member
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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

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))

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.

That did the trick-thanks.

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.

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

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.

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

Replies
5
Views
183
Replies
5
Views
163
Replies
1
Views
293
Replies
2
Views
272
Replies
6
Views
315

1,214,438
Messages
6,119,523
Members
448,904
Latest member
uNiek

### 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.

### Which adblocker are you using?

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

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