Complex Number Formatting in Excel

I received a couple of questions back-to-back recently related to my post on complex numbers in Excel, asking how to properly format these types of numbers, especially after using a complex number function to perform a calculation.

The Complex Number Formatting Problem

The issue is that if the real and imaginary portions of a complex number are calculated values, the number of decimals can’t be adjusted through the toolbar by using the increase or decrease decimals button in Excel. As a result, you are sometimes left with complex numbers containing real and imaginary components with up to 16 characters.

Just as an example, if we were to enter the following complex number into Excel:


we would use the formula:

=COMPLEX(PI(),PI())

which returns:

3.14159265358979+3.14159265358979i

Obviously, all of these displayed decimals are irritating and unnecessary.

However, if you try to use the buttons in the Number group of the Home tab to reduce the number of decimal places, you’d quickly see that they don’t do anything.

The COMPLEX String

Why is this?

It occurs because the COMPLEX function in Excel takes the numerical real and imaginary components, combines them together with an operator (+ or -) and the suffix for the imaginary component (i or j), and outputs a text string. Other complex number functions decompose this string into numbers to perform their calculations.

With this knowledge, it’s not surprising then that we can’t adjust the number of decimals resulting from the complex function.

What we can do, though, is control the number of decimals on the inputs to the complex function by using ROUND.

Controlling the Inputs

So, if we want to limit the real and imaginary component decimals to 3, we could use this formula:

=COMPLEX(ROUND(real_num,3),ROUND(i_num,3))

For the example above, the result would be:

3.142+3.142i

One downside to this is that the accuracy of subsequent calculations will be limited by this rounding, because we’ve told Excel to basically forget about the trailing decimals. So, don’t reduce the number of displayed digits to be less than the level of resulting accuracy you are comfortable with.

It’s also a few more steps than one would really like to see just to control formatting on a complex number. But it does enable us to work with complex numbers in Excel while simultaneously keeping the outputs readable.

Scroll to Top
Complete... 50%
Please enter your name and email address below to receive a link to the ebook.

You’ll also receive regular tips to help you master Excel for engineering.

FREE EBOOK:

10 SMARTER WAYS TO USE EXCEL FOR ENGINEERING

By Charlie Young, P.E.

Take your engineering to the next level with advanced Excel skills.