I have data in google sheets, looking like this:

and I want to implement the following formula:

which would manually look like this:

=(B1/(B1 B2)) (C1/(C1 C2)) ... (G1/(G1/G2))

However, I need this formula to be scalable. I've tried to use ARRAYFORMULA and SERIESSUM but could not manage to actually iterate over the row. Does sheets even support this, and if so, how does sheets implement iterating sums?

CodePudding user response：

You can do the following:

=Sum(ArrayFormula(B1:G1/(B1:G1 B2:G2)))

Update:

If you want the range to be dependent on a manually entered j, use:

=Sum(ArrayFormula(Indirect("R1C2:"&"R1C"&j 1,0)/(Indirect("R1C2:"&"R1C"&j 1,0) Indirect("R2C2:"&"R2C"&j 1,0))))

This assumes that the table in your post starts in A1 (R1C1).