7-Day Rolling Average, Based on Date in P1

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
370
Office Version
  1. 2016
Hi

In Column P I have a date (ie 1 March 2021)

In column B (starting in B4:B100) - counting downwards, so the most recent date is in B4 - 23 March 2021 to B100 21 December 2020)

In Column C I have figures corresponding to those dates

I want it to look up the date in P1, and then reference that to column B's dates, and then use the figure in Column C and calculate the average

P101 March 2021AVERAGE :25
(Average of 10, 18, 36, 42, 37, 19, 13)


B4
23 March 202142
B522 March 202136
..............
B2701 March 202110
B2828 February 202118
B2927 February 202136
B3026 February 202142
B3125 February 202137
B3224 February 202119
B3323 February 202113
B3422 February 202117
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,601
Office Version
  1. 365
Platform
  1. Windows
This is how I would do it in office 365.
Excel Formula:
=AVERAGE(INDEX(C4:C100,SEQUENCE(7,,MATCH(P1,B4:B100,-1))))
If you have an older version of excel then you will need a different formula. Please click on your username at the top right of the page then update your account details to show the correct version of excel that you are using.
 

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
370
Office Version
  1. 2016
This is how I would do it in office 365.
Excel Formula:
=AVERAGE(INDEX(C4:C100,SEQUENCE(7,,MATCH(P1,B4:B100,-1))))
If you have an older version of excel then you will need a different formula. Please click on your username at the top right of the page then update your account details to show the correct version of excel that you are using.
Hi - apologies updated - am on Excel 2016
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,601
Office Version
  1. 365
Platform
  1. Windows
In that case, try
Excel Formula:
=AVERAGE(OFFSET($C$4,MATCH(P1,$B$4:$B$100,-1)-1,0,7,1))
 
Solution

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
370
Office Version
  1. 2016
In that case, try
Excel Formula:
=AVERAGE(OFFSET($C$4,MATCH(P1,$B$4:$B$100,-1)-1,0,7,1))
Thanks very much, very helpful.... Tried to get my head around OFFSET without success, so will delve into the formula later so I can understand what it is doing, and not just putting the solution in my sheet without understanding how it works!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,395
Messages
5,636,057
Members
416,895
Latest member
SteveRandall

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
Top