Need help with a formula that will give me the average of the prior 4 weeks

P

Panama1182

New Member
Joined
Apr 3, 2018
Messages
23
My data set is as follows:
In columns starting on C3 I have week ending dates starting with 1/6/2018 all the way to column BB3. on Column C5 i have the week number starting at 1 going all the way to column BB5.
C6:M6 contains my data. I need a formula that will average the prior 4 weeks. I need a formula that will average I6:L6 and shift accordingly moving forward.
On cell BD4 i list the current week number and BD5 i list the week ending date based on the week number. I want a dynamic formula that will average the prior 4 weeks based on what is listed on BD5.

So for example, if BD5 says it is 3/17/2018 then i would like to average the prior 4 weeks (I6:L6) and when BD5 changes to 3/24/2018 i want the formula to average the prior 4 weeks (J6:M6)

Please help.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Try
Code: 
=AVERAGE(OFFSET(INDEX(C5:BB5,MATCH(BD4,C5:BB5,0)),0,-4):INDEX(C5:BB5,MATCH(BD4,C5:BB5,0)-1))
 
Upvote 0
[TABLE="width: 500"]
<tbody>[TR]
[TD]1/6/2018[/TD]
[TD]1/13/2018[/TD]
[TD]1/20/2018[/TD]
[TD]1/27/2018[/TD]
[TD]2/3/2018[/TD]
[TD]2/10/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]905[/TD]
[TD]922[/TD]
[TD]940[/TD]
[TD]951[/TD]
[TD]965[/TD]
[TD]967[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

in this example the prior 4 week average if the current week was 2/10/2018 would be 944.5 which is the result of averaging 922,940,951,and 965. I need a formula that will calculate the prior 4 week average as new data comes in.
 
Upvote 0
Panama1182 said:
My data set is as follows:
In columns starting on C3 I have week ending dates starting with 1/6/2018 all the way to column BB3. on Column C5 i have the week number starting at 1 going all the way to column BB5.
C6:M6 contains my data. I need a formula that will average the prior 4 weeks. I need a formula that will average I6:L6 and shift accordingly moving forward.
On cell BD4 i list the current week number and BD5 i list the week ending date based on the week number. I want a dynamic formula that will average the prior 4 weeks based on what is listed on BD5.
So for example, if BD5 says it is 3/17/2018 then i would like to average the prior 4 weeks (I6:L6) and when BD5 changes to 3/24/2018 i want the formula to average the prior 4 weeks (J6:M6)
Please help.
Click to expand...

Hi!

Try the formulas below too:

In BD4

=MATCH(BD5,$C$3:$BB$3,0)

In BD6

=AVERAGE(OFFSET($C$6,0,BD4-2,1,-4))

In BD7

=AVERAGE(INDEX($C$6:$BB6,BD4-4):INDEX($C$6:$BB6,BD4-1))


[TABLE="class: grid, width: 1403"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]BB[/TD]
[TD]BC[/TD]
[TD]BD[/TD]
[TD]BE[/TD]
[TD]BF[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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]3[/TD]
[TD][/TD]
[TD="align: right"]06/01/2018[/TD]
[TD="align: right"]13/01/2018[/TD]
[TD="align: right"]20/01/2018[/TD]
[TD="align: right"]27/01/2018[/TD]
[TD="align: right"]03/02/2018[/TD]
[TD="align: right"]10/02/2018[/TD]
[TD="align: right"]17/02/2018[/TD]
[TD="align: right"]24/02/2018[/TD]
[TD="align: right"]03/03/2018[/TD]
[TD="align: right"]10/03/2018[/TD]
[TD="align: right"]17/03/2018[/TD]
[TD="align: right"]24/03/2018[/TD]
[TD="align: right"]31/03/2018[/TD]
[TD="align: right"]29/12/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD]Week[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]52[/TD]
[TD][/TD]
[TD="align: right"]24/03/2018[/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]901[/TD]
[TD="align: right"]902[/TD]
[TD="align: right"]906[/TD]
[TD="align: right"]912[/TD]
[TD="align: right"]918[/TD]
[TD="align: right"]926[/TD]
[TD="align: right"]934[/TD]
[TD="align: right"]937[/TD]
[TD="align: right"]942[/TD]
[TD="align: right"]948[/TD]
[TD="align: right"]901[/TD]
[TD="align: right"]908[/TD]
[TD="align: right"]954[/TD]
[TD][/TD]
[TD="align: right"]940,25[/TD]
[TD]Result[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]940,25[/TD]
[TD]Result[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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]***[/TD]
[TD]**[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]**[/TD]
[TD]**********[/TD]
[TD]*******[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Average Weekly Sales before and after an event.
Replies
4
Views
312
hagia_sofia
hagia_sofia
O
  • Question Question
Calculating Average # of Procedures per Week
Replies
2
Views
263
RobP
R
R
  • Question Question
Looking for help w Excel formula
Replies
2
Views
82
Dave Patton
D
G
  • Question Question
Sales - Count Weeks on Offer (Retail)
Replies
3
Views
200
awoohaw
awoohaw
D
  • Solved
VLOOKUP or INDEX/MATCH utilizing day of the week
Replies
5
Views
472
Darren_workforce
D

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

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