COMPLEX & IMAGINARY Number Formulas
Learn how to handle complex numbers in Excel with COMPLEX, IMREAL, and IMAGINARY. Audit electrical engineering AC impedance and signal phase cleanly.
Electrical engineers working on alternating current (AC) power systems and radiofrequency (RF) circuits frequently calculate electrical impedance ($Z$). Impedance consists of real resistance ($R$ in Ohms) and imaginary reactance ($X$ in Ohms caused by capacitors and inductors), written mathematically as $Z = R + jX$.
Because Excel does not have a native numeric data type for complex numbers, it stores all complex values as formatted text strings (like "24+10j"). Standard mathematical operators like + or * trigger a #VALUE! error. To perform calculations on complex numbers, Excel provides the specialized IM formula family.
Quick Comparison: Core Complex Number Functions
| Formula | Input | Output | Engineering Application |
|---|---|---|---|
COMPLEX(real, imag, [suffix]) |
Two numeric values | Text string ("24+10j") |
Building impedance numbers from resistance and reactance. |
IMREAL(inumber) |
Complex text string | Pure numeric real part | Extracting pure resistive heat loss from an AC circuit. |
IMAGINARY(inumber) |
Complex text string | Pure numeric imaginary part | Extracting inductive and capacitive reactance. |
IMABS(inumber) |
Complex text string | Magnitude ($sqrt{R^2 + X^2}$) | Calculating total scalar impedance across a transmission line. |
Deep Technical Mechanics
1. Suffix Options ("i" vs "j")
While mathematicians use $i$ for $sqrt{-1}$, electrical engineers use $j$ to avoid confusion with electric current ($i$). The COMPLEX formula accepts either "i" or "j" as its optional third argument:
=COMPLEX(24, 10, "j") // Returns "24+10j"2. Math Operations on Complex Strings
Never write ="24+10j" + "5-2j". To add, multiply, or divide complex numbers in Excel, use the dedicated functions:
=IMSUM(cell1, cell2)=IMPRODUCT(cell1, cell2)=IMDIV(cell1, cell2)
Hands-on Practice Playground
Use the electrical substation impedance board below to assemble complex numbers and extract real and imaginary components. Follow each objective to complete the exercise.
In cell D2, construct the complex impedance string using the COMPLEX formula with suffix "j".
In cell D2, construct the complex impedance string for Substation Branch A using the COMPLEX formula with real part B2, imaginary part C2, and suffix "j".
In cell E2, extract the real resistance component from the impedance string.
In cell E2, extract the real resistance component from the complex impedance string in D2 using the IMREAL formula.
In cell F2, extract the imaginary reactance component using IMAGINARY.
In cell F2, extract the imaginary reactance component from complex impedance D2 using the IMAGINARY formula.
Share This Lesson
Spread the word with your peers and challenge your friends