How to set the displayed precision for a complex number in Excel 2010?

Question

MS Excel provides a set of functions to work with complex numbers. When displaying real numbers, one can set the decimal places precision from the Format Cells dialog. However, it does not work for complex numbers.

How can I set the displayed precision of a complex number? Is there a custom template I can use for that?

0.98078528040323+0.195090322016128i --> 0.980+0.195i

Solution 1

You could wrap your complex formula's in a UDF that does the formatting:

Function FormatComplex(r As Variant, fmt As String) As String
    With Application.WorksheetFunction
        FormatComplex = Format(.ImReal(r), fmt) & Format(.Imaginary(r), fmt) & "i"
    End With
End Function

Call it like

=FormatComplex(IMSQRT(-2),"+0.000")

result:

+0.000+1.414i

Solution 2

To achieve this by formula (in a different cell), you can use the following, where your unformatted complex number is in A1:

=COMPLEX(VALUE(TEXT(IMREAL(A1),"0.000")),VALUE(TEXT(IMAGINARY(A1),"0.000")))

Comments

[ysap]

Thanks. I was hoping to avoid formulas like this, to occupy another cell. It also messes up the layout of my spreadsheet (this is basically regarding hundreds of cells..)

[ysap]

Thanks. I guess this is as good as it can get, although it make the formulation of the cell ugly.