Worksheet Formula Returning Sum of a Series

[monir]

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 Phillips]

=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)

 

[Shane Devenshire]

Hi

 

[Shane Devenshire]

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

Cheers,
Shane

 

[monir]

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.

=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)

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.

 

[monir]

Shane;

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

Thank you.
(Excel 2003, Win XP)

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

Cheers,
Shane

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.

 

[monir]

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)

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.

=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)

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.

 

[monir]

Shane;

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

Regards.
(Excel 2003, Win XP)

Shane;

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

Thank you.
(Excel 2003, Win XP)

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

Cheers,
Shane

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.

 

[monir]

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(INDIRECT(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)

Shane;

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

Regards.
(Excel 2003, Win XP)

Shane;

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

Thank you.
(Excel 2003, Win XP)

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

Cheers,
Shane

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.

 

[monir]

Hi Dana;

Good to hear from you and thank you for your thoughtful reply.

1) Let me first discuss one of your examples:
{=SUM(PRODUCT(A1:A4,B1:B4))}

It works fine for such simple arrays.
However, as I mentioned in my previous reply, If I use such formation in my
Example 1 (with real coefficients and real X value):
{=SUM(PRODUCT(B11:INDIRECT(B32)*( POWER(C14,ROW(INDIRECT(B7&":"&B8+1))-1))))}
it returns the wrong result 3,507.705 ...
The other three formulas (with no PRODUCT) return the correct result -7.071
....:
{=SUM(B11:INDIRECT(B32)*( POWER(C14,ROW(INDIRECT(B7&":"&B8+1))-1)))}
OR
{=SUMPRODUCT(B11:INDIRECT(B32),(POWER(C14,ROW(INDIRECT(B7&":"&B8+1))-1)))}
OR
=SERIESSUM(C11,0,1,B11:INDIRECT(B32))

So, it seems to me that there is something incompatible between my formula
and the PRODUCT function or the SUM / PRODUCT combination!

2) Regarding my Example 2 (with complex coefficient 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
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
.....(m+1) in cell D14:: -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)

The following array formula returns #VALUE! instead of 0.0:
{=IMSUM(IMPRODUCT(D11:INDIRECT(D32),(
IMPOWER($I$11,ROW(INDIRECT(B7&":"&B8+1))-1))))}

3) If one evaluates the above formula:
Tools::Formula Auditing::Evaluate Formula
one would quickly realize that there's something fundamentally incorrect!

4) The Evaluate Formula window displays right away:
"This is a circular reference and may not evaluate to a result you expect."
However, Excel seems to display the message for evaluating any array
formula. So, I simply ignore it!

5) The Evaluate process continues nicely and at some point it displays
correctly the elements of the 2nd argument of IMPRODUCT :
......., (IMPOWER($I$11, {0;1;2;3}))))
The next Evaluate would evaluate:
......, (IMPOWER($I$11, {0;1;2;3})))) to:
......, (#VALUE!)))

Shouldn't the above 2nd argument be simply evaluated to:
......., ($I$11^0; $I$11^1,$I$11^2, $I$11^3 )))) according to the above
notations ??

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

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

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(INDIRECT(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)

Shane;

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

Regards.
(Excel 2003, Win XP)

:

Shane;

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

Thank you.
(Excel 2003, Win XP)


:

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

Cheers,
Shane

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.

 

[monir]

Hi;

Clearly you're very knowledgeable and experienced in XL and VBA.
See if you agree with me!

1) To the best I can tell, the XL functions PRODUCT(,) and IMPRODUCT(,)
perform 1D multiplication of the supplied arguments, regardless of whether
one uses ":" and/or "," to separate the arguments.
For example, the following formulas (each entered as an array or a non-array
single-result formula):
=PRODUCT(A1:A4,B1:B4)
=PRODUCT(A1:A4:B1:B4)
=PRODUCT(A1:B4)
would produce the identical result:
= A1*A2*A3*A4*B1*B2*B3*B4

2) That's precisely why you were getting the same (but not the expected)
results from:
{=SUM(PRODUCT(A1:A4,B1:B4))}
and
{=PRODUCT(A1:A4,B1:B4)}
since there's no more than one term to sum!

3) On the other hand, the formulas:
=SUMPRODUCT(A1:A4,B1:B4)
and
{=SUM(A1:A4*B1:B4)}
each correctly performs the array elements multiplication and summation as
advertised!

4) How to adapt 3. above to complex numbers ?? IMSUMPRODUCT function
doesn't exist and one can't use the multiplication "*" operator with complex
numbers ??
(Is IMSUMPRODUCT available in XL 2007 by any chance ?? I doubt it)
In any event, I can see clearly now why the array formula in 5. below
returns #VALUE! instead of the correct result 0.0:

5) Example 2:
{=IMSUM(IMPRODUCT(D11:INDIRECT(D32),(
IMPOWER($I$11,ROW(INDIRECT(B7&":"&B8+1))-1))))}

Series:
f(X) = Sum [k=1 to k=m+1] a(k)*X^(k-1), with complex coefficients and
complex X value
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)

The problem once again is that the function IMPRODUCT(,) simply multiplies
ALL the elements of the two arrays specified as its arguments, and thus the
function IMSUM does nothing.

6) Here's a thought. I wonder if one can somehow breakdup (tweak may be a
better word) the above formula such that IMPRODUCT is applied sequentially to
the required m+1 multiplications and thus allow IMSUM to do its job ??
Keep in mind that both PRODUCT and IMPRODUCT would work fine in our case
provided that the supplied arguments of the functions are limited to one
element per each array argument, e.g.;
=PRODUCT(B11,$I$11^2) would work fine for real coefficients and real X value
=IMPRODUCT(D11,$I$11^3) would work fine for complex coefficients and complex
X value

Any thoughts ?? Thanks again for your interest and help.
(Excel 2003, Win XP)

{=SUM(PRODUCT(A1:A4,B1:B4))}
It works fine for such simple arrays.

Well, in Excel 2007 anyway, we get different answers.
This sets up an example.
On my system, D1 = D2, and they are much different that D3.

Sub Demo()
[A1:B4] = [{1,2;3,4;5,6;7,8}]
[D1].FormulaArray = "=Sum(Product(A1:A4,B1:B4))"
[D2].Formula = "=Product(A1:B4)"

[D3].Formula = "=SumProduct(A1:A4,B1:B4)"

' The two different answers are:
Debug.Print [Sum(Product(A1:A4,B1:B4))]
Debug.Print [SumProduct(A1:A4,B1:B4)]
End Sub

The two solutions are:
40320
100

As a suggestion, it may be better to post a small sample of data beginning
in A1 so that it is easy for others to set up.
Perhaps only 3-4 rows of data, along with the expected results.
Again, just a suggestion. :>)

--
Dana DeLouis

Hi Dana;

Good to hear from you and thank you for your thoughtful reply.

1) Let me first discuss one of your examples:
{=SUM(PRODUCT(A1:A4,B1:B4))}

It works fine for such simple arrays.
However, as I mentioned in my previous reply, If I use such formation in my
Example 1 (with real coefficients and real X value):
{=SUM(PRODUCT(B11:INDIRECT(B32)*( POWER(C14,ROW(INDIRECT(B7&":"&B8+1))-1))))}
it returns the wrong result 3,507.705 ...
The other three formulas (with no PRODUCT) return the correct result -7.071
...:
{=SUM(B11:INDIRECT(B32)*( POWER(C14,ROW(INDIRECT(B7&":"&B8+1))-1)))}
OR
{=SUMPRODUCT(B11:INDIRECT(B32),(POWER(C14,ROW(INDIRECT(B7&":"&B8+1))-1)))}
OR
=SERIESSUM(C11,0,1,B11:INDIRECT(B32))

So, it seems to me that there is something incompatible between my formula
and the PRODUCT function or the SUM / PRODUCT combination!

2) Regarding my Example 2 (with complex coefficient 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
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
....(m+1) in cell D14:: -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)

The following array formula returns #VALUE! instead of 0.0:
{=IMSUM(IMPRODUCT(D11:INDIRECT(D32),(
IMPOWER($I$11,ROW(INDIRECT(B7&":"&B8+1))-1))))}

3) If one evaluates the above formula:
Tools::Formula Auditing::Evaluate Formula
one would quickly realize that there's something fundamentally incorrect!

4) The Evaluate Formula window displays right away:
"This is a circular reference and may not evaluate to a result you expect."
However, Excel seems to display the message for evaluating any array
formula. So, I simply ignore it!

5) The Evaluate process continues nicely and at some point it displays
correctly the elements of the 2nd argument of IMPRODUCT :
......, (IMPOWER($I$11, {0;1;2;3}))))
The next Evaluate would evaluate:
......, (IMPOWER($I$11, {0;1;2;3})))) to:
......, (#VALUE!)))

Shouldn't the above 2nd argument be simply evaluated to:
......, ($I$11^0; $I$11^1,$I$11^2, $I$11^3 )))) according to the above
notations ??

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

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


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(INDIRECT(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)


:

Shane;

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

Regards.
(Excel 2003, Win XP)

:

Shane;

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

Thank you.
(Excel 2003, Win XP)


:

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

Cheers,
Shane

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

 

[monir]

Hi Dana;

Thanks again and I apologize for the delay in responding to your latest
reply couple of days ago.

1) As you correctly suggested, one could easily add couple of columns on the
w/s to calculate the complex poly terms and use the w/s IMSUM function to add
the relevant terms. As a matter of fact, I've already done that to check the
result from the array function.
But don't you agree that a neat array formula returning a single result
would be more efficient ??

2) OK. IMSUMPRODUCT is not available in XL 2007. Maybe a better luck with
IMSERIESSUM ??

3) If you kindly re-examine my failed attempt:
{=IMSUM(IMPRODUCT(D11:INDIRECT(D32),(
IMPOWER(I11,ROW(INDIRECT(B7&":"&B8+1))-1))))}

Or for the sake of discussion, let us assume that we have two 1D arrays,
each of m complex elements:
a(1):a(m); starting in cell A1
b(1):b(m); starting in cell B1
and m is given in cell B8
So we need the above formals to read something like (symbolically):
=IMSUM[k=1 to k=m] ( IMPRODUCT(A(k),B(k)) )

I'm trying to convince myself that even with the complexity of Excel
there're nevertheless some limitations to what one can do!

Kind regards.
(XL 2003, Win XP)

Hi. I believe 1-3 are correct.
Also, in general, many of the Engineering functions just don't lend themselves to being used in Array functions. It's too bad.
To Sum all the cells in two ranges, one would use
=SUM(A1:A4,C1:C4)
To Multiply each cell together, one would use
=PRODUCT(A1:A4,C1:C4)

As an array function, I believe the built in function PRODUCT does not have the logic to switch its definition to what we want.
Hence, I believe that is why the Function "SumProduct" was added.

4) How to adapt 3. above to complex numbers ?? IMSUMPRODUCT function
doesn't exist and one can't use the multiplication "*" operator with complex
numbers ??
(Is IMSUMPRODUCT available in XL 2007 by any chance ??

Unfortunately, IMSUMPRODUCT is not in XL 2007. :>(
In fact, most of the Engineering functions just don't work when used in Array Formulas.

Having said that, you may be interested in the following just for Gee Wiz.
You can do this on a worksheet with Range Names, but I'll do this here in vba.
Sometimes, it can be useful to keep the Real & Imaginary values in separate arrays. (depending on what one is doing of course)
For example, in vba, if we were to multiply two 4,000 digit numbers together via Fourier Transform, I find it much faster to keep the Real & Imaginary numbers in separate arrays.
Anyway, here's one technique that avoids programming loops.

Let me get / check some test data via a math program to make sure this is correct.

If we have two vectors of complex numbers.

v = {9 + 7*I, 1 + 7*I, 8 + 3*I}
w = {5 + I, 6 + 2*I, 2 + 4*I}

Then the SumProduct (known as the Dot Product in math terms) is:
v . w

34 + 126*I

So, for the challenge of not writing program loops:

Sub Demo()
Dim ar, ai, br, bi
Dim Re, Im

ar = Array(9, 1, 8) 'Vector A - Reals
ai = Array(7, 7, 3) 'Vector A - Imag
br = Array(5, 6, 2) 'Vector B - Reals
bi = Array(1, 2, 4) 'Vector B - Imag

' Workaround
With ActiveWorkbook.Names
.Add "ar", ar
.Add "ai", ai
.Add "br", br
.Add "bi", bi
End With

Re = [Sum(ar*br,-ai*bi)]
Im = [Sum(ar*bi,ai*br)]
Debug.Print Re
Debug.Print Im
End Sub

This returns the correct solution of:
34
126

Anyway, not what you wanted, but I thought you might find it interesting.
--
HTH :>)
Dana DeLouis

Hi;

Clearly you're very knowledgeable and experienced in XL and VBA.
See if you agree with me!

1) To the best I can tell, the XL functions PRODUCT(,) and IMPRODUCT(,)
perform 1D multiplication of the supplied arguments, regardless of whether
one uses ":" and/or "," to separate the arguments.
For example, the following formulas (each entered as an array or a non-array
single-result formula):
=PRODUCT(A1:A4,B1:B4)
=PRODUCT(A1:A4:B1:B4)
=PRODUCT(A1:B4)
would produce the identical result:
= A1*A2*A3*A4*B1*B2*B3*B4

2) That's precisely why you were getting the same (but not the expected)
results from:
{=SUM(PRODUCT(A1:A4,B1:B4))}
and
{=PRODUCT(A1:A4,B1:B4)}
since there's no more than one term to sum!

3) On the other hand, the formulas:
=SUMPRODUCT(A1:A4,B1:B4)
and
{=SUM(A1:A4*B1:B4)}
each correctly performs the array elements multiplication and summation as
advertised!

4) How to adapt 3. above to complex numbers ?? IMSUMPRODUCT function
doesn't exist and one can't use the multiplication "*" operator with complex
numbers ??
(Is IMSUMPRODUCT available in XL 2007 by any chance ?? I doubt it)
In any event, I can see clearly now why the array formula in 5. below
returns #VALUE! instead of the correct result 0.0:

5) Example 2:
{=IMSUM(IMPRODUCT(D11:INDIRECT(D32),(
IMPOWER($I$11,ROW(INDIRECT(B7&":"&B8+1))-1))))}

Series:
f(X) = Sum [k=1 to k=m+1] a(k)*X^(k-1), with complex coefficients and
complex X value
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)

The problem once again is that the function IMPRODUCT(,) simply multiplies
ALL the elements of the two arrays specified as its arguments, and thus the
function IMSUM does nothing.

6) Here's a thought. I wonder if one can somehow breakdup (tweak may be a
better word) the above formula such that IMPRODUCT is applied sequentially to
the required m+1 multiplications and thus allow IMSUM to do its job ??
Keep in mind that both PRODUCT and IMPRODUCT would work fine in our case
provided that the supplied arguments of the functions are limited to one
element per each array argument, e.g.;
=PRODUCT(B11,$I$11^2) would work fine for real coefficients and real X value
=IMPRODUCT(D11,$I$11^3) would work fine for complex coefficients and complex
X value

Any thoughts ?? Thanks again for your interest and help.
(Excel 2003, Win XP)

{=SUM(PRODUCT(A1:A4,B1:B4))}
It works fine for such simple arrays.

Well, in Excel 2007 anyway, we get different answers.
This sets up an example.
On my system, D1 = D2, and they are much different that D3.

Sub Demo()
[A1:B4] = [{1,2;3,4;5,6;7,8}]
[D1].FormulaArray = "=Sum(Product(A1:A4,B1:B4))"
[D2].Formula = "=Product(A1:B4)"

[D3].Formula = "=SumProduct(A1:A4,B1:B4)"

' The two different answers are:
Debug.Print [Sum(Product(A1:A4,B1:B4))]
Debug.Print [SumProduct(A1:A4,B1:B4)]
End Sub

The two solutions are:
40320
100

As a suggestion, it may be better to post a small sample of data beginning
in A1 so that it is easy for others to set up.
Perhaps only 3-4 rows of data, along with the expected results.
Again, just a suggestion. :>)

--
Dana DeLouis


Hi Dana;

Good to hear from you and thank you for your thoughtful reply.

1) Let me first discuss one of your examples:
{=SUM(PRODUCT(A1:A4,B1:B4))}

It works fine for such simple arrays.
However, as I mentioned in my previous reply, If I use such formation in
my
Example 1 (with real coefficients and real X value):
{=SUM(PRODUCT(B11:INDIRECT(B32)*(
POWER(C14,ROW(INDIRECT(B7&":"&B8+1))-1))))}
it returns the wrong result 3,507.705 ...
The other three formulas (with no PRODUCT) return the correct
result -7.071
...:
{=SUM(B11:INDIRECT(B32)*( POWER(C14,ROW(INDIRECT(B7&":"&B8+1))-1)))}
OR

{=SUMPRODUCT(B11:INDIRECT(B32),(POWER(C14,ROW(INDIRECT(B7&":"&B8+1))-1)))}
OR
=SERIESSUM(C11,0,1,B11:INDIRECT(B32))

So, it seems to me that there is something incompatible between my
formula
and the PRODUCT function or the SUM / PRODUCT combination!

2) Regarding my Example 2 (with complex coefficient 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
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
....(m+1) in cell D14:: -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)

The following array formula returns #VALUE! instead of 0.0:
{=IMSUM(IMPRODUCT(D11:INDIRECT(D32),(
IMPOWER($I$11,ROW(INDIRECT(B7&":"&B8+1))-1))))}

3) If one evaluates the above formula:
Tools::Formula Auditing::Evaluate Formula
one would quickly realize that there's something fundamentally
incorrect!

4) The Evaluate Formula window displays right away:
"This is a circular reference and may not evaluate to a result you
expect."
However, Excel seems to display the message for evaluating any array
formula. So, I simply ignore it!

5) The Evaluate process continues nicely and at some point it displays
correctly the elements of the 2nd argument of IMPRODUCT :
......, (IMPOWER($I$11, {0;1;2;3}))))
The next Evaluate would evaluate:
......, (IMPOWER($I$11, {0;1;2;3})))) to:
......, (#VALUE!)))

Shouldn't the above 2nd argument be simply evaluated to:
......, ($I$11^0; $I$11^1,$I$11^2, $I$11^3 )))) according to the above
notations ??

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


:

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.

 

[monir]

I'm sorry Dana. I've just posted a reply to what I thought your latest, not
realizing that you had kindly posted two more. My apologies!

Are you sure there's a vba function IMSERIESSUM, which I asked you about
earlier today ??
Please allow me some time to re-check since I'm sure it's not available in
XL 2003, but I could be wrong!

Kind regards.
(XL 2003, Win XP)

1.E-12

Hi. Let me change this a little.
If your input data was exact, then another program shows both the real &
imaginary numbers to be very small (not zero, but ~*10^-14)
Usually, in these types of programs, we try to avoid using "Power"
functions where possible, and I think that's where the problem was.
Hence, let me rewrite it as this:

Now I get:
9.9475983006414E-14-5.99520433297585E-14i
which I think is a little bit closer.

Function ImSeriesSum(Rng As Range, X)
Dim Cell As Range
Dim p As Long
Dim k
Dim Ans

k = 1#
With WorksheetFunction
For Each Cell In Rng.Cells
Ans = .ImSum(Ans, .ImProduct(Cell, k))
k = .ImProduct(k, X)
p = p + 1
Next Cell
End With
ImSeriesSum = Ans
End Function

--
Dana DeLouis

Hi. Don't know if you would want this, but here's a vba function to do what you want.
One advantage is that you do not need helper cells on your worksheet.
I have excel 2007, so the engineering functions are built in.
You may have to set a vba library reference to the atp functions.
In this version, the Real part went to 0, and the imaginary part was

very small (-3.99680288865056E-14i)

You can run "SetUp" below to set up your test data.


Function ImSeriesSum(Rng As Range, X)
'// = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
'// For Complex Numbers in Excel, solves SumProduct
'// (ie Dot Product of the two vectors)
'// {a,b,c,d} . {1, x^1, x^2, x^3} = a + b x + c x^2 + d x^3
'// = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

Dim Cell As Range
Dim p As Long
Dim Ans

With WorksheetFunction
For Each Cell In Rng.Cells
Ans = .ImSum(Ans, .ImProduct(Cell, .ImPower(X, p)))
p = p + 1
Next Cell
End With

ImSeriesSum = Ans
End Function


Sub SetUp()
[A1] = "=COMPLEX(-1.899,1.4998)"
[A2] = "=COMPLEX(2.5,-11.098)"
[A3] = "=COMPLEX(3.699,5.5)"
[A4] = "=COMPLEX(-3.121,2)"

' C1 holds your 'X value
[C1] = "=COMPLEX(-1.41509650979734,1.56059079558358)"

[C3].Formula = "=imSERIESSUM(A1:A4,C1)"
End Sub

Returned:
-3.99680288865056E-14i

--
HTH :>)
Dana DeLouis

<snip>

 

[monir]

Hi Dana;

1) Your function ImSeriesSum(Rng As Range, X) works perfectly.
The following function is a bit simpler, more adaptable to the situation at
hand, has been successfully tested, and works as well:

Function MyImSeriesSum (m, x)
' the vba ImSeriesSum() is not available in XL 2003.
' MyImSeriesSum (m, x) evaluates poly of deg "m" with complex or real
coefficients, at complex or real value x.
' had the vba ImSeriesSum function been available, one would've entered it
directly into the cell on the w/s:
' =ImSeriesSum($I$11,0,1,$D$11:INDIRECT($D$32))
' x value is in cell $I$11;
' poly coeffs in col D starting at cell $D$11 with the const term
' $D$32 has the address of the last coeff. of the poly
' In cell D32::=ADDRESS(ROW(D11)+$B$8,4,3)
' the poly deg m is specified in cell $B$8

Dim j As Integer
Dim mySum As String
ReDim a(m + 1) As String 'poly complex coefficients
mySum = 0
For j = 1 To m + 1
a(j) = Cells(11 + j - 1, 4)
mySum = IMSUM(mySum, IMPRODUCT(a(j), IMPOWER(x, j - 1)))
Next j
MyImSeriesSum = mySum
End Function

2) For m=3, values of a(k), k=1, m+1
.......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 D14:: -3.121+2.0i
value of X in cell $I$11:: -1.41509650979734+1.56059079558358i
Exact Sum value is 0.0

3) Returned result from MyImSeriesSum() is:
9.9475983006414E-014 - 3.99680288865056E-014i
which is close enough to 0.0. Agree ??

4) The remaining/resulting difficulty, or rather inconvenience, is that all
the formulas on the w/s have now become volatile when opening the w/b, and
I've to use CTRL+ALT+F9 to restore the values to the formula and referenced
cells.
That's despite the fact that I DO NOT use Volatile Functions, or Events, or
volatile option in the w/b.
I've recently posted some details under the thread: "Formula Result =
Volatile ??".

Once again, thank you kindly for your tremendous help, time, and patience in
resolving the issue. I've learned a lot in the process, and there is much to
learn!
(XL 2003, Win XP)

Are you sure there's a vba function IMSERIESSUM,

Hi. No, it's a custom function that you can add to your module.
It's just something I passed along in case you wanted to use it.
It solves the following equation, where the variables could be complex.

' a + b*x + c*x^2 + d*x^3 ...etc

When working with such polynomials, it can sometimes be more numerically
stable to re-write the equation as the following.
However, for this particular problem, it didn't help us out much.
You would place the function on a regular module sheet.
Then, on a worksheet, you could use it like this:

=ImSeriesSum(A1:A4,C1)

If you send me your email address, I'll be glad to send you the workbook
if you think it would help.

Function ImSeriesSum(Rng As Range, X)
' Rng is a single-column range on a worksheet
' holding complex values (or real's for that matter)

Dim j As Long
Dim Ans
Dim M

M = Rng.Cells.Value
Ans = M(UBound(M, 1), 1)

With WorksheetFunction
For j = UBound(M, 1) - 1 To 1 Step -1
Ans = .ImSum(M(j, 1), .ImProduct(Ans, X))
Next j
End With
ImSeriesSum = Ans
End Function

--
HTH :>)
Dana DeLouis

I'm sorry Dana. I've just posted a reply to what I thought your latest, not
realizing that you had kindly posted two more. My apologies!

Are you sure there's a vba function IMSERIESSUM, which I asked you about
earlier today ??
Please allow me some time to re-check since I'm sure it's not available in
XL 2003, but I could be wrong!

Kind regards.
(XL 2003, Win XP)

Retuned Sum value in cell I34::should be 0.0 (or extremely small,
1.E-12

Hi. Let me change this a little.
If your input data was exact, then another program shows both the real &
imaginary numbers to be very small (not zero, but ~*10^-14)
Usually, in these types of programs, we try to avoid using "Power"
functions where possible, and I think that's where the problem was.
Hence, let me rewrite it as this:

Now I get:
9.9475983006414E-14-5.99520433297585E-14i
which I think is a little bit closer.

Function ImSeriesSum(Rng As Range, X)
Dim Cell As Range
Dim p As Long
Dim k
Dim Ans

k = 1#
With WorksheetFunction
For Each Cell In Rng.Cells
Ans = .ImSum(Ans, .ImProduct(Cell, k))
k = .ImProduct(k, X)
p = p + 1
Next Cell
End With
ImSeriesSum = Ans
End Function

--
Dana DeLouis


(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)

Hi. Don't know if you would want this, but here's a vba function to do
what you want.
One advantage is that you do not need helper cells on your worksheet.
I have excel 2007, so the engineering functions are built in.
You may have to set a vba library reference to the atp functions.
In this version, the Real part went to 0, and the imaginary part was
very small (-3.99680288865056E-14i)
You can run "SetUp" below to set up your test data.


Function ImSeriesSum(Rng As Range, X)
'// = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
'// For Complex Numbers in Excel, solves SumProduct
'// (ie Dot Product of the two vectors)
'// {a,b,c,d} . {1, x^1, x^2, x^3} = a + b x + c x^2 + d x^3
'// = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

Dim Cell As Range
Dim p As Long
Dim Ans

With WorksheetFunction
For Each Cell In Rng.Cells
Ans = .ImSum(Ans, .ImProduct(Cell, .ImPower(X, p)))
p = p + 1
Next Cell
End With

ImSeriesSum = Ans
End Function


Sub SetUp()
[A1] = "=COMPLEX(-1.899,1.4998)"
[A2] = "=COMPLEX(2.5,-11.098)"
[A3] = "=COMPLEX(3.699,5.5)"
[A4] = "=COMPLEX(-3.121,2)"

' C1 holds your 'X value
[C1] = "=COMPLEX(-1.41509650979734,1.56059079558358)"

[C3].Formula = "=imSERIESSUM(A1:A4,C1)"
End Sub

Returned:
-3.99680288865056E-14i

--
HTH :>)
Dana DeLouis

<snip>

 

[monir]

Hi Dana;

1) Rounding complex numbers in Excel:
With my Function MyImSeriesSum() entered in cell $I$38 which returns the
value:
9.9475983006414E-014-3.99680288865056E-014i
I have in the next cell $K$38:
=COMPLEX(ROUND(IMREAL($I$38),8),ROUND(IMAGINARY($I$38),8))
which returns 0.
The above w/s Round formula is exactly as you suggested in your Sub
IfYouWant(), but without "+0" as you also correctly predicted.

2) Your observation regarding roundoff and accumulated roundoff errors for
high-power polynomials is well taken.
Perhaps we should avoid calculating "powers of x" for polynomials.
Suppose we need to evaluate a quartic polynomial at x.
f(x) = a1 + a2 x + a3 x^2 + a4 x^3 + a5 x^4 ....(*)
hence:
f(x) = a1 + x(a2 + x(a3 + x(a4 + x(a5)))) ..........(**)

Can we algorithmically evaluate (**) using only IMSUM and IMPRODUCT (or SUM
and Product for real numbers) ??
The answer is a qualified: "Yes we can!" (Sorry, I've been listing to the
news too much!)

3) I'll try to adjust the main loop based on (**) above and get rid of
IMPOWER altogether, and let you know if successful:

Function MyImSeriesSum (m, x)
'..........some code
mySum = 0
For j = 1 To m + 1
a(j) = Cells(11 + j - 1, 4)
mySum = IMSUM(mySum, IMPRODUCT(a(j), IMPOWER(x, j - 1)))
Next j
MyImSeriesSum = mySum
End Function

Kind regards.
(XL 2003, Win XP)

 

[monir]

Hi Dana;

1) I'll try your Function ImSeriesSum(Rng As Range, X) first thing in the
morning (in about 4 hrs!). It's neat and looks professional!

2) For now, here's a modified working version of my earlier UDF without the
use of IMPOWER:

Function MyImSeriesSum2(m, x)
' e.g.; for a quartic poly: m=4, f(x) = a1 + x( a2 + x( a3 + x( a4 + x( a5
))))
' for a quintic poly: m=5, f(x) = a1 + x( a2 + x( a3 + x( a4 + x( a5
+ x(a6)))))
' m >= 2

Dim j As Integer
Dim mySum As String
ReDim a(m + 1) As String 'poly complex coefficients
..
For j = 1 To m + 1 'assign poly coeffs, w/s col D, starting D11: const
term, x coeff, x^2 coeff, x^3 coeff, etc.
a(j) = Cells(11 + j - 1, 4)
Next j

mySum = ImProduct(x, IMSUM(a(m), ImProduct(x, a(m + 1)))) 'last term of
polynomial
For j = m To 3 Step -1 'calculate remaining terms of poly excluding const
term
mySum = IMSUM(ImProduct(x, a(j - 1)), ImProduct(x, mySum))
Next j
mySum = IMSUM(a(1), mySum) 'add constant term

MyImSeriesSum2 = mySum
End Function

3) For the same numerical example posted earlier, the above function returns:
9.99200722162641E-014
compared with the earlier results (using IMPOWER):
9.9475983006414E-014 - 3.99680288865056E-014i

4) For 8th deg poly, the results are:
1.97669991486293E-009 - 3.57939988759881E-009i
compared with (using IMPOWER):
1.97679916880134E-009 - 3.57938745310094E-009i

Insignificant differences really! Agree ??

I would be glad to send you a "clean" copy of the XL w/b with Function
ZRoots2() and Sub Laguer2(). I should be able to find your work email
address.

Regards.
(XL 2003, Win XP)

 

[monir]

Hi Dana;

Your Function ImSeriesSum(Rng As Range, X) works fine and produces almost
identical results to mine.
The only difficulty is that it accepts the range of coeffs as argument,
while the other functions on the w/s require the deg of poly "m" instead and
assume the (m+1) complex coeffs are in col D starting at D11. Difficult to
remember the difference later on!

Here's my latest:

Function MyImSeriesSum2(m, x)
' e.g.; eval quartic poly: m=4, f(x) = a1 + x( a2 + x( a3 + x( a4 + x( a5))))
' eval quintic poly: m=5, f(x) = a1 + x( a2 + x( a3 + x( a4 + x( a5+
x(a6)))))
' poly deg m >= 2

Dim j As Integer
Dim mySum As String
ReDim a(m + 1) As String 'poly complex coefficients

For j = 1 To m + 1 'assign poly coeffs, w/s col D, starting D11: const
term, x coeff, x^2 coeff, etc.
a(j) = Cells(11 + j - 1, 4)
Next j

mySum = ImProduct(X, a(M + 1)) 'innermost term of polynomial
For j = m+1 To 3 Step -1 'remaining terms excluding const term
mySum = IMSUM(ImProduct(x, a(j - 1)), ImProduct(x, mySum))
Next j
mySum = IMSUM(a(1), mySum) 'add const term

MyImSeriesSum2 = mySum
End Function

Regards.