Moving Average

C

cunningAce

Board Regular
Joined
Dec 21, 2017
Messages
91
Office Version
  1. 365
Platform
  1. Windows
Hi,

Hoping someone could help me with the following please,

I am looking to find the average of a range of numbers (determined by the variable in the 'weeks average' cell)

So in the below examples, I want the average of the last 5 weeks for each set of numbers.
I want to ignore any zeros after the last weeks figures (week 8 in these examples) but include any zeros within the 5 week average,

Eg,
The first line would calculate an average for weeks 4-8 - (0,10,50,60,100 = 44)
The 3rd line would also calculate weeks 4-8 ignoring anything before, and any zeros after week 8. (5,10,50,60,200 = 65)

Hope this makes sense,


Thanks in advance,


BCDEFGHIJKL

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: center"]Week 1[/TD]
[TD="align: center"]Week 2[/TD]
[TD="align: center"]Week 3[/TD]
[TD="align: center"]Week 4[/TD]
[TD="align: center"]Week 5[/TD]
[TD="align: center"]Week 6[/TD]
[TD="align: center"]Week 7[/TD]
[TD="align: center"]Week 8[/TD]
[TD="align: center"]Week 9[/TD]
[TD="align: center"]Week 10[/TD]
[TD="align: center"]Average[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"]44[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"]45[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"]65[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]Weeks Average[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​
[TABLE="width: 85%"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 
Last edited:
Sort by date Sort by votes

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Book1
BCDEFGHIJKL
3Week 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10Average
400001050601000044
500051050601000045
624651050602000065
7
8
9Weeks Average5
Sheet2
Cell Formulas
RangeFormula
L4{=AVERAGE(OFFSET(B4:INDEX(B4:K4,LARGE(IF(B4:K4<>0,COLUMN(B4:K4)-COLUMN(B4)+1),1)),0,LARGE(IF(B4:K4<>0,COLUMN(B4:K4)-COLUMN(B4)+1),1)-$C$9,1,$C$9))}
L5{=AVERAGE(OFFSET(B5:INDEX(B5:K5,LARGE(IF(B5:K5<>0,COLUMN(B5:K5)-COLUMN(B5)+1),1)),0,LARGE(IF(B5:K5<>0,COLUMN(B5:K5)-COLUMN(B5)+1),1)-$C$9,1,$C$9))}
L6{=AVERAGE(OFFSET(B6:INDEX(B6:K6,LARGE(IF(B6:K6<>0,COLUMN(B6:K6)-COLUMN(B6)+1),1)),0,LARGE(IF(B6:K6<>0,COLUMN(B6:K6)-COLUMN(B6)+1),1)-$C$9,1,$C$9))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
How about (with CSE):

=AVERAGE(INDEX(B4:K4,N(IF(1,MATCH(1,0/B4:K4)-{0,1,2,3,4}))))

Regards
 
Upvote 0
Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][td="bgcolor: #DCE6F1"]
H​
[/td][td="bgcolor: #DCE6F1"]
I​
[/td][td="bgcolor: #DCE6F1"]
J​
[/td][td="bgcolor: #DCE6F1"]
K​
[/td][td="bgcolor: #DCE6F1"]
L​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
Week 1​
[/td][td]
Week 2​
[/td][td]
Week 3​
[/td][td]
Week 4​
[/td][td]
Week 5​
[/td][td]
Week 6​
[/td][td]
Week 7​
[/td][td]
Week 8​
[/td][td]
Week 9​
[/td][td]
Week 10​
[/td][td]
Average​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
0​
[/td][td]
0​
[/td][td]
0​
[/td][td]
0​
[/td][td]
10​
[/td][td]
50​
[/td][td]
60​
[/td][td]
100​
[/td][td]
0​
[/td][td]
0​
[/td][td]
44​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
0​
[/td][td]
0​
[/td][td]
0​
[/td][td]
5​
[/td][td]
10​
[/td][td]
50​
[/td][td]
60​
[/td][td]
100​
[/td][td]
0​
[/td][td]
0​
[/td][td]
45​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
2​
[/td][td]
4​
[/td][td]
6​
[/td][td]
5​
[/td][td]
10​
[/td][td]
50​
[/td][td]
60​
[/td][td]
200​
[/td][td]
0​
[/td][td]
0​
[/td][td]
65​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
Weeks Average​
[/td][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in L4 copied down
=AVERAGE(OFFSET(INDEX(B4:K4,MATCH(9.99E+307,IF(B4:K4>0,B4:K4,""))),,,,-C$9))
Ctrl+Shift+Enter

M.
 
Upvote 0
To avoid #REF errors, when the relative column of the last number > 0 is less than C9, try this new version


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][td="bgcolor: #DCE6F1"]
H​
[/td][td="bgcolor: #DCE6F1"]
I​
[/td][td="bgcolor: #DCE6F1"]
J​
[/td][td="bgcolor: #DCE6F1"]
K​
[/td][td="bgcolor: #DCE6F1"]
L​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
Week 1​
[/td][td]
Week 2​
[/td][td]
Week 3​
[/td][td]
Week 4​
[/td][td]
Week 5​
[/td][td]
Week 6​
[/td][td]
Week 7​
[/td][td]
Week 8​
[/td][td]
Week 9​
[/td][td]
Week 10​
[/td][td]
Average​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
0​
[/td][td]
0​
[/td][td]
0​
[/td][td]
0​
[/td][td]
10​
[/td][td="bgcolor:#DDD9C4"]
50​
[/td][td="bgcolor:#DDD9C4"]
60​
[/td][td="bgcolor:#DDD9C4"]
100​
[/td][td="bgcolor:#DDD9C4"]
0​
[/td][td="bgcolor:#DDD9C4"]
80​
[/td][td]
58​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
0​
[/td][td]
0​
[/td][td]
0​
[/td][td="bgcolor:#DDD9C4"]
5​
[/td][td="bgcolor:#DDD9C4"]
10​
[/td][td="bgcolor:#DDD9C4"]
50​
[/td][td="bgcolor:#DDD9C4"]
60​
[/td][td="bgcolor:#DDD9C4"]
100​
[/td][td]
0​
[/td][td]
0​
[/td][td]
45​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
2​
[/td][td]
4​
[/td][td]
6​
[/td][td]
5​
[/td][td="bgcolor:#DDD9C4"]
10​
[/td][td="bgcolor:#DDD9C4"]
50​
[/td][td="bgcolor:#DDD9C4"]
60​
[/td][td="bgcolor:#DDD9C4"]
200​
[/td][td="bgcolor:#DDD9C4"]
60​
[/td][td]
0​
[/td][td]
76​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td="bgcolor:#DDD9C4"]
2​
[/td][td="bgcolor:#DDD9C4"]
0​
[/td][td="bgcolor:#DDD9C4"]
7​
[/td][td]
0​
[/td][td]
0​
[/td][td]
0​
[/td][td]
0​
[/td][td]
0​
[/td][td]
0​
[/td][td]
0​
[/td][td]
3​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
Weeks Average​
[/td][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in L4 copied down
=AVERAGE(OFFSET(INDEX(B4:K4,MATCH(9.99E+307,IF(B4:K4>0,B4:K4,""))),,,,MAX(-MATCH(9.99E+307,IF(B4:K4>0,B4:K4,"")),-C$9)))
Ctrl+Shift+Enter

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
YTD and Period Averages? Possible averageif with index/match?
Replies
2
Views
26
reacon84
R
S
  • Solved
Formula to calculate average and not include blanks or zeros in the calculation
Replies
4
Views
622
etaf
etaf
T
  • Question Question
Moving average last 5 numeric number, N/A for any zero cells and the omitting that for next moving average
Replies
3
Views
327
Cubist
Cubist
K
  • Question Question
Compress consecutive numbers
Replies
9
Views
371
Fuji
F
T
  • Question Question
Running Average of last n row if sum is between a certain value
Replies
1
Views
71
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

Share this page

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
Back
Top