Formula needed to perform a rollup calculation

[Celly]

I'm looking to roll up a calculation on a single dimensional range into a single cell formula. This is typically easily solvable by using a sequence of cells, however my requirement is a single cell solution which works on an arbitrary range.

So if the range is A1:A5, the formula is: (A1) + (A1 * A2) + (A1 * A2 * A3) + (A1 * A2 * A3 * A4) + (A1 * A2 * A3 * A4 * A5)

Is this possible? Sumproduct doesn't seem up to the task.

 

[aRandomHelper]

If you're fine with a VBA solution, try:

Sub me1159242_formula()
    Dim i As Long, j As Long, s As String
    s = "=("
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row 'A1 to last row of A
        For j = 1 To i
            If i > 1 Then
                s = s & Cells(j, 1).Address(0, 0) & "*"
                If j = i Then s = Left(s, Len(s) - 1) & ")+"
            Else
                s = s & Cells(j, 1).Address(0, 0) & ")+"
            End If
        Next
        s = s & "("
    Next
    ActiveCell.Formula = Left(s, Len(s) - 2)
End Sub

This puts a formula according to what you wanted into the currently selected cell. Range used is A1 to last row of A.

 

[Tetra201]

See if the following single-cell formula works for you:

=SUMPRODUCT(SUBTOTAL(6,OFFSET(A1,,,ROW(A1:A5)-ROW(A1)+1)))

 

[Celly]

See if the following single-cell formula works for you:

=SUMPRODUCT(SUBTOTAL(6,OFFSET(A1,,,ROW(A1:A5)-ROW(A1)+1)))

OK I had to study that one for a while. It looks like the brilliant part is passing a range to the OFFSET height parameter to return a jagged array.

So this variation is equivalent:

{=SUM(SUBTOTAL(6,OFFSET(A11,,,ROW(A11:A15)-ROW(A11)+1)))}

That's a nice technique, thanks Tetra!

 

[Tetra201]

You are welcome.