Sumproduct + Array Function?
 

I need a tricky function that I think needs to be entered as a Sumproduct
Array Function.

I have numerical data in a table A1:K11 (B2:K11 contains the data; A2:A11
= Row labels, B1:K1 = Column Labels)
What I need to do is, given an input column (B to K) and an output column
(B to K), perform the following calculation:
For each cell in input column
multiply by the sum of all cells below that cell's row in the output column
And add the final results.

So, for example, assume input column B and output column C, here's the
steps I'd need to do:
B2 * sum (C3:c11) +
B3 * sum (C4:c11) +
B4 * sum (C5:c11) +
B5 * sum (C6:c11) +
B6 * sum (C7:c11) +
B7 * sum (C8:c11) +
B8 * sum (C9:c11) +
B9 * sum (C10:c11) +
B10 * sum (C11:c11)

Notice how the number of cells in the sum drops by 1 as you step through
the input column's rows.
In case it matters, the data in each column adds up to 1; the value in
each cell is always greater than zero and less than 1.

Is this possible?

Thanks in advance

Darren Hill wrote...
....
So, for example, assume input column B and output column C, here's the
steps I'd need to do:
B2 * sum (C3:c11) +
B3 * sum (C4:c11) +
B4 * sum (C5:c11) +
B5 * sum (C6:c11) +
B6 * sum (C7:c11) +
B7 * sum (C8:c11) +
B8 * sum (C9:c11) +
B9 * sum (C10:c11) +
B10 * sum (C11:c11)
....

Use the array formula

=SUM(B2:B10*MMULT(--(ROW(C3:C11)<=TRANSPOSE(ROW(C3:C11))),C3:C11))

Nice one Harlan! I was going to offer the following...

=SUMPRODUCT(B2:B10,SUBTOTAL(9,OFFSET(C3:C11,ROW(C3 :C11)-ROW(C3),0,ROWS(C3
:C11)-(ROW(C3:C11)-ROW(C3)))))

....confirmed with just ENTER, but I prefer your formula. I'll have to
take a closer look at it so that I can understand it.

In article .com,
"Harlan Grove" wrote:

Darren Hill wrote...
...
So, for example, assume input column B and output column C, here's the
steps I'd need to do:
B2 * sum (C3:c11) +
B3 * sum (C4:c11) +
B4 * sum (C5:c11) +
B5 * sum (C6:c11) +
B6 * sum (C7:c11) +
B7 * sum (C8:c11) +
B8 * sum (C9:c11) +
B9 * sum (C10:c11) +
B10 * sum (C11:c11)
...

Use the array formula

=SUM(B2:B10*MMULT(--(ROW(C3:C11)<=TRANSPOSE(ROW(C3:C11))),C3:C11))

On Thu, 08 Sep 2005 00:14:52 +0100, Harlan Grove wrote:

Use the array formula
=SUM(B2:B10*MMULT(--(ROW(C3:C11)<=TRANSPOSE(ROW(C3:C11))),C3:C11))

Thank you very much :)
------------------
Darren

On Thu, 08 Sep 2005 00:34:32 +0100, Domenic wrote:

Nice one Harlan! I was going to offer the following...

=SUMPRODUCT(B2:B10,SUBTOTAL(9,OFFSET(C3:C11,ROW(C3 :C11)-ROW(C3),0,ROWS(C3
:C11)-(ROW(C3:C11)-ROW(C3)))))

...confirmed with just ENTER, but I prefer your formula. I'll have to
take a closer look at it so that I can understand it.

I'm doing that, too.
I've saved your formula for study too. Thanks.

Darren