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.

Are you struggling to the find the right solutions to your engineering problems in Excel?

In Engineering with Excel, you’ll learn Excel for advanced engineering calculations through a step-by-step system that helps engineers solve difficult problems quickly and accurately.

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.

Complete... 50%

SIGN UP BELOW TO GET STARTED!

You’re almost there! Please enter your email address below to get started with your free training:

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.

EXCEL TRAINING FOR ENGINEERS

Learn advanced engineering techniques in Excel with this limited time free video training.

Click the button below to get started today.