Returning an array of sums without any helper column

[KolGuyXcel]

We have n positive integral "random" values in n consecutive rows in a column with only the n-th value always as 0. Is there a formula which would return in a particular cell, without any helper column/row/cells, an array where the p-th term is the sum of the first p terms of the original set of values?

Using Excel 2013.

 

[baitmaster]

I can't think of a way to write this as a formula, but that doesn't mean it's not possible, and I suspect it may be by calculating an array in some way and then using Textjoin to merge it into one text string

The best I can come up with is a User-Defined Function (UDF) built using VBA and entered as a worksheet formula. It's not an ideal approach but may be of use to you

Public Function arrSums(rng As Range) As String

' add maximum cell limit to prevent long processing , overflow errors etc. Could also restrict formula to the worksheet Used range
If rng.Cells.Count > 100 Then Exit Function


Dim cl As Range, i As Integer, j As Integer
Dim arrResults(): ReDim arrResults(1 To rng.Cells.Count)


For i = 1 To rng.Cells.Count
    For j = 1 To i
        arrResults(i) = arrResults(i) + rng(j)
    Next j
Next i


arrSums = Join(arrResults, "; ")


End Function

Add this code to a standard VBA code module, then type "=arrSums(your range here)" as a normal Excel formula. You may need to add "Application.Volatile" as a new first row of code (after the function name row) in order to have it recalculate on demand

 

[Eric W]

Here are 2 formulas to do it:

	B	C	D
1, 4, 10, 12, 13, 21, 32, 37, 37
1, 4, 10, 12, 13, 21, 32, 37, 37

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]{=TEXTJOIN(", ",TRUE,MMULT(IF(ROW(B3:B11)>=TRANSPOSE(ROW(B3:B11)),TRANSPOSE(B3:B11),0),ROW(B3:B11)^0))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]{=TEXTJOIN(", ",1,SUBTOTAL(9,OFFSET(B3,0,0,ROW(B3:B11)-ROW(B3)+1)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Both use the TEXTJOIN function, which is not available in Excel 2013. I do have a UDF called TEXTJOINx which duplicates the functionality of TEXTJOIN. But if you use it, then you have a macro-enabled workbook. And if that's the case, it may be preferable to use a custom macro such as baitmaster provided.

 

[KolGuyXcel]

Thanks for your responses.

However, UDF-s would need to enable macros which is a challenge. For most of the workstations.

Besides, these are returning "strings". How to reference these as "integer arrays"?

 

[Eric W]

Maybe you can explain what you'd use this for. You can't really put an array in a cell. You can do something like ={1,2,3} but you'd have to select 3 cells to show them all. If you want all of them to display, you need to create a string, which is what we did.

If you want to create an array that is usable by another formula, you could use the Name Manager. Click "Define Name", give it a name like MyArray, and use the formula:

=SUBTOTAL(9,OFFSET(Sheet1!$B$3,0,0,ROW(Sheet1!$B$3:$B$11)-ROW(Sheet1!$B$3)+1))

in the Refers to: box. Now you can use MyArray in any other formula where you'd want the array. You can select a vertical range , enter =MyArray in the top cell, and confirm with Control+Shift+Enter to just show the values.

 

[KolGuyXcel]

It worked! Subtotal-9 did the trick! Modified a bit to make it dynamic!

 

[baitmaster]

Nice formulas Eric, any chance you could explain the logic of what's happening here please? I was trying to use Row numbers within a Sumproduct but couldn't get it, I knew there was something that would work!

 

[Eric W]

The SUBTOTAL formula is actually pretty easy. It's an array formula, but you don't need to use Control+Shift+Enter since that's assumed when you enter a formula in the Name Manager. As a rule, you can't use OFFSET inside an array formula, but you can if it's inside SUBTOTAL. The key is the parameter within OFFSET that says how many rows are in the range. I just used the ROW(Sheet1!$B$3:$B$11)-ROW(Sheet1!$B$3)+1 part to create an array from 1 to n {1,2,3,4,5,...}. Then the SUBTOTAL sums up the individual ranges created by OFFSET of the various lengths. 9 is the parameter for SUBTOTAL that means "SUM".

I'll forego explaining the MMULT formula, since the SUBTOTAL one is so much easier. I only really presented it because it was the first one I got working, and I wanted to have something to show for my time! The techniques there are useful in some circumstances.


KolGuyXcel, I'm glad it works for you! Good job adapting it for your needs!

 

[baitmaster]

Yep of course I get you now, should've been able to see that as it's the height that's changing each time - especially as I was looking to do something very similar in Sumproduct. Nice trick

No worries about the MMULT, I didn't enjoy matrices when I did my maths degree 20+ years ago, and I'm happy to continue not using them now!