Moving Averages

cfyock7

New Member
I need the values that correspond to 2-4 averaged so

1 5 5.67
2 5 5.67
3 6 5.67
4 6 5.67

1 7 7.67
2 7 7.67
3 8 7.67
4 8 7.67

I need to figure out what forumla to plug into column C that would get the average of the coresponding values in this case 5,6,7 and 8 to be placed in all 4 cells in C if my above example makes more sense.

I have 47454 lines in this excel sheet and I really do not want to try and average every single cell.

I have some VBA knowledge but not enough to create this code solo.

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Not sure I completely follow, but assuming your first table starts in A1, try this in C1:

=AVERAGEIFS(\$B\$1:\$B\$4,\$A\$1:\$A\$4,">=2",\$A\$1:\$A\$4,"<=4")

okay and i would have to adjust that for every proceeding table correct? the 1-4 sequence repeats multiple times so i was hoping i could find something that I could put in the first cell and have it carry all the way down to the end

Are there blank lines between the sequences?

And will the sequence always be 1,2,3,4 (i.e. you wouldn't have to match 2,3,4, but rather do the 2nd through 4th of each)?

There are no blanks and yes it is the same spots each time

Try this in C2 and copy down:

=AVERAGE(INDEX(\$B\$1:\$B\$50000,INT(ROW(A1)/4)*4+2,1):INDEX(\$B\$1:\$B\$50000,INT(ROW(A1)/4)*4+4,1))

Last edited:
It worked for the first set but once we got to the second it got messed up

It worked for the first set but once we got to the second it got messed up

I edited the formula after I originally posted it. Try the revision. Make sure you put it in C2, not C1. The only down side is that C1 would have to be manually entered or a different formula. These are the results I get:

 1 5 2 5 5.666667 3 6 5.666667 4 6 5.666667 1 7 7.666667 2 7 7.666667 3 8 7.666667 4 8 7.666667 1 9 9.666667 2 9 9.666667 3 10 9.666667 4 10 9.666667 1 11 11.66667 2 11 11.66667 3 12 11.66667 4 12 11.66667 1 13 13.66667 2 13 13.66667 3 14 13.66667 4 14 13.66667

<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>
</tbody>

Last edited:
It worked!

Replies
10
Views
591
Replies
2
Views
124
Replies
5
Views
216
Replies
5
Views
161
Replies
9
Views
145

1,217,676
Messages
6,137,934
Members
450,099
Latest member
Pushbutton

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.

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