Absolute Reference - Change absolute range without manual intervention

[Marklarbear]

Hi brains trust

Looking for assistance with the following formula in Sheet2 cell F2 (the formula calculates a weighted Average Handle Time):

=IFERROR(SUMPRODUCT(('Sheet1'!$G$2:$G$53=C2)*('Sheet1'!$E$2:$E$53)*('Sheet1'!$B$2:$B$53=B2)*('Sheet1'!$F$2:$F$53))/SUM(('Sheet1'!$G$2:$G$53=C2)*('Sheet1'!$E$2:$E$53)),0)

The formula works the way it is intended to, however, is there a way to 'progress' the absolute range references by 51 rows automatically?

The spreadsheet that I have inherited has data that goes across several years. the number of rows I need to look at in total on sheet1 is 74000. The date changes every 51 rows.... so at the moment i'm manually changing the absolute reference range by 51 every time the date changes. This is a very slow process....


ie on Sheet2, in cell F52 the formula changes to:

=IFERROR(SUMPRODUCT(('Sheet1'!$G$54:$G$105=C52)*('Sheet1'!$E$54:$E$105)*('Sheet1'!$B$54:$B$105=B52)*('Sheet1'!$F$54:$F$105))/SUM(('Sheet1'!$G$54:$G$105=C52)*('Sheet1'!$E$54:$E$105)),0)

and in cell F102 the formula changes to:

=IFERROR(SUMPRODUCT(('Sheet1'!$G$106:$G$157=C102)*('Sheet1'!$E$106:$E$157)*('Sheet1'!$B$106:$B$157=B102)*('Sheet1'!$F$106:$F$157))/SUM(('Sheet1'!$G$106:$G$157=C102)*('Sheet1'!$E$106:$E$157)),0)

and so on.....

Below are samples how sheet1 and sheet2 are set up (apologies as work security protocols wont allow xl2bb):

Sheet1:

Sheet2:

As always - any help is greatly appreciated....

 

[bebo021999]

Need to test the formula with actual sample...
Can you attach spreadsheet, using XL2BB or gg drive?

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Armstrong_N14]

Check the INDIRECT Function. I think that could help.

 

[kvsrinivasamurthy]

It can be done with the macro. the code will apply formula till the last with the conditions. Is it ok.

 

[kvsrinivasamurthy]

Dynamic formula for 365 version.
In Sheet2 cell F2

=LET(a,BYROW(K2:K2000,LAMBDA(ra,2+INT((ROWS($K$2:ra)-1)/50)*52)),b,BYROW(K2:K2000,LAMBDA(rb,53+INT((ROWS($K$2:rb)-1)/50)*52)),c,C2:C2000,d,B2:B2000,MAP(a,b,c,d,LAMBDA(sa,sb,sc,sd,SUMPRODUCT(((INDEX(Sheet1!$G:$G,sa):INDEX(Sheet1!$G:$G,sb))=sc)*((INDEX(Sheet1!$B:$B,sa):INDEX(Sheet1!$B:$B,sb))=sd)*(INDEX(Sheet1!$E:$E,sa):INDEX(Sheet1!$E:$E,sb))*(INDEX(Sheet1!$F:$F,sa):INDEX(Sheet1!$F:$F,sb)))/SUMPRODUCT(((INDEX(Sheet1!$G:$G,sa):INDEX(Sheet1!$G:$G,sb))=sc)*((INDEX(Sheet1!$B:$B,sa):INDEX(Sheet1!$B:$B,sb))=sd)*(INDEX(Sheet1!$E:$E,sa):INDEX(Sheet1!$E:$E,sb))))))

 

[kvsrinivasamurthy]

In the above formula max row is given as 2000, change it as required.

 

[Marklarbear]

Dynamic formula for 365 version.
In Sheet2 cell F2

=LET(a,BYROW(K2:K2000,LAMBDA(ra,2+INT((ROWS($K$2:ra)-1)/50)*52)),b,BYROW(K2:K2000,LAMBDA(rb,53+INT((ROWS($K$2:rb)-1)/50)*52)),c,C2:C2000,d,B2:B2000,MAP(a,b,c,d,LAMBDA(sa,sb,sc,sd,SUMPRODUCT(((INDEX(Sheet1!$G:$G,sa):INDEX(Sheet1!$G:$G,sb))=sc)*((INDEX(Sheet1!$B:$B,sa):INDEX(Sheet1!$B:$B,sb))=sd)*(INDEX(Sheet1!$E:$E,sa):INDEX(Sheet1!$E:$E,sb))*(INDEX(Sheet1!$F:$F,sa):INDEX(Sheet1!$F:$F,sb)))/SUMPRODUCT(((INDEX(Sheet1!$G:$G,sa):INDEX(Sheet1!$G:$G,sb))=sc)*((INDEX(Sheet1!$B:$B,sa):INDEX(Sheet1!$B:$B,sb))=sd)*(INDEX(Sheet1!$E:$E,sa):INDEX(Sheet1!$E:$E,sb))))))

Hey K - I don't completely understand this magic that you have in the formula, but it works perfectly!
thankyou greatly

 

[Peter_SSs]

but it works perfectly!

Just checking .. are you sure?
Is there something wrong with my sample data?

For a small test, this is all I have in Sheet1

Marklarbear.xlsm
	B	C	D	E	F	G
1
2	30/05/2022	A		1	1	A
3	30/05/2022	B		1	2	A
4	30/05/2022	X		1	3	B
5	31/05/2022	B		1	4	A
6	31/05/2022	K		1	5	B
7
8
Sheet1

In Sheet2 I have your formula in column F, the post 5 formula in column G and a new suggestion in column H
The new formula matches your results for this small sample (but would need further careful checking).

Marklarbear.xlsm
	B	C			F	G	H
1
2	30/05/2022	A			1	1.5	1
3	30/05/2022	B			1.5	3	1.5
4	31/05/2022	C			0	#DIV/0!	0
Sheet2

Cell Formulas
Range	Formula
G2:G2000	G2	=LET(a,BYROW(K2:K2000,LAMBDA(ra,2+INT((ROWS($K$2:ra)-1)/50)*52)),b,BYROW(K2:K2000,LAMBDA(rb,53+INT((ROWS($K$2:rb)-1)/50)*52)),c,C2:C2000,d,B2:B2000,MAP(a,b,c,d,LAMBDA(sa,sb,sc,sd,SUMPRODUCT(((INDEX(Sheet1!$G:$G,sa):INDEX(Sheet1!$G:$G,sb))=sc)*((INDEX(Sheet1!$B:$B,sa):INDEX(Sheet1!$B:$B,sb))=sd)*(INDEX(Sheet1!$E:$E,sa):INDEX(Sheet1!$E:$E,sb))*(INDEX(Sheet1!$F:$F,sa):INDEX(Sheet1!$F:$F,sb)))/SUMPRODUCT(((INDEX(Sheet1!$G:$G,sa):INDEX(Sheet1!$G:$G,sb))=sc)*((INDEX(Sheet1!$B:$B,sa):INDEX(Sheet1!$B:$B,sb))=sd)*(INDEX(Sheet1!$E:$E,sa):INDEX(Sheet1!$E:$E,sb))))))
H2:H2000	H2	=BYROW(B2:C2000,LAMBDA(rw,LET(ary,INT((ROW(rw)-1)/50)*52+SEQUENCE(52,,2),g,INDEX(Sheet1!G:G,ary),e,INDEX(Sheet1!E:E,ary),IFERROR(SUMPRODUCT((g=INDEX(rw,2))*e*(INDEX(Sheet1!B:B,ary)=INDEX(rw,1))*INDEX(Sheet1!F:F,ary))/SUM((g=INDEX(rw,2))*e),0))))
F2:F4	F2	=IFERROR(SUMPRODUCT((Sheet1!$G$2:$G$53=C2)*(Sheet1!$E$2:$E$53)*(Sheet1!$B$2:$B$53=B2)*(Sheet1!$F$2:$F$53))/SUM((Sheet1!$G$2:$G$53=C2)*(Sheet1!$E$2:$E$53)),0)
Dynamic array formulas.