Moving Averages

cfyock7

New Member
Joined
Sep 12, 2014
Messages
20
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

bbott

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
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

New Member
Joined
Sep 12, 2014
Messages
20
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

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
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

New Member
Joined
Sep 12, 2014
Messages
20

ADVERTISEMENT

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

bbott

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
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:

cfyock7

New Member
Joined
Sep 12, 2014
Messages
20

ADVERTISEMENT

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

bbott

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
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:

15
2
55.666667
365.666667
465.666667
177.666667
277.666667
387.666667
487.666667
199.666667
299.666667
3109.666667
4109.666667
11111.66667
21111.66667
31211.66667
41211.66667
11313.66667
21313.66667
31413.66667
41413.66667

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

Watch MrExcel Video

Forum statistics

Threads
1,114,652
Messages
5,549,209
Members
410,905
Latest member
Extjel
Top