subtleskeptic
New Member
- Joined
- Sep 11, 2016
- Messages
- 46
- Office Version
- 365
- 2019
- Platform
- Windows
Hello all, I have a spreadsheet with three columns
A = prices
B = Quantities
C = Dates
in:
D1 = Id like to return a weighted 1 month avg
D2 = Weighted 3 month avg
D3 = Weighted average YTD.
However, this dataset of A/B/C is updated everyday - so basically D1 needs to take the previous 21 business from today (not 30 calendar days) to calculate the 1 month weighted avg, and D2, 3 months back, and D3 YTD.
Ive tried using an offset but it doesnt seem like its working - any ideas please?
Thanks!
A = prices
B = Quantities
C = Dates
in:
D1 = Id like to return a weighted 1 month avg
D2 = Weighted 3 month avg
D3 = Weighted average YTD.
However, this dataset of A/B/C is updated everyday - so basically D1 needs to take the previous 21 business from today (not 30 calendar days) to calculate the 1 month weighted avg, and D2, 3 months back, and D3 YTD.
Ive tried using an offset but it doesnt seem like its working - any ideas please?
Thanks!