Dynamic SumProduct Formula

[subtleskeptic]

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!

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

+Fluff 1.xlsm
	A	B	C	D
1
2	8	1	01/05/2021
3	2	2	02/05/2021
4	16	3	03/05/2021
5	8	4	04/05/2021
6	12	5	05/05/2021
7	15	6	06/05/2021
8	10	7	07/05/2021
9	5	8	08/05/2021
10	17	9	09/05/2021
11	3	10	10/05/2021
12	13	11	11/05/2021
13	6	12	12/05/2021
14	13	13	13/05/2021
15	13	14	14/05/2021
16	10	15	15/05/2021
17	18	16	16/05/2021
18	6	17	17/05/2021
19	19	18	18/05/2021
20	8	19	19/05/2021
21	8	20	20/05/2021
22				62.5
Lists

Cell Formulas
Range	Formula
D22	D22	=SUMPRODUCT((C2:C200>=TODAY()-21)*(C2:C200<=TODAY()),A2:A200,B2:B200)/COUNTIFS(C2:C200,">="&TODAY()-21,C2:C200,"<="&TODAY())

 

[subtleskeptic]

thanks for this !

 

[Fluff]

You're welcome & thanks for the feedback.

 

[subtleskeptic]

So - using your data - i get a sumproduct weighted average value of 10.92857, any idea please?

Thanks

=SUMPRODUCT(B2:B21,C2:C21/SUM(C2:C21))

 

[Fluff]

Because you are dividing the dates by the sum of the dates.

 

[subtleskeptic]

thats my typo sorry, had copy pasted your sheet into mine.

so,
=SUMPRODUCT(A2:A21,B2:B21/SUM(B2:B21))

returns 10.92857

 

[Fluff]

If you ignoring the dates, then that is the correct result if you want the average price per unit.
For the average per unit my formula would be

=SUMPRODUCT((C2:C200>=TODAY()-21)*(C2:C200<=TODAY()),A2:A200,B2:B200)/SUMIFS(B2:B200,C2:C200,">="&TODAY()-21,C2:C200,"<="&TODAY())

 

[subtleskeptic]

all set, works - thanks again!

 

[Fluff]

Glad to help & thanks for the feedback.