Final Friday Fix: September 2023 Challenge

On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI problem for you to puzzle over for the weekend.  On the Monday, we publish a solution.  If you think there is an alternative answer, feel free to email us.  We’ll feel free to ignore you.

The Challenge

Given an array of numeric values, how do you produce an array of row running totals?  You might be familiar with using a SUM function and anchoring the beginning of ranges.  This month, we challenge you to do that for a whole array in one [1] formula!  You can download the question file here which asks you to produce row cumulative sums and row cumulative products for a 9×10 array.  The outputs should look like the following upon completion:

As always, there are some requirements:

• each formula needs to be within one cell
• the function LAMBDA and its helper functions (e.g. LET, BYROW or MAP) are not allowed
• this is a formula challenge; no Power Query / Get & Transform or VBA.

Feel free to use the attached Excel file to assist you.

Sounds easy?  Then why not have a go?  We’ll publish one solution in Monday’s blog.  Have a great weekend in the meantime!