Array formula to return all steps of cumulative / running total

Hi all,

I'm scratching my head around an array formula that I'm trying to build. It has to be an array since I need to use it as an input for a function.

Bascially what I'm looking for is the equivalent of each step of a cumulative / running total but in array form. It's easiest explained with an example so an image and workbook is attached.

arraycml.PNG

What I am aiming for is col D "Array Cml" to have an identical output to col C "Cml Sales" using only col B "Sales".

Can anyone point me in the right direction?

Thanks

What is your expected result ?

Guys, I included all of this information in the original post

1. Expected result is for an array formula in col D to output the values given by individual formulas in col C

2. Not an array formula, that is the group of non-array formulas in col C

There is little point in rolling your eyes at people who are trying to help you - it's not terribly courteous, either.

A better approach might be to show where you want to use the array: what is the formula you are trying to build? There may be a more appropriate way of doing this.

=subtotal(9,offset(b2:b13,,,row(b2:b13)-1,))

Originally Posted by AliGW

There is little point in rolling your eyes at people who are trying to help you - it's not terribly courteous, either.

It was a happy and jolly eye-roll with a smile on my face ("ohhh you guysssss!")--I cannot help but laugh at these things and emoticons are limited


I think you are right that more context is probably required.
So my final goal is to apply linear regression to monthly sales, however I would like to apply the regression to the cumulative totals and adjust for zero-intercept so that I get a model for forecasting sales in future months (one that's a 'better' fit than just taking the mean across all months, especially when we start drilling down to individual sites).

There are three caveats which complicate this:
1. My sales data is not in one continuous array so will need to filter it out in-situ using an array formula and/or SUMPRODUCT()
2. My sales data includes 0s for future months for which I would need the cumulative total to 'stop'
3. Contrary to my usual ideology, I do not wish to use helper columns since I have a requirement for no hidden columns in this report

I have attached a workbook showing my final goal and how to get there using manual intervention and manual columns. The challenge is to replicate this into one formula regardless of how complicated it is.

We like a challenge!

Thanks for the extra information - I am sure it will prove useful.

Do you have Office 365?

Unfortunately not, just Office 2016, so I cannot use 365 exclusive functions such as SWITCH() etc.

That's a shame, because I had come up with this:

=SUBSTITUTE(SUBSTITUTE(TEXTJOIN(",",TRUE,IF(B7:J7="TOT",B8:J8)),"FALSE",""),",,","")

... array entered.

It returns the values needed for the first TREND formula. It was just a start - maybe could be replicated with CONCATENATE?

I'm beginning to feel that you will probably need a UDF somewhere along the way here.

Trying to avoid using a UDF since I purge all the VBA from the file when I generate the 'static' version for circulation, but the directors who receive it like to see all the formulas in there so that they can remember how things are calculated. This one will definitely require a comment!!

I spent some more time on this yesterday afternoon. I have used Tim's clever little SUBTOTAL(OFFSET()) resizing formula to generate the cumulative totals inside the final formula (+rep added). However I can't seem to use an array input for the OFFSET() section of Tim's formula, meaning I have to pull out the "TOT" values in a separate helper row.
This is a "so close yet so far" situation. Is using SUBTOTAL(OFFSET()) viable and just requires a bit of work to sum only the "TOT" values, or is this the closest this method can get? Are there any other ways of getting each step of cumulative totals into an array?

So close...

Edit: I have given up on the TREND() formula since it counts all the zeros (i.e. where I am deleting non-relevant data by multipling by zero) as datapoints. By calculating it manually I can sum-out the zeros I have generated. Generating Sigma(xx) is easy, it's generating the Sigma(xy_cml) that's tricky because of the y_cml part.

Hi John,

Thanks for spending the time creating those formulas. However the type of formula I'm looking for is a static array, because we cannot 'copy accross' inside a single-cell formula. I'm looking to eliminate the helper row and have the entire calculation in a single cell.

If you use 'Evaluate Formula' in cell D59 you can see this happening by creating arrays inside the formula. However it doesn't work because either the SUBTOTAL() or OFFSET() function is not working properly when faced with a direct array rather than a cell reference. I have a feeling it's the OFFSET() function which is built to work off of cell ranges, not arrays of values...

Ok, so I have solved my specific problem. Not by getting the OFFSET() function to accept arrays inside the formula, but rather by looking at the maths behind specifically what I am currently getting vs what I actually want.

Currently I am getting a cumulative sum of B1, B2, and TOT all added together, and multipling by the month number.
Quickly renaming B1 = A, B2 = B, TOT = y gives:

CodeCogsEqn.png

I was trying to extract just y (TOT) only, but because by definition y = A + B (TOT = B1 + B2) you'll notice that we get:

CodeCogsEqn(1).png

So now it is easy to see that we just need to divide by 2 to get the equation required, in all cases, with any amount of parts for y:

CodeCogsEqn(2).png


My updated formula gives me the target of 3695:

Formula:

Please Login or Register  to view this content.

Then just dividing by the sum of the x²'s gives me my final regression coefficient of 263.929 in a single cell!

Perhaps if I had used more rounded example numbers I would have noticed earlier lol.

Thanks everyone for helping me get to this solution!
Sorry that I couldn't figure out a more general solution to the formula problem, for anyone looking at this in the future... but good luck