Excel: Is it possible to use Spilled Arrays for a Cost Recovery Reconciliation Table without Circular References"

[jrhouse5]

I have been converting older spreadsheets to spilled arrays to improve and simplify logic. However, there is one area I cannot convert to a spilled array without the dreaded circular reference! Below is a simplified version of a table where costs are recovered from reveneue before the profit is split. I am hoping there is a solution to eliminate the circular reference in this calcuation. Thanks.

CR Spill Array Test.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
2	Time Periods	10		1	2	3	4	5	6	7	8	9	10
3
4	ANNUAL
5	Costs	58,500		10,000	10,000	5,000	5,000	5,000	10,000	2,500	2,500	6,000	2,500
6	Revenue	75,000		-	-	20,000	20,000	10,000	5,000	5,000	5,000	5,000	5,000
7
8	OLD WAY
9	Cost Recovery Reconcuiliation Table
10	Beginning	-		-	10,000	20,000	5,000	-	-	5,000	2,500	-	1,000
11	Costs Adds	58,500		10,000	10,000	5,000	5,000	5,000	10,000	2,500	2,500	6,000	2,500
12	Cost Recovery	58,500		-	-	20,000	10,000	5,000	5,000	5,000	5,000	5,000	3,500
13	End	-		10,000	20,000	5,000	-	-	5,000	2,500	-	1,000	-
14
15
16	SPILLED ARRAY --> Results in CIRCULAR REFERENCES!
17	Cost Recovery Reconcuiliation Table
18	Beginning	-		-	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
19	Costs Adds	58,500		10,000	10,000	5,000	5,000	5,000	10,000	2,500	2,500	6,000	2,500
20	Cost Recovery	#REF!		-	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
21	End	#REF!		10,000	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
Sheet1

Cell Formulas
Range	Formula
E2:N2	E2	=TRANSPOSE(SEQUENCE(C2))
C5:C6,C19:C20,C11:C12	C5	=SUM(E5:N5)
F10:N10	F10	=E13
E11	E11	=E$5
F11:N11	F11	=F5
E12:N12	E12	=MIN(E$10+E$11,E$6)
E13:N13,C13	E13	=E10+E11-E12
C18	C18	=INDEX(E18:N18,1)
C21	C21	=INDEX(E21#,C2)
E18:N18	E18	=MAKEARRAY(1,$C$2,LAMBDA(r,c,IF(c=1,0,INDEX(E21#,c))))
E19:N19	E19	=Costs
E20:N20	E20	=MAKEARRAY(1,$C$2,LAMBDA(r,c,MIN(INDEX(E18#,c)+INDEX(E19#,c),INDEX(Revenue,c))))
E21:N21	E21	=MAKEARRAY(1,$C$2,LAMBDA(r,c,SUM(INDEX(E19#,1):INDEX(E19#,c))-SUM(INDEX(E20:N20,1):INDEX(E20:N20,c))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Costs	=Sheet1!$E$5:$N$5	E19, C5, E11
Revenue	=Sheet1!$E$6:$N$6	E20, C6, E12

 

[StephenCrump]

I can replicate your Cost Recovery line (E12:N12) with the formula:

=LET(AllCosts,Costs+HSTACK(0,DROP(SCAN(0,Costs-Revenue,LAMBDA(a,b,MAX(0,a+b))),,-1)),IF(AllCosts>Revenue,Revenue,AllCosts))

 

[jrhouse5]

Thank you StephenCrump. That works like a champ!