Recursive sum in Google Sheets

Question

I want to build a table of in-game item costs for the iOS game Builderment. I've entered the item recipes into a sheet named Recipes:

	A	B	C	D	E
1		Gyroscope	Magnetic Field Generator	Quantum Entangler	Stabilizer
2	Gyroscope				2
3	Magnetic Field Generator			1
4	Quantum Entangler
5	Stabilizer		1	2

Each column is the quantity of items required to build the item at the top. (This is a small subset of the 53 items in the game, sufficient to illustrate the problem.)

1. A Gyroscope is free and built from nothing.
2. A Magnetic Field Generator is built from 1 Stabilizer.
3. A Quantum Entangler is built from 1 Magnetic Field Generator and 2 Stabilizers.
4. A Stabilizer is built from 2 Gyroscopes.

Goal: a second sheet Costs with the same table, where each column represents the total cost of building that item. Each cell is a recursive sum of the cost of each of this item’s ingredients.

	A	B	C	D	E
1		Gyroscope	Magnetic Field Generator	Quantum Entangler	Stabilizer
2	Gyroscope		2	6	2
3	Magnetic Field Generator			1
4	Quantum Entangler
5	Stabilizer		1	3

The Quantum Entangler costs a total of 6 Gyroscopes, because the Magnetic Field Generator requires 2 of them, plus each of the 2 Stabilizers requires 2 more.

I manually entered this formula for cell D2:

=Recipes!D2 + Recipes!D3*C2 + Recipes!D5*E2

This is the sum of the pairwise products of this column (D) from the Recipes sheet with this row (2) of this (Costs) sheet.

I had to skip Recipes!D4 because it will always be 0, and continuing the pattern would have resulted in Recipes!D4*D2, a circular reference back to this cell.

For cell D5 I entered:

=Recipes!D5 + Recipes!D2*B2 + Recipes!D3*C5

How can I generalize this to apply to the entire 53x53 table? It seems I need a TRANSPOSE somehow? To go down the column of the Recipes sheet while going across one row of the Costs sheet.

Answer 1

Here's another solution.

=MAP(B1:E1,LAMBDA(i,LET(l,TOCOL(A2:A,1),u,B1:E1,r,FILTER(Recipes!B2:E,A2:A<>""),d,REDUCE({l,FILTER(r,u=i)},SEQUENCE(COUNTA(l)^2),LAMBDA(a,c,{a;{l,FILTER(r*INDEX(a,c,2),u=INDEX(a,c,1))}})),q,UNIQUE(INDEX(d,,1)),MAP(q,LAMBDA(q,IFERROR(1/SUM(FILTER(INDEX(d,,2),q=INDEX(d,,1)))^-1))))))

You can find this formula in tab 'z' cell B2.

Answer 2

Here's one approach for you to test it out:

=map($I2:$I,lambda(z_,if(z_="",,let(Γ,countif(let(item_,B1, row_,$A2:$A, col_,$A1:$E1, data_,$A2:$E,
     loop_,lambda(rec_,Λ,reduce(,sequence(rows(Λ)),lambda(a,c,tocol({a;if(counta(ifna(filter(data_,col_=index(Λ,c))))=0,index(Λ,c),
                 {index(Λ,c);rec_(rec_,tocol(map(row_,choosecols(data_,xmatch(index(Λ,c),col_)),lambda(a_,Σ,if(Σ="",,wraprows(a_,Σ,a_)))),1))})},1)))),
     Γ,loop_(loop_,item_),filter(Γ,isna(xmatch(Γ,item_)))),z_),if(Γ>0,Γ,)))))