Complex Number Formatting

  • Thread starter Thread starter Frank
  • Start date Start date
F

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
 
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))
 
Stephen Bye said:
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))
Click to expand...

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 said:
But the problem reappears if you do IMPRODUCT on the rounded up
vectors.
Click to expand...

So you just round them again, exactly the same as you would do with ordinary
numbers...
 
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!
 
Herbert Seidenberg said:
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!
Click to expand...

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))
 
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.
 
Herbert Seidenberg said:
This might not be satisfactory if the user wants to work with
an accuracy of say 5 places, but display only 3 places.
Click to expand...

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.
 
Herbert Seidenberg said:
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.
Click to expand...

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
 
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")
 
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))).
Click to expand...

=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
Click to expand...
<snip>
 
Tom Ogilvy said:
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")
Click to expand...

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
 
Dana said:
A little off topic, but an alternative to your main equation might be
something like this:


=IMEXP(COMPLEX(LN(E9),RADIANS(F9)))
Click to expand...

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

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)))
 
Dana DeLouis said:
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)))
Click to expand...

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

Frank
 
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
 
Back
Top