Moving Averages

[cfyock7]

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.

Thanks in advance

 

[bbott]

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

 

[cfyock7]

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

 

[bbott]

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

 

[cfyock7]

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

 

[bbott]

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

 

[cfyock7]

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

 

[bbott]

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>

 

[cfyock7]

It worked!

 

[bbott]

It worked!

Thanks for the feedback, glad it worked for you.