Complex Number Formatting

[Frank]

While trying to set up a vector "Cross product matrix".
-- technically not really a matrix, but rather just
mechanically calculating the determinants from the
entries within a square matrix. I ran into a major
formatting irritant with Excel. When I convert
the polar form of a complex number from two cells
to rectangular form within a single cell:
=COMPLEX(D8*COS(E8*PI()/180),D8*SIN(E8*PI()/180));
the cell size expands to 42 digits in scientific
format, for example, as follows:
2.2998663682995E-005+2.0404386284536E-006i.
The problem appears to lie in the fact that Excel treats
complex numbers as text, and they cannot be formatted
in the normal way. I have searched several bookstores,
and looked at every major book on Excel, and VBA
macros. Complex numbers are treated in only a
superficial way, or not at all.

Microsoft does discuss the problem concerning XL2000 at:
http://support.microsoft.com/?kbid=213294
The VB code does not make a lot of sense to me,
also in particular the statement:
"=FormatComplex(A1,"0.00","0.0000")" does not
appear to work (Where would I put it anyway),
nor does their "Sample VBA Procedure". This may
be due to the fact that I am using Excel 97, so I
may just have to live with the very large cells.

Be interested to know if anybody else has observed
this problem.

Regards,

Frank

 

[Stephen Bye]

You can extract the real and imaginary parts, round them to whatever
precision you like, and then recombine them. Foe example:
=COMPLEX(ROUND(IMREAL(A1),2),ROUND(IMAGINARY(A1),2))

 

[Frank's]

You can extract the real and imaginary parts, round them to whatever
precision you like, and then recombine them. Foe example:
=COMPLEX(ROUND(IMREAL(A1),2),ROUND(IMAGINARY(A1),2))

Thanks very much Stephen, it works perfectly. I am kind of
embarrassed that I could not find the answer in some book,
or on the web. Now I know what to look for, I can find
all kinds of references to the command.

Regards,

Frank

 

[Herbert Seidenberg]

But the problem reappears if you do IMPRODUCT on the rounded up
vectors.

 

[Stephen Bye]

But the problem reappears if you do IMPRODUCT on the rounded up
vectors.

So you just round them again, exactly the same as you would do with ordinary
numbers...

 

[Herbert Seidenberg]

Let's actually try an example in Excel.
Our aim is to have 3 digits to the right of the decimal point.
Here are four numbers starting at A1:
4.123
2.568
4.897
8.124
We enter these formulas at A5 and A6
=COMPLEX(A1,A2)
=COMPLEX( A3,A4)
The results are:
4.123+2.568i
4.897+8.124i
Now we want the product of these 2 vectors.
=IMPRODUCT(A5,A6)
The result is:
-0.672101000000001+46.070748i
If we now try to round the vectors with ROUND(),
exactly as in your proposed formula:
=IMPRODUCT(ROUND(A5,3),ROUND(A6,3))
we get #VALUE!

 

[Stephen Bye]

Let's actually try an example in Excel.
Our aim is to have 3 digits to the right of the decimal point.
Here are four numbers starting at A1:
4.123
2.568
4.897
8.124
We enter these formulas at A5 and A6
=COMPLEX(A1,A2)
=COMPLEX( A3,A4)
The results are:
4.123+2.568i
4.897+8.124i
Now we want the product of these 2 vectors.
=IMPRODUCT(A5,A6)
The result is:
-0.672101000000001+46.070748i
If we now try to round the vectors with ROUND(),
exactly as in your proposed formula:
=IMPRODUCT(ROUND(A5,3),ROUND(A6,3))
we get #VALUE!

Option 1: If your IMPRODUCT(A5,A6) result is in A7, you can put the rounded
version of it in A8 by using
=COMPLEX(ROUND(IMREAL(A7),2),ROUND(IMAGINARY(A7),2))

Option 2: If you just want the rounded result without the intermediate
accurate value, in A7 put:
=COMPLEX(ROUND(IMREAL(IMPRODUCT(A5,A6)),2),ROUND(IMAGINARY(IMPRODUCT(A5,A6)),2))

 

[Herbert Seidenberg]

This might not be satisfactory if the user wants to work with
an accuracy of say 5 places, but display only 3 places.
The VBA code that Frank mentioned in his post works quite well
for me (Excel 2003)
Frank wrote:
=FormatComplex(A1,"0.00","0.0000") does not
appear to work (Where would I put it anyway)
I am using Excel 97.

In my example, change the formula to
=FormatComplex(A7,"0.000","0.000")
and put it in A8.

 

[Stephen Bye]

This might not be satisfactory if the user wants to work with
an accuracy of say 5 places, but display only 3 places.

You can't choose the accuracy that Excel works with.
The FormatComplex function doesn't change how the complex number is
displayed, it produces a new complex number with the specified precision,
exactly the same thing that my originally posted formula does.

 

[Frank's]

This might not be satisfactory if the user wants to work with
an accuracy of say 5 places, but display only 3 places.
The VBA code that Frank mentioned in his post works quite well
for me (Excel 2003)
Frank wrote:
=FormatComplex(A1,"0.00","0.0000") does not
appear to work (Where would I put it anyway)
I am using Excel 97.

In my example, change the formula to
=FormatComplex(A7,"0.000","0.000")
and put it in A8.

Thanks for your comments Herbert. Unfortunately it does not
work for me.

In my particular test case I have the following in cell Q9:
=COMPLEX((E9*COS(F9*PI()/180)),(E9*SIN(F9*PI()/180))).
Which produces:
0.112960544981105+9.30181927236372E-002i
The source cells, E9 and F9, contain the complex number in polar form;
with E9 containing the magnitude, and F9 the angle in degrees.

The way I understand it I could put the "=FormatComplex(......) in
any cell, as long as it referred to the cell requiring formatting.
Anyway I placed " =FormatComplex(Q9,"0.000","0.000")" in
cell R9. This did nothing to cell Q9, and cell R9 shows:
#NAME? This may be because I am using Excel 97, which
possibly does not support the command.

Even if it worked for me, I have over 40 columns, so would make the
spread sheet very large. Of course there may be a variant; where a
range of cells could be formatted, such as:
=FormatComplex(Q9:Q99,"0.000","0.000").

For the moment I am using the "ROUND" command. My overall
accuracy is within 0.2%, which is perfectly acceptable. The fact
is the error could be due to my source data from NEC 4.1.

Regards,

Frank

 

[Tom Ogilvy]

If you get the NAME error, then you have put the code in the wrong place.
Not because you are using Excel 97. It should go in a general module, not
in a sheet module.

When I did that, it worked fine for me in Excel 97 - either refering to a
cell with the your formula or directly as

=FormatComplex(COMPLEX(D8*COS(E8*PI()/180),D8*SIN(E8*PI()/180)),"0.00","0.0000")

 

[Dana]

A little off topic, but an alternative to your main equation might be
something like this:

=COMPLEX((E9*COS(F9*PI()/180)),(E9*SIN(F9*PI()/180))).

=IMEXP(COMPLEX(LN(E9),RADIANS(F9)))

--
Dana DeLouis
Windows XP, Office 2003

In my particular test case I have the following in cell Q9:
=COMPLEX((E9*COS(F9*PI()/180)),(E9*SIN(F9*PI()/180))).
Which produces:
0.112960544981105+9.30181927236372E-002i
The source cells, E9 and F9, contain the complex number in polar form;
with E9 containing the magnitude, and F9 the angle in degrees.

..." =FormatComplex(Q9,"0.000","0.000")" in

<snip>

 

[Frank's]

If you get the NAME error, then you have put the code in the wrong place.
Not because you are using Excel 97. It should go in a general module, not
in a sheet module.

When I did that, it worked fine for me in Excel 97 - either refering to a
cell with the your formula or directly as

=FormatComplex(COMPLEX(D8*COS(E8*PI()/180),D8*SIN(E8*PI()/180)),"0.00","0.0000")

Thanks for the info Tom. To be honest I am pretty much brain dead
when it comes to Excel. When you need to analyze a lot of data
it is a powerful tool though.

Regards,

Frank

 

[Frank's]

A little off topic, but an alternative to your main equation might be
something like this:


=IMEXP(COMPLEX(LN(E9),RADIANS(F9)))

Thanks Dana, a lot more compact than what I was using.

Frank

 

[Dana DeLouis]

f> =COMPLEX((E9*COS(F9*PI()/180)),(E9*SIN(F9*PI()/180))).

Hi. There is another way, but it's a few characters longer... :>)

=IMPRODUCT(E9,IMEXP(RADIANS(F9) & "i"))
vs
=IMEXP(COMPLEX(LN(E9),RADIANS(F9)))

 

[Frank]

f> =COMPLEX((E9*COS(F9*PI()/180)),(E9*SIN(F9*PI()/180))).

Hi. There is another way, but it's a few characters longer... :>)

=IMPRODUCT(E9,IMEXP(RADIANS(F9) & "i"))
vs
=IMEXP(COMPLEX(LN(E9),RADIANS(F9)))

Thanks again. It seems I know so little about Excel, that I am
going to have to buy a book on the subject!

Frank

 

[Dana DeLouis]

Here are four numbers starting at A1:
Just to throw this idea out.
For this example, an alternative to using the ATP could be something like
this.

=TEXT(A1*A3-A2*A4,"0.000") & TEXT(A1*A4+A2*A3,"+0.000i;-0.000i")

This returns your complex number...
-0.672+46.071i