Help with simplifying Weighted AHT formula by Day of Week over 365 days of data

[Marklarbear]

Hi Brains Trust

What I'm looking to do is reduce the size (and the potential for cell reference error) of the weighted AHT formula that we currently use. The sample below covers 1 month of data - our actual data table covers a full year (01/01/2021 - 31/12/2021).

What we need is a weighted AHT value by day of the week within the date range of the table. Cells in column K does this task, however this formula gets rather large when looking over a 365 day range - is there a way to get the same answers in column K with a more concise formula???

Book1
	B	C	D	E	F	G	H	I	J	K
2	Date	DOW	Vol Act	Vol Fct	AHT Act	AHT Fct
3	01/01/21	Fri	0		0
4	02/01/21	Sat	177		525
5	03/01/21	Sun	87		610				Day	Weighted AHT
6	04/01/21	Mon	606		711				Mon	733
7	05/01/21	Tue	585		738				Tue	679
8	06/01/21	Wed	535		726				Wed	717
9	07/01/21	Thu	482		731				Thu	690
10	08/01/21	Fri	457		637				Fri	692
11	09/01/21	Sat	181		613				Sat	627
12	10/01/21	Sun	92		736				Sun	698
13	11/01/21	Mon	556		714
14	12/01/21	Tue	533		645
15	13/01/21	Wed	488		678
16	14/01/21	Thu	532		658
17	15/01/21	Fri	474		661
18	16/01/21	Sat	153		632
19	17/01/21	Sun	77		629
20	18/01/21	Mon	561		726
21	19/01/21	Tue	504		648
22	20/01/21	Wed	536		693
23	21/01/21	Thu	565		657
24	22/01/21	Fri	613		720
25	23/01/21	Sat	217		635
26	24/01/21	Sun	99		647
27	25/01/21	Mon	650		775
28	26/01/21	Tue	0		0
29	27/01/21	Wed	618		761
30	28/01/21	Thu	580		716
31	29/01/21	Fri	566		731
32	30/01/21	Sat	171		733
33	31/01/21	Sun	105		836
Sheet1

Cell Formulas
Range	Formula
K6:K9	K6	=SUMPRODUCT(D6*F6+D13*F13+D20*F20+D27*F27)/SUM(D6+D13+D20+D27)
K10:K12	K10	=SUMPRODUCT(D3*F3+D10*F10+D17*F17+D24*F24+D31*F31)/SUM(D3+D10+D17+D24+D31)

The above is a sample of the data - our 'live' work book looks like this (using Mondays as an example):

Live Data Set.xlsx
	H
26	716
Summary

Cell Formulas
Range	Formula
H26	H26	=IFERROR(SUMPRODUCT('Data Set'!$D18*'Data Set'!$F18+'Data Set'!$D25*'Data Set'!$F25+'Data Set'!$D32*'Data Set'!$F32+'Data Set'!$D39*'Data Set'!$F39+'Data Set'!$D46*'Data Set'!$F46+'Data Set'!$D53*'Data Set'!$F53+'Data Set'!$D60*'Data Set'!$F60+'Data Set'!$D67*'Data Set'!$F67+'Data Set'!$D74*'Data Set'!$F74+'Data Set'!$D81*'Data Set'!$F81+'Data Set'!$D88*'Data Set'!$F88+'Data Set'!$D95*'Data Set'!$F95+'Data Set'!$D102*'Data Set'!$F102+'Data Set'!$D109*'Data Set'!$F109+'Data Set'!$D116*'Data Set'!$F116+'Data Set'!$D123*'Data Set'!$F123+'Data Set'!$D130*'Data Set'!$F130+'Data Set'!$D137*'Data Set'!$F137+'Data Set'!$D144*'Data Set'!$F144+'Data Set'!$D151*'Data Set'!$F151+'Data Set'!$D158*'Data Set'!$F158+'Data Set'!$D165*'Data Set'!$F165+'Data Set'!$D172*'Data Set'!$F172+'Data Set'!$D179*'Data Set'!$F179+'Data Set'!$D186*'Data Set'!$F186+'Data Set'!$D193*'Data Set'!$F193+'Data Set'!$D200*'Data Set'!$F200+'Data Set'!$D207*'Data Set'!$F207+'Data Set'!$D214*'Data Set'!$F214+'Data Set'!$D221*'Data Set'!$F221+'Data Set'!$D228*'Data Set'!$F228+'Data Set'!$D235*'Data Set'!$F235+'Data Set'!$D242*'Data Set'!$F242+'Data Set'!$D249*'Data Set'!$F249+'Data Set'!$D256*'Data Set'!$F256+'Data Set'!$D263*'Data Set'!$F263+'Data Set'!$D270*'Data Set'!$F270+'Data Set'!$D277*'Data Set'!$F277+'Data Set'!$D284*'Data Set'!$F284+'Data Set'!$D291*'Data Set'!$F291+'Data Set'!$D298*'Data Set'!$F298+'Data Set'!$D305*'Data Set'!$F305+'Data Set'!$D312*'Data Set'!$F312+'Data Set'!$D319*'Data Set'!$F319+'Data Set'!$D326*'Data Set'!$F326+'Data Set'!$D333*'Data Set'!$F333+'Data Set'!$D340*'Data Set'!$F340+'Data Set'!$D347*'Data Set'!$F347+'Data Set'!$D354*'Data Set'!$F354+'Data Set'!$D361*'Data Set'!$F361+'Data Set'!$D368*'Data Set'!$F368+'Data Set'!$D375*'Data Set'!$F375+'Data Set'!$D382*'Data Set'!$F382)/SUM('Data Set'!$D18,'Data Set'!$D25,'Data Set'!$D32,'Data Set'!$D39,'Data Set'!$D46,'Data Set'!$D53,'Data Set'!$D60,'Data Set'!$D67,'Data Set'!$D74,'Data Set'!$D81,'Data Set'!$D88,'Data Set'!$D95,'Data Set'!$D102,'Data Set'!$D109,'Data Set'!$D116,'Data Set'!$D123,'Data Set'!$D130,'Data Set'!$D137,'Data Set'!$D144,'Data Set'!$D151,'Data Set'!$D158,'Data Set'!$D165,'Data Set'!$D172,'Data Set'!$D179,'Data Set'!$D186,'Data Set'!$D193,'Data Set'!$D200,'Data Set'!$D207,'Data Set'!$D214,'Data Set'!$D221,'Data Set'!$D228,'Data Set'!$D235,'Data Set'!$D242,'Data Set'!$D249,'Data Set'!$D256,'Data Set'!$D263,'Data Set'!$D270,'Data Set'!$D277,'Data Set'!$D284,'Data Set'!$D291,'Data Set'!$D298,'Data Set'!$D305,'Data Set'!$D312,'Data Set'!$D319,'Data Set'!$D326,'Data Set'!$D333,'Data Set'!$D340,'Data Set'!$D347,'Data Set'!$D354,'Data Set'!$D361,'Data Set'!$D368,'Data Set'!$D375,'Data Set'!$D382),"-")

As you can appreciate - a formula this size is open to human error when referencing that many cells.....

Any assistance would be greatly appreciated

 

[KRice]

This assumes you plan to keep Monday as the first day listed in the summary, so the WEEKDAY function and the ROWS function are coordinated to return values when Monday=1, Tuesday=2, etc. If you change the order of the days in the summary list, this part of the formula would require revision. Otherwise, just adjust the range covered by the formula in column J to operate over a much longer period of time...comparison shown to your original in column I.

Book1
	A	B	C	D	E	F	G	H	I	J
1
2	Date	DOW	Vol Act	Vol Fct	AHT Act	AHT Fct
3	1/1/2021	Fri	0		0
4	1/2/2021	Sat	177		525
5	1/3/2021	Sun	87		610			Day	Weighted AHT	Weighted AHT
6	1/4/2021	Mon	606		711			Mon	732.7796	732.779604
7	1/5/2021	Tue	585		738			Tue	679.47411	679.474106
8	1/6/2021	Wed	535		726			Wed	717.05099	717.050988
9	1/7/2021	Thu	482		731			Thu	689.61695	689.616952
10	1/8/2021	Fri	457		637			Fri	691.71991	691.719905
11	1/9/2021	Sat	181		613			Sat	627.04338	627.043382
12	1/10/2021	Sun	92		736			Sun	697.93043	697.930435
13	1/11/2021	Mon	556		714
14	1/12/2021	Tue	533		645
15	1/13/2021	Wed	488		678
16	1/14/2021	Thu	532		658
17	1/15/2021	Fri	474		661
18	1/16/2021	Sat	153		632
19	1/17/2021	Sun	77		629
20	1/18/2021	Mon	561		726
21	1/19/2021	Tue	504		648
22	1/20/2021	Wed	536		693
23	1/21/2021	Thu	565		657
24	1/22/2021	Fri	613		720
25	1/23/2021	Sat	217		635
26	1/24/2021	Sun	99		647
27	1/25/2021	Mon	650		775
28	1/26/2021	Tue	0		0
29	1/27/2021	Wed	618		761
30	1/28/2021	Thu	580		716
31	1/29/2021	Fri	566		731
32	1/30/2021	Sat	171		733
33	1/31/2021	Sun	105		836
Sheet1

Cell Formulas
Range	Formula
I6:I9	I6	=SUMPRODUCT(C6*E6+C13*E13+C20*E20+C27*E27)/SUM(C6+C13+C20+C27)
J6:J12	J6	=LET(darr,--(WEEKDAY($A$3:$A$33,2)=ROWS(H$6:H6)),dow,$C$3:$C$33,SUMPRODUCT(dow,$E$3:$E$33,darr)/SUM(dow*darr))
I10:I12	I10	=SUMPRODUCT(C3*E3+C10*E10+C17*E17+C24*E24+C31*E31)/SUM(C3+C10+C17+C24+C31)

 

[KRice]

Let me try that again...I didn't have my sheet set up the same way...and this version avoids the WEEKDAY/ROWS idea and simply matches days to the DOW column so it's even shorter:

MrExcel_20220531.xlsx
	B	C	D	E	F	G	H	I	J
2	Date	DOW	Vol Act	Vol Fct	AHT Act	AHT Fct
3	1/1/2021	Fri	0		0
4	1/2/2021	Sat	177		525
5	1/3/2021	Sun	87		610			Day	Weighted AHT
6	1/4/2021	Mon	606		711			Mon	732.779604
7	1/5/2021	Tue	585		738			Tue	679.474106
8	1/6/2021	Wed	535		726			Wed	717.050988
9	1/7/2021	Thu	482		731			Thu	689.616952
10	1/8/2021	Fri	457		637			Fri	691.719905
11	1/9/2021	Sat	181		613			Sat	627.043382
12	1/10/2021	Sun	92		736			Sun	697.930435
Sheet1

Cell Formulas
Range	Formula
J6:J12	J6	=LET(darr,N($C$3:$C$33=I6),vol,$D$3:$D$33,SUMPRODUCT(vol,darr,$F$3:$F$33)/SUM(vol*darr))

 

[Marklarbear]

Let me try that again...I didn't have my sheet set up the same way...and this version avoids the WEEKDAY/ROWS idea and simply matches days to the DOW column so it's even shorter:

MrExcel_20220531.xlsx
	B	C	D	E	F	G	H	I	J
2	Date	DOW	Vol Act	Vol Fct	AHT Act	AHT Fct
3	1/1/2021	Fri	0		0
4	1/2/2021	Sat	177		525
5	1/3/2021	Sun	87		610			Day	Weighted AHT
6	1/4/2021	Mon	606		711			Mon	732.779604
7	1/5/2021	Tue	585		738			Tue	679.474106
8	1/6/2021	Wed	535		726			Wed	717.050988
9	1/7/2021	Thu	482		731			Thu	689.616952
10	1/8/2021	Fri	457		637			Fri	691.719905
11	1/9/2021	Sat	181		613			Sat	627.043382
12	1/10/2021	Sun	92		736			Sun	697.930435
Sheet1

Cell Formulas
Range	Formula
J6:J12	J6	=LET(darr,N($C$3:$C$33=I6),vol,$D$3:$D$33,SUMPRODUCT(vol,darr,$F$3:$F$33)/SUM(vol*darr))

Cheers KRice - This works brilliantly!!! you are a legend!

 

[KRice]

I'm happy to help.