Convert two-dimensional array elements to cumulative sums

for one row
=SUM(TRANSPOSE(MMULT(--(ROW(1:6)>=COLUMN(A:F)),TRANSPOSE(A1:F1))))

Originally Posted by FlameRetired

Not sure I understand.

From the description ... but not your results ... I interpret

Formula:

Please Login or Register  to view this content.

which returns {105;208;105;244;46;208;46}
at my end.

What am I missing?

This looks like the sum of all the elements in each row. Now the question is how to derive the "running total" of each row at each columnar position (e.g. {1,2,3} -> {1,3,6})? The resulting array should be the same dimensions (each element having the prior elements in the row added to it).

Originally Posted by FlameRetired

In that case you expect another 6x7 array ... then would the desired result be

{46,105,105,105,105,105;46,105,173,208,208,208;46,105,105,105,105,105;46,105,173,208,237,244;46,46,46,46,46,46;46,105,173,208,208,208;46,46,46,46,46,46}

Ah yes. You are correct. It looks like I ommitted the 5th result row by mistake in my initial post. Yours above is the desired result.

Originally Posted by FlameRetired

All I can come up with uses a lot of brute force. It is not robust. It requires 6x7 array.

For brevity the array is named in Name Manager Arry.

The formula

Formula:

Please Login or Register  to view this content.

Some how I doubt this acceptable.

It does give me something else to investigate in the choose() function, though. You're right, the trouble is that 6x7 array is dynamic, so the dimensions could be different. The problem is the recursion through the original array, which is what you've manually produced through brute force. I was playing around with that coerced INDEX(...,N(IF(1,...)) approach to isolate each element in each row, but it was inside the original array-producing function, so the recursion wasn't correct. Perhaps that choose() function might offer some more possibilities. Thanks for the lead, going to work on that for a bit and will post back for posterity if I come up with something.

Originally Posted by FlameRetired

I wish you the best.

I suspect you are going to need a VBA approach to this. Unfortunately I don't know enough to help you there.

I you want to go that route (Use Report Post) and ask a Mod/Admin to move your thread to the VBA section. Please don't do this yourself. That's double-posting. They'll yell at me for not telling you. LOL

Thanks, Dave. I'm going to do just that. With out VBA, I can't seem to find a way to manufacture a looping variable to increment. All the options for built-in functions (INDEX, SUMPRODUCT, CHOOSE, SUBTOTAL, AGGREGATE, etc.) can take arrays as inputs, but immediately evaluate those arrays, preventing recursion to modify the elements.

I think the new approach will be a VBA user-defined function that takes an array as input, loops through each of the elements to calculate the row subtotal at each element's columnar position, and returns those running sums in an array of the same dimensions.

Will begin working on this function, but I'm not very strong in VBA either, so any/all input from forum members is welcomed!

Originally Posted by MrShorty

If any and all input is welcomed, I will offer this input -- is there some reason this must be done in a single cell array formula? I have long thought that the easiest way to get a "running total" like this is to have the array in a block of cells, then a simple SUM() function -- with the right mix of relative and absolute references -- easily computes the cumulative totals.

If your array is in A3:F9, then your cumulative sum formula is =SUM($A3:A3) entered into H3 and copied across and down to N9. Without knowing the reasons for trying to force this calculation into a single cell, my first thought is how easy this is using helper ranges.

You make a good point, but yes, there is some additional context I left out for simplicity.

This cell is part of a worksheet that calculates pro-forma financial projections for a hospitality development. The array it produces describes the occupancy of all of the floorplans in the development in a given month (e.g. elements = # days occupied, columns = unique floorplans of accommodations, rows = # of guests in occupancy).

So you see, the problem is that as the sheet moves left to right, each column is a new month's pro-forma projection, utilizing one of the sheet's two available dimensions. Converting this array from a single-cell formula to a multi-cell range would require adding a 3rd dimension to the sheet.

I thought about using an altogether separate sheet in the workbook to host an 'occupancy grid' as an array formula spread across a 2D range, but it would only be able to calculate one month's configuration each time the workbook calculated. I would basically need a seperate 2D range for each month in the projection.

Posting the stunningly simple VBA solution for posterity. This takes a two-dimensional array as input, and returns row-wise running sums at each columnar position.

Please Login or Register  to view this content.