Weighted Average (Dynamic Date Range)

[Evan11]

Based on the date in cell A1, I would like to make a dynamic weight average. For this specific Example I would get the start of the year for my beginning date criteria with the following formula: DATE(YEAR$A$1),1,1)) and I would get the end date for my range by using: EOMONTH($A$1,0). Those would produce the date range of 1/1/23 - 8/31/23. Based on that range criteria, I would like to get the weighted average Retained rate in column B using column D, Total Balance, as the weight.

Formula I thought would work but not returning he result I wanted is:
=SUMPRODUCT(--($A$3:$A$50>=DATE(YEAR($A$1),1,1))--($A$3:$A$50<=EOMONTH($A$1,0)),($B$3:$B$50))/SUMPRODUCT(--($A$3:$A$50>=DATE(YEAR($A$1),1,1))--($A$3:$A$50<=EOMONTH($A$1,0)),($D$3:$D$50))

Any help on this would be much appreciated so I don't have to use the static =SUMPRODUCT(B39:B46,D39:D46)/SUM(D39:D46) weighted average formula and adjust the range when I load in new data. The desired result is 80.09% weighted average %. Thank you in advance

 

[Fluff]

Can you post your sample data, so we don't have to type it all out.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Evan11]

Ya I spent about 30 minutes trying to get the add-in to work and couldn't so posted a picture. Any other good options? I think my work may have some admin permissions from allowing XL2BB

 

[Evan11]

Raw data in case it helps since XL2BB was not working for me

8/25/2023​
	Total Retained	Total Closed	Total Balance
Jan-20	72.52%​	27.48%​	13,794,799
Feb-20	90.73%​	9.27%​	16,294,006
Mar-20	84.69%​	15.31%​	13,446,514
Apr-20	81.08%​	18.92%​	9,181,673
May-20	86.72%​	13.28%​	14,055,403
Jun-20	89.09%​	10.91%​	19,605,836
Jul-20	85.41%​	14.59%​	15,899,258
Aug-20	85.41%​	14.59%​	22,864,442
Sep-20	83.18%​	16.82%​	20,385,255
Oct-20	90.89%​	9.11%​	17,428,825
Nov-20	89.35%​	10.65%​	19,116,272
Dec-20	87.82%​	12.18%​	14,391,627
Jan-21	86.06%​	13.94%​	15,237,447
Feb-21	74.66%​	25.34%​	8,179,291
Mar-21	83.73%​	16.27%​	10,603,310
Apr-21	74.44%​	25.56%​	16,702,808
May-21	73.13%​	26.87%​	23,502,383
Jun-21	72.10%​	27.90%​	31,389,176
Jul-21	79.63%​	20.37%​	19,811,719
Aug-21	65.04%​	34.96%​	20,783,235
Sep-21	76.26%​	23.74%​	22,251,831
Oct-21	83.78%​	16.22%​	31,037,446
Nov-21	89.54%​	10.46%​	13,076,260
Dec-21	90.12%​	9.88%​	15,675,045
Jan-22	86.09%​	13.91%​	15,320,342
Feb-22	88.35%​	11.65%​	12,240,125
Mar-22	90.44%​	9.56%​	20,710,468
Apr-22	86.49%​	13.51%​	14,302,590
May-22	49.81%​	50.19%​	12,647,727
Jun-22	73.73%​	26.27%​	10,722,957
Jul-22	83.92%​	16.08%​	13,053,842
Aug-22	83.30%​	16.70%​	12,731,275
Sep-22	81.37%​	18.63%​	12,211,456
Oct-22	88.47%​	11.53%​	16,701,952
Nov-22	76.91%​	23.09%​	9,511,249
Dec-22	69.90%​	30.10%​	6,970,017
Jan-23	77.62%​	22.38%​	8,787,850
Feb-23	73.24%​	26.76%​	7,182,903
Mar-23	71.96%​	28.04%​	24,730,843
Apr-23	79.17%​	20.83%​	15,562,675
May-23	91.98%​	8.02%​	14,077,476
Jun-23	85.96%​	14.04%​	19,657,827
Jul-23	74.16%​	25.84%​	17,776,725
Aug-23	89.51%​	10.49%​	11,596,747
Sep-23
Oct-23
Nov-23
Dec-23

 

[Fluff]

Ya I spent about 30 minutes trying to get the add-in to work and couldn't

In what way wouldn't it work?

 

[Evan11]

1. Get a microsoft security notice saying microsoft is blocking the macros from running since the file source is untrusted despite moving it from downloads to the local drive. 2. some other error about it not being supported in this format.

 

[Fluff]

Ok, have a look here XL2BB Icons greyed out

 

[Fluff]

Try

Fluff.xlsm
	A	B	C	D	E	F
1	25/08/2023
2		Total Retained	Total Closed	Total Balance
3	Jan-20	72.52%	27.48%	13,794,799		0.8009259
4	Feb-20	90.73%	9.27%	16,294,006
5	Mar-20	84.69%	15.31%	13,446,514
6	Apr-20	81.08%	18.92%	9,181,673
7	May-20	86.72%	13.28%	14,055,403
8	Jun-20	89.09%	10.91%	19,605,836
9	Jul-20	85.41%	14.59%	15,899,258
10	Aug-20	85.41%	14.59%	22,864,442
11	Sep-20	83.18%	16.82%	20,385,255
12	Oct-20	90.89%	9.11%	17,428,825
13	Nov-20	89.35%	10.65%	19,116,272
14	Dec-20	87.82%	12.18%	14,391,627
15	Jan-21	86.06%	13.94%	15,237,447
16	Feb-21	74.66%	25.34%	8,179,291
17	Mar-21	83.73%	16.27%	10,603,310
18	Apr-21	74.44%	25.56%	16,702,808
19	May-21	73.13%	26.87%	23,502,383
20	Jun-21	72.10%	27.90%	31,389,176
21	Jul-21	79.63%	20.37%	19,811,719
22	Aug-21	65.04%	34.96%	20,783,235
23	Sep-21	76.26%	23.74%	22,251,831
24	Oct-21	83.78%	16.22%	31,037,446
25	Nov-21	89.54%	10.46%	13,076,260
26	Dec-21	90.12%	9.88%	15,675,045
27	Jan-22	86.09%	13.91%	15,320,342
28	Feb-22	88.35%	11.65%	12,240,125
29	Mar-22	90.44%	9.56%	20,710,468
30	Apr-22	86.49%	13.51%	14,302,590
31	May-22	49.81%	50.19%	12,647,727
32	Jun-22	73.73%	26.27%	10,722,957
33	Jul-22	83.92%	16.08%	13,053,842
34	Aug-22	83.30%	16.70%	12,731,275
35	Sep-22	81.37%	18.63%	12,211,456
36	Oct-22	88.47%	11.53%	16,701,952
37	Nov-22	76.91%	23.09%	9,511,249
38	Dec-22	69.90%	30.10%	6,970,017
39	Jan-23	77.62%	22.38%	8,787,850
40	Feb-23	73.24%	26.76%	7,182,903
41	Mar-23	71.96%	28.04%	24,730,843
42	Apr-23	79.17%	20.83%	15,562,675
43	May-23	91.98%	8.02%	14,077,476
44	Jun-23	85.96%	14.04%	19,657,827
45	Jul-23	74.16%	25.84%	17,776,725
46	Aug-23	89.51%	10.49%	11,596,747
47	Sep-23
48	Oct-23
49	Nov-23
50	Dec-23
Data

Cell Formulas
Range	Formula
F3	F3	=SUMPRODUCT(--($A$3:$A$50>=DATE(YEAR($A$1),1,1)),--($A$3:$A$50<=EOMONTH($A$1,0)),($B$3:$B$50),D3:D50)/SUMPRODUCT(--($A$3:$A$50>=DATE(YEAR($A$1),1,1)),--($A$3:$A$50<=EOMONTH($A$1,0)),($D$3:$D$50))

 

[Evan11]

Solution worked great, thank you so much for your help. I have marked as solved. I will next work on getting the XL2BB add-in working for any other future posts.

 

[Fluff]

Glad to help & thanks for the feedback.