How to combine nested array formulas in google sheets?

Question

My dataset is as follows

Tokens per week is a static value, basically, it is hardcoded

Stakes per week is a sum of values in a column below. Eg. F2 contains formula: =sum(F4:F)

Values in calls below Week # are manually typed.

What I need is to calculate how many tokens each user has earned per week and show total amount in a row under Tokens pending

An example of Math for a very first row:

F4/F2*F1+G4/G2*G1

I have users constantly signing up for this campaign, so I need a formula to automatically apply for each new user.

Here is what I tried so far:

I put following formula in E4: =ArrayFormula(if(isblank(A4:A), "", (SUMIF(IF(COLUMN(F4:BE4),ROW(F4:BE)),ROW(F4:BE),F4:BE)*($F$1/$F$2)-SUMIF(IF(COLUMN(BF4:BO4),ROW(BF4:BO)),ROW(BF4:BO),BF4:BO))))

But that gives me wrong results
I put following formula in E4 =arrayformula(sum(iferror(F4:BE4/F$2:BE$2*F$1:BE$1,0)))

But that calculates it correctly for only one row. Formula is not automatically applied for each row below
I put following formula into E4: =ArrayFormula(if(isblank(A4:A), "", arrayformula(sum(iferror(F4:BE/F$2:BE$2*F$1:BE$1,0)))))

But that gives me the wrong calculations again.

I am now considering to write user-defined function to handle this case.

I would appreciate any help!

UPDATE 25-06-2020

Here is a link to the sheet as it was requested:

I recreate you dataset from the screenshoot and the second solution : =arrayformula(sum(iferror(F4:BE4/F$2:BE$2*F$1:BE$1,0))) ; work perfectly for me... can you show the incorrects values ? — Julien Maret
– Julien Maret, Commented Jun 24, 2020 at 13:14
@JulienMaret Yeap, it works, but it does not expand automatically to newly added rows. I have to apply that manually. And also, I have a typo, it should be "Formula is not automatically..." — Max
– Max, Commented Jun 25, 2020 at 15:03

player0 · Accepted Answer · 2020-06-25 22:35:49Z

1

try in F2:

=ARRAYFORMULA(IF(F1:1="",,TRANSPOSE(MMULT(TRANSPOSE(
 INDIRECT("F4:"&ROWS(A:A))*1), SIGN(F4:F)^0))))

and E4:

=ARRAYFORMULA(MMULT(IFERROR(F4:BE/F2:BE2*F1:BE1, 0), TRANSPOSE(SIGN(F1:BE1))^0))

answered Jun 25, 2020 at 22:35

player0

131k14 gold badges92 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

it looks it works, but I have no idea what is going on there. Can you exapling your solution?