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:
Elevate Your Engineering With Excel
Advance in Excel with engineering-focused training that equips you with the skills to streamline projects and accelerate your career.
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.
