Re: Nested array formula for a SUMPRODUCT operation
Anyone can help on this one?
Re: Nested array formula for a SUMPRODUCT operation
Anyone can help on this one?
Re: Nested array formula for a SUMPRODUCT operation
I'm attaching an example.
I have two columns of data with N data each, being N variable. Be data in column 1: {a1, a2, ....., aN} and in column 2: {b1, b2, .... bN}. Each figure is in one cell
[TABLE="width: 500"]
a1
[/td][td]b1
[/td][/tr][tr][td]a2
[/td][td]b2
[/td][/tr][tr][td]....
[/td][td]....
[/td][/tr][tr][td]....
[/td][td]....
[/td][/tr][tr][td]aN
[/td][td]bN
[/td][/tr]
[/TABLE]
I need an array formula to be able to perform the following calculation:
a1*(1+b1)*(1+b2)*...*(1+bN)+a2*(1+b2)*(1+b3)*...*(1+bN)+.....+aN*(1+bN)
It needs to be an array formula because the arrays in columns 1 and 2 are of different length every time. Imaging data in columns A and B from rows 1 to 10 (N=10), the formula I tried was:
{=SUMPRODUCT(A1:A10,PRODUCT(1+B1:B10))}
but it didn't work