CODEWITHSUNDEEP
google-sheetsarray-formulas
Simon Oswald
Simon Oswald

Reputation: 13

How to sum up complex array formula across rows without storing intermediate results in helper column?

I have some "complex" calculation that I currently do row-by-row and store in a helper column. In the end I simply run a sum on the helper column to calculate the total value of that calculation.

I would like to simply have one field, where I do the calculation of the total value - without needing the helper column.

To be concrete, I am calculating exertion load (XL): http://www.strongur.io/monitoring-training-stress-with-exertion-load/

As input data I get a weight lifter, repetitions performed and how many reps were in the tank until failure (RIR) is reached. I calculate the XL of a set by expanding the reps performed into a range => 3 reps becomes [1,2,3] and then running an ArrayFormula on that range to calculate the distance to failure from the perspective of that rep (rep 1 is further from failure than rep 3) and consequently the XL of that single rep. I then use a sum to calculate the total exertion load of the given set.

Unfortunately, this approach does not scale to the "single field"-solution - as I cannot add another ArrayFormula around it. I am not sure where to go from here - my spreadsheet experience is limited.

I think I am missing something here from a conceptual perspective - I've been doing some googling and have seen matrices mentioned, would that be the right direction for this kind of thing? I would like to avoid having to write a JavaScript function just for this use-case.

Thanks in advance for any tips/pointers! :)

Best Regards, Simon

Sample Spreadsheet: https://docs.google.com/spreadsheets/d/1CNYxsQKo_CUIsstCDbcjoojL6WK46rg9ONybviFxAGs/edit?usp=sharing

Upvotes: 1

Views: 80

Answers (1)

player0
player0

Reputation: 1

use:

=ARRAYFORMULA(SUM(IF(B2:B="";;IF(COLUMN(1:1)>C2:C;;
 B2:B*EXP(-0,215*(D2:D+C2:C-COLUMN(1:1)))))))

enter image description here

Upvotes: 2

Related Questions