Worksheet Formula Returning Sum of a Series

This is a cross-post

Hello;

The following is a failed attempt for the sum of a simple series:

The series formula is:
f(X) = Sum [k=1 to k=m+1] a(k)*X^(k-1)

Example:
lower limit in cell $B$7:: 1
value of m in cell $B$8:: 3
(m+1) values of a(k) in col B starting (always) cell B11
..........a(1) in cell B11:: -1.899
..........a(2) in cell B12:: 2.50
..........a(3) in cell B13:: 3.699
.....a(m+1) in cell B14:: -3.121
(address of last cell in col B is in cell B32:: (B14 for this example)
value of X in cell $I$11:: 2.0
Retuned Sum value in cell I34::should be -7.071 ...

The array formula entered in I34:
{=SUM(PRODUCT(B11:B32, POWER(I11,ROW(INDIRECT(B7&":"&B8+1))-1)))}

returns the wrong sum of 54.807 ...
(Product and Power functions are used so that they could be replaced (if the
need arise) with their complex number counterparts)

The problem with the above formula is that apparently it multiplies the
elements of the 1st array: B11*B12*B13*B14 instead of summing the product of
the corresponding elements: B11*I11^0 + B12*I11^1 + B13*I11^2 + B14*I11^3
The 1st argument B11:B32 of the Product function is clearly the problem.
The 2nd argument should be OK. I think!

Your suggestion to fix the above array formula would be greatly appreciated.

Regards.

=SUM(B11:INDIRECT(B32)*( POWER(I11,ROW(INDIRECT(B7&":"&B8+1))-1)))

make sure that you do array enter it, the result you quoted in your formula
was only achieved if not array entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"monir" wrote in message
...
This is a cross-post

Hello;

The following is a failed attempt for the sum of a simple series:

The series formula is:
f(X) = Sum [k=1 to k=m+1] a(k)*X^(k-1)

Example:
lower limit in cell $B$7:: 1
value of m in cell $B$8:: 3
(m+1) values of a(k) in col B starting (always) cell B11
.........a(1) in cell B11:: -1.899
.........a(2) in cell B12:: 2.50
.........a(3) in cell B13:: 3.699
....a(m+1) in cell B14:: -3.121
(address of last cell in col B is in cell B32:: (B14 for this example)
value of X in cell $I$11:: 2.0
Retuned Sum value in cell I34::should be -7.071 ...

The array formula entered in I34:
{=SUM(PRODUCT(B11:B32, POWER(I11,ROW(INDIRECT(B7&":"&B8+1))-1)))}

returns the wrong sum of 54.807 ...
(Product and Power functions are used so that they could be replaced (if
the
need arise) with their complex number counterparts)

The problem with the above formula is that apparently it multiplies the
elements of the 1st array: B11*B12*B13*B14 instead of summing the product
of
the corresponding elements: B11*I11^0 + B12*I11^1 + B13*I11^2 + B14*I11^3
The 1st argument B11:B32 of the Product function is clearly the problem.
The 2nd argument should be OK. I think!

Your suggestion to fix the above array formula would be greatly
appreciated.

Regards.

Hi

"monir" wrote in message
...
This is a cross-post

Hello;

The following is a failed attempt for the sum of a simple series:

The series formula is:
f(X) = Sum [k=1 to k=m+1] a(k)*X^(k-1)

Example:
lower limit in cell $B$7:: 1
value of m in cell $B$8:: 3
(m+1) values of a(k) in col B starting (always) cell B11
.........a(1) in cell B11:: -1.899
.........a(2) in cell B12:: 2.50
.........a(3) in cell B13:: 3.699
....a(m+1) in cell B14:: -3.121
(address of last cell in col B is in cell B32:: (B14 for this example)
value of X in cell $I$11:: 2.0
Retuned Sum value in cell I34::should be -7.071 ...

The array formula entered in I34:
{=SUM(PRODUCT(B11:B32, POWER(I11,ROW(INDIRECT(B7&":"&B8+1))-1)))}

returns the wrong sum of 54.807 ...
(Product and Power functions are used so that they could be replaced (if
the
need arise) with their complex number counterparts)

The problem with the above formula is that apparently it multiplies the
elements of the 1st array: B11*B12*B13*B14 instead of summing the product
of
the corresponding elements: B11*I11^0 + B12*I11^1 + B13*I11^2 + B14*I11^3
The 1st argument B11:B32 of the Product function is clearly the problem.
The 2nd argument should be OK. I think!

Your suggestion to fix the above array formula would be greatly
appreciated.

Regards.

Take a look at the SERIESSUM function, it's in the Analysis ToolPak.

Cheers,
Shane

"monir" wrote in message
...
This is a cross-post

Hello;

The following is a failed attempt for the sum of a simple series:

The series formula is:
f(X) = Sum [k=1 to k=m+1] a(k)*X^(k-1)

Example:
lower limit in cell $B$7:: 1
value of m in cell $B$8:: 3
(m+1) values of a(k) in col B starting (always) cell B11
.........a(1) in cell B11:: -1.899
.........a(2) in cell B12:: 2.50
.........a(3) in cell B13:: 3.699
....a(m+1) in cell B14:: -3.121
(address of last cell in col B is in cell B32:: (B14 for this example)
value of X in cell $I$11:: 2.0
Retuned Sum value in cell I34::should be -7.071 ...

The array formula entered in I34:
{=SUM(PRODUCT(B11:B32, POWER(I11,ROW(INDIRECT(B7&":"&B8+1))-1)))}

returns the wrong sum of 54.807 ...
(Product and Power functions are used so that they could be replaced (if
the
need arise) with their complex number counterparts)

The problem with the above formula is that apparently it multiplies the
elements of the 1st array: B11*B12*B13*B14 instead of summing the product
of
the corresponding elements: B11*I11^0 + B12*I11^1 + B13*I11^2 + B14*I11^3
The 1st argument B11:B32 of the Product function is clearly the problem.
The 2nd argument should be OK. I think!

Your suggestion to fix the above array formula would be greatly
appreciated.

Regards.

Bob;

Thank you.
Now try with the Product function instead of the multiplication operator "*":
{=SUM(PRODUCT(B11:INDIRECT(B32),( POWER(C14,ROW(INDIRECT(B7&":"&B8+1))-1))))}

For the same numerical example, you will get the wrong result of 3,507.704
.... and not -7.071 ...

Any thoughts ??

Regards.


"Bob Phillips" wrote:

=SUM(B11:INDIRECT(B32)*( POWER(I11,ROW(INDIRECT(B7&":"&B8+1))-1)))

make sure that you do array enter it, the result you quoted in your formula
was only achieved if not array entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"monir" wrote in message
...
This is a cross-post

Hello;

The following is a failed attempt for the sum of a simple series:

The series formula is:
f(X) = Sum [k=1 to k=m+1] a(k)*X^(k-1)

Example:
lower limit in cell $B$7:: 1
value of m in cell $B$8:: 3
(m+1) values of a(k) in col B starting (always) cell B11
.........a(1) in cell B11:: -1.899
.........a(2) in cell B12:: 2.50
.........a(3) in cell B13:: 3.699
....a(m+1) in cell B14:: -3.121
(address of last cell in col B is in cell B32:: (B14 for this example)
value of X in cell $I$11:: 2.0
Retuned Sum value in cell I34::should be -7.071 ...

The array formula entered in I34:
{=SUM(PRODUCT(B11:B32, POWER(I11,ROW(INDIRECT(B7&":"&B8+1))-1)))}

returns the wrong sum of 54.807 ...
(Product and Power functions are used so that they could be replaced (if
the
need arise) with their complex number counterparts)

The problem with the above formula is that apparently it multiplies the
elements of the 1st array: B11*B12*B13*B14 instead of summing the product
of
the corresponding elements: B11*I11^0 + B12*I11^1 + B13*I11^2 + B14*I11^3
The 1st argument B11:B32 of the Product function is clearly the problem.
The 2nd argument should be OK. I think!

Your suggestion to fix the above array formula would be greatly
appreciated.

Regards.

Shane;

There's no SERIESSUM function under Tools:ata Analysis.
Could it be under something else ??

Thank you.
(Excel 2003, Win XP)


"Shane Devenshire" wrote:

Take a look at the SERIESSUM function, it's in the Analysis ToolPak.

Cheers,
Shane

"monir" wrote in message
...
This is a cross-post

Hello;

The following is a failed attempt for the sum of a simple series:

The series formula is:
f(X) = Sum [k=1 to k=m+1] a(k)*X^(k-1)

Example:
lower limit in cell $B$7:: 1
value of m in cell $B$8:: 3
(m+1) values of a(k) in col B starting (always) cell B11
.........a(1) in cell B11:: -1.899
.........a(2) in cell B12:: 2.50
.........a(3) in cell B13:: 3.699
....a(m+1) in cell B14:: -3.121
(address of last cell in col B is in cell B32:: (B14 for this example)
value of X in cell $I$11:: 2.0
Retuned Sum value in cell I34::should be -7.071 ...

The array formula entered in I34:
{=SUM(PRODUCT(B11:B32, POWER(I11,ROW(INDIRECT(B7&":"&B8+1))-1)))}

returns the wrong sum of 54.807 ...
(Product and Power functions are used so that they could be replaced (if
the
need arise) with their complex number counterparts)

The problem with the above formula is that apparently it multiplies the
elements of the 1st array: B11*B12*B13*B14 instead of summing the product
of
the corresponding elements: B11*I11^0 + B12*I11^1 + B13*I11^2 + B14*I11^3
The 1st argument B11:B32 of the Product function is clearly the problem.
The 2nd argument should be OK. I think!

Your suggestion to fix the above array formula would be greatly
appreciated.

Regards.

Bob;

It works if you combine/replace the two functions SUM and PRODUCT with
SUMPRODUCT:
{=SUMPRODUCT(B11:INDIRECT(B32),( POWER(C14,ROW(INDIRECT(B7&":"&B8+1))-1)))}

But I don't believe there's IMSUMPRODUCT function.

Thank you.
(Excel 2003, Win XP)


"monir" wrote:

Bob;

Thank you.
Now try with the Product function instead of the multiplication operator "*":
{=SUM(PRODUCT(B11:INDIRECT(B32),( POWER(C14,ROW(INDIRECT(B7&":"&B8+1))-1))))}

For the same numerical example, you will get the wrong result of 3,507.704
... and not -7.071 ...

Any thoughts ??

Regards.


"Bob Phillips" wrote:

=SUM(B11:INDIRECT(B32)*( POWER(I11,ROW(INDIRECT(B7&":"&B8+1))-1)))

make sure that you do array enter it, the result you quoted in your formula
was only achieved if not array entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"monir" wrote in message
...
This is a cross-post

Hello;

The following is a failed attempt for the sum of a simple series:

The series formula is:
f(X) = Sum [k=1 to k=m+1] a(k)*X^(k-1)

Example:
lower limit in cell $B$7:: 1
value of m in cell $B$8:: 3
(m+1) values of a(k) in col B starting (always) cell B11
.........a(1) in cell B11:: -1.899
.........a(2) in cell B12:: 2.50
.........a(3) in cell B13:: 3.699
....a(m+1) in cell B14:: -3.121
(address of last cell in col B is in cell B32:: (B14 for this example)
value of X in cell $I$11:: 2.0
Retuned Sum value in cell I34::should be -7.071 ...

The array formula entered in I34:
{=SUM(PRODUCT(B11:B32, POWER(I11,ROW(INDIRECT(B7&":"&B8+1))-1)))}

returns the wrong sum of 54.807 ...
(Product and Power functions are used so that they could be replaced (if
the
need arise) with their complex number counterparts)

The problem with the above formula is that apparently it multiplies the
elements of the 1st array: B11*B12*B13*B14 instead of summing the product
of
the corresponding elements: B11*I11^0 + B12*I11^1 + B13*I11^2 + B14*I11^3
The 1st argument B11:B32 of the Product function is clearly the problem.
The 2nd argument should be OK. I think!

Your suggestion to fix the above array formula would be greatly
appreciated.

Regards.

Shane;

I've just located SERIESSUM. My apologies.
Will see if it can be used.

Regards.
(Excel 2003, Win XP)

"monir" wrote:

Shane;

There's no SERIESSUM function under Tools:ata Analysis.
Could it be under something else ??

Thank you.
(Excel 2003, Win XP)


"Shane Devenshire" wrote:

Take a look at the SERIESSUM function, it's in the Analysis ToolPak.

Cheers,
Shane

"monir" wrote in message
...
This is a cross-post

Hello;

The following is a failed attempt for the sum of a simple series:

The series formula is:
f(X) = Sum [k=1 to k=m+1] a(k)*X^(k-1)

Example:
lower limit in cell $B$7:: 1
value of m in cell $B$8:: 3
(m+1) values of a(k) in col B starting (always) cell B11
.........a(1) in cell B11:: -1.899
.........a(2) in cell B12:: 2.50
.........a(3) in cell B13:: 3.699
....a(m+1) in cell B14:: -3.121
(address of last cell in col B is in cell B32:: (B14 for this example)
value of X in cell $I$11:: 2.0
Retuned Sum value in cell I34::should be -7.071 ...

The array formula entered in I34:
{=SUM(PRODUCT(B11:B32, POWER(I11,ROW(INDIRECT(B7&":"&B8+1))-1)))}

returns the wrong sum of 54.807 ...
(Product and Power functions are used so that they could be replaced (if
the
need arise) with their complex number counterparts)

The problem with the above formula is that apparently it multiplies the
elements of the 1st array: B11*B12*B13*B14 instead of summing the product
of
the corresponding elements: B11*I11^0 + B12*I11^1 + B13*I11^2 + B14*I11^3
The 1st argument B11:B32 of the Product function is clearly the problem.
The 2nd argument should be OK. I think!

Your suggestion to fix the above array formula would be greatly
appreciated.

Regards.

Hello;

SUMMARY Conclusion and a Question.

FIRST: For Series with Real Coefficients and Real X value
------------------------------------------------------------------
There're (at least!) three formulas for the series sum:
Series Formula:
f(X) = Sum [k=1 to k=m+1] a(k)*X^(k-1)
Example 1:
-------------
lower limit in cell $B$7:: 1
value of m in cell $B$8:: 3
(m+1) values of a(k) in col B starting (always) cell B11
..........a(1) in cell B11:: -1.899
..........a(2) in cell B12:: 2.50
..........a(3) in cell B13:: 3.699
.....a(m+1) in cell B14:: -3.121
(address of last cell in col B is in cell B32 (B14 for this example), in
B32::=ADDRESS(ROW(B11)+$B$8,2,3)
value of X in cell $I$11:: 2.0
Retuned Sum value in cell I34::should be -7.071 ...

Any of the following three formulas entered in cell I34 will work fine and
returns the correct sum -7.071
The array formula:
{=SUM(B11:INDIRECT(B32)*( POWER(C14,ROW(INDIRECT(B7&":"&B8+1))-1)))}
OR
{=SUMPRODUCT(B11:INDIRECT(B32),(POWER(C14,ROW(INDI RECT(B7&":"&B8+1))-1)))}
OR
=SERIESSUM(C11,0,1,B11:INDIRECT(B32))

SECOND: For Series with Complex Coefficients and Complex X value
------------------------------------------------------------------------------
Series Formula:
f(X) = Sum [k=1 to k=m+1] a(k)*X^(k-1), with complex coefficients and
complex X value
Example 2:
-------------
lower limit in cell $B$7:: 1
value of m in cell $B$8:: 3
(m+1) values of a(k) in col B starting (always) cell D11
........a(1) in cell D11:: -1.899+1.4998i
........a(2) in cell D12:: 2.50-11.098i
........a(3) in cell D13:: 3.699+5.50i
......a(m+1) in cell B14:: -3.121+2.0i
(address of last cell in col D is in cell D32 (D14 for this example), in
D32::=ADDRESS(ROW(D11)+$B$8,2,3)
value of X in cell $I$11:: -1.41509650979734+1.56059079558358i
Retuned Sum value in cell I34::should be 0.0 (or extremely small, 1.E-12 or
so)

Analogous to the above array formulas, one would expect the following array
formula to work fine.
It returns #VALUE! instead of 0.0:
{=IMSUM(IMPRODUCT(D11:INDIRECT(D32),(
IMPOWER(I11,ROW(INDIRECT(B7&":"&B8+1))-1))))}
and if it is not array entered, it returns:
-25.7448778279517-675.866887239558i

It is worth noting: " = SUM(PRODUCT( ... " combination didn't work with real
coefficients (FIRST above) and had to be replaced by the single function
SUMPRODUCT.
Perhaps, "=IMSUM(IMPRODUCT(..." combination shouldn't be used, but there's
no IMSUMPRODUCT available !!

Any thoughts ?? Thank you kindly.
(Excel 2003, Win XP)


"monir" wrote:

Shane;

I've just located SERIESSUM. My apologies.
Will see if it can be used.

Regards.
(Excel 2003, Win XP)

"monir" wrote:

Shane;

There's no SERIESSUM function under Tools:ata Analysis.
Could it be under something else ??

Thank you.
(Excel 2003, Win XP)


"Shane Devenshire" wrote:

Take a look at the SERIESSUM function, it's in the Analysis ToolPak.

Cheers,
Shane

"monir" wrote in message
...
This is a cross-post

Hello;

The following is a failed attempt for the sum of a simple series:

The series formula is:
f(X) = Sum [k=1 to k=m+1] a(k)*X^(k-1)

Example:
lower limit in cell $B$7:: 1
value of m in cell $B$8:: 3
(m+1) values of a(k) in col B starting (always) cell B11
.........a(1) in cell B11:: -1.899
.........a(2) in cell B12:: 2.50
.........a(3) in cell B13:: 3.699
....a(m+1) in cell B14:: -3.121
(address of last cell in col B is in cell B32:: (B14 for this example)
value of X in cell $I$11:: 2.0
Retuned Sum value in cell I34::should be -7.071 ...

The array formula entered in I34:
{=SUM(PRODUCT(B11:B32, POWER(I11,ROW(INDIRECT(B7&":"&B8+1))-1)))}

returns the wrong sum of 54.807 ...
(Product and Power functions are used so that they could be replaced (if
the
need arise) with their complex number counterparts)

The problem with the above formula is that apparently it multiplies the
elements of the 1st array: B11*B12*B13*B14 instead of summing the product
of
the corresponding elements: B11*I11^0 + B12*I11^1 + B13*I11^2 + B14*I11^3
The 1st argument B11:B32 of the Product function is clearly the problem.
The 2nd argument should be OK. I think!

Your suggestion to fix the above array formula would be greatly
appreciated.

Regards.

It is worth noting: " = SUM(PRODUCT( ... " combination didn't work with real

coefficients (FIRST above) and had to be replaced by the single function
SUMPRODUCT.
Perhaps, "=IMSUM(IMPRODUCT(..." combination shouldn't be used, but there's
no IMSUMPRODUCT available !!


Hi. In general, Product doesn't thread itself very well as an Array Function.
For Example,

{=SUM(PRODUCT(A1:A4,B1:B4))}

Is the same as
{=PRODUCT(A1:B4)}

However:
=SUMPRODUCT(A1:A4,B1:B4)

could be done with this array formula as you have noted.
{=SUM(A1:A4*B1:B4)}



But I don't believe there's IMSUMPRODUCT function.


Unfortunetly, the array formula
=IMPRODUCT(A1:A4,B1:B4)

is the same as
=IMPRODUCT(A1:B4)

No simple array solution as far as I know.
--
HTH :)
Dana DeLouis


"monir" wrote in message ...

Hello;

SUMMARY Conclusion and a Question.

FIRST: For Series with Real Coefficients and Real X value
------------------------------------------------------------------
There're (at least!) three formulas for the series sum:
Series Formula:
f(X) = Sum [k=1 to k=m+1] a(k)*X^(k-1)
Example 1:
-------------
lower limit in cell $B$7:: 1
value of m in cell $B$8:: 3
(m+1) values of a(k) in col B starting (always) cell B11
.........a(1) in cell B11:: -1.899
.........a(2) in cell B12:: 2.50
.........a(3) in cell B13:: 3.699
....a(m+1) in cell B14:: -3.121
(address of last cell in col B is in cell B32 (B14 for this example), in
B32::=ADDRESS(ROW(B11)+$B$8,2,3)
value of X in cell $I$11:: 2.0
Retuned Sum value in cell I34::should be -7.071 ...

Any of the following three formulas entered in cell I34 will work fine and
returns the correct sum -7.071
The array formula:
{=SUM(B11:INDIRECT(B32)*( POWER(C14,ROW(INDIRECT(B7&":"&B8+1))-1)))}
OR
{=SUMPRODUCT(B11:INDIRECT(B32),(POWER(C14,ROW(INDI RECT(B7&":"&B8+1))-1)))}
OR
=SERIESSUM(C11,0,1,B11:INDIRECT(B32))

SECOND: For Series with Complex Coefficients and Complex X value
------------------------------------------------------------------------------
Series Formula:
f(X) = Sum [k=1 to k=m+1] a(k)*X^(k-1), with complex coefficients and
complex X value
Example 2:
-------------
lower limit in cell $B$7:: 1
value of m in cell $B$8:: 3
(m+1) values of a(k) in col B starting (always) cell D11
.......a(1) in cell D11:: -1.899+1.4998i
.......a(2) in cell D12:: 2.50-11.098i
.......a(3) in cell D13:: 3.699+5.50i
.....a(m+1) in cell B14:: -3.121+2.0i
(address of last cell in col D is in cell D32 (D14 for this example), in
D32::=ADDRESS(ROW(D11)+$B$8,2,3)
value of X in cell $I$11:: -1.41509650979734+1.56059079558358i
Retuned Sum value in cell I34::should be 0.0 (or extremely small, 1.E-12 or
so)

Analogous to the above array formulas, one would expect the following array
formula to work fine.
It returns #VALUE! instead of 0.0:
{=IMSUM(IMPRODUCT(D11:INDIRECT(D32),(
IMPOWER(I11,ROW(INDIRECT(B7&":"&B8+1))-1))))}
and if it is not array entered, it returns:
-25.7448778279517-675.866887239558i

It is worth noting: " = SUM(PRODUCT( ... " combination didn't work with real
coefficients (FIRST above) and had to be replaced by the single function
SUMPRODUCT.
Perhaps, "=IMSUM(IMPRODUCT(..." combination shouldn't be used, but there's
no IMSUMPRODUCT available !!

Any thoughts ?? Thank you kindly.
(Excel 2003, Win XP)


"monir" wrote:


Shane;

I've just located SERIESSUM. My apologies.
Will see if it can be used.

Regards.
(Excel 2003, Win XP)

"monir" wrote:


Shane;

There's no SERIESSUM function under Tools:ata Analysis.
Could it be under something else ??

Thank you.
(Excel 2003, Win XP)


"Shane Devenshire" wrote:

Take a look at the SERIESSUM function, it's in the Analysis ToolPak.

Cheers,
Shane

"monir" wrote in message
...
This is a cross-post

Hello;

The following is a failed attempt for the sum of a simple series:

The series formula is:
f(X) = Sum [k=1 to k=m+1] a(k)*X^(k-1)

Example:
lower limit in cell $B$7:: 1
value of m in cell $B$8:: 3
(m+1) values of a(k) in col B starting (always) cell B11
.........a(1) in cell B11:: -1.899
.........a(2) in cell B12:: 2.50
.........a(3) in cell B13:: 3.699
....a(m+1) in cell B14:: -3.121
(address of last cell in col B is in cell B32:: (B14 for this example)
value of X in cell $I$11:: 2.0
Retuned Sum value in cell I34::should be -7.071 ...

The array formula entered in I34:
{=SUM(PRODUCT(B11:B32, POWER(I11,ROW(INDIRECT(B7&":"&B8+1))-1)))}

returns the wrong sum of 54.807 ...
(Product and Power functions are used so that they could be replaced (if
the
need arise) with their complex number counterparts)

The problem with the above formula is that apparently it multiplies the
elements of the 1st array: B11*B12*B13*B14 instead of summing the product
of
the corresponding elements: B11*I11^0 + B12*I11^1 + B13*I11^2 + B14*I11^3
The 1st argument B11:B32 of the Product function is clearly the problem.
The 2nd argument should be OK. I think!

Your suggestion to fix the above array formula would be greatly
appreciated.

Regards.