In electrical engineering, acoustics, signal processing, and advanced physics, calculations frequently involve complex numbers composed of real and imaginary parts (expressed as x + yi or x + yj). While standard spreadsheet formulas handle real numbers effortlessly, standard operators like +, *, or / will fail and return #VALUE! errors when applied directly to strings containing imaginary notation. To solve this, Microsoft Excel includes a specialised suite of engineering functions designed specifically for complex number arithmetic.
In this guide, you will learn how to create complex numbers using the COMPLEX function and execute addition, multiplication, division, and conversions using IMSUM, IMPRODUCT, IMDIV, IMREAL, and IMAGINARY.
Understanding Complex Numbers in Excel
A complex number takes the standard mathematical form:
z = x + yi (or x + yj)Where x represents the real coefficient, y represents the imaginary coefficient, and i (or j in electrical engineering) represents the imaginary unit satisfying i² = -1.
In Microsoft Excel, complex numbers are stored and evaluated as text strings. Because they are formatted as text, you cannot perform arithmetic using standard operators (e.g., ="3+4i" + "1+2i" fails). Instead, Excel prefixes all complex operations with IM (short for “Imaginary”).
1. Creating Complex Numbers: The COMPLEX Function
The COMPLEX function converts real and imaginary coefficients into a standardised complex number text string.
Syntax
=COMPLEX(real_num, i_num, [suffix])Arguments
real_num(Required): The real coefficient of the complex number.i_num(Required): The imaginary coefficient of the complex number.suffix(Optional): The imaginary unit suffix symbol. Can be either lowercase"i"(default) or"j"(preferred in electrical engineering to avoid confusion with electric current).
Examples
=COMPLEX(3, 4)returns"3+4i"=COMPLEX(5, -2, "j")returns"5-2j"=COMPLEX(0, 7)returns"7i"
2. Extracting Coefficients: IMREAL and IMAGINARY
To deconstruct a complex number back into its individual numeric components for graphing or scalar calculations, use IMREAL and IMAGINARY:
# Extract the real part (returns numeric 3)
=IMREAL("3+4i")
# Extract the imaginary part (returns numeric 4)
=IMAGINARY("3+4i")3. Basic Arithmetic: IMSUM, IMPRODUCT, and IMDIV
Adding Complex Numbers: IMSUM
The IMSUM function calculates the sum of two or more complex numbers (adding real parts together and imaginary parts together):
# Add (3 + 4i) and (1 + 2i)
=IMSUM("3+4i", "1+2i")Result: "4+6i" (since 3 + 1 = 4 and 4i + 2i = 6i).
Subtracting Complex Numbers: IMSUB
To subtract one complex number from another:
# Subtract (2 + 5i) from (7 + 9i)
=IMSUB("7+9i", "2+5i")Result: "5+4i".
Multiplying Complex Numbers: IMPRODUCT
When multiplying complex numbers, the mathematical foil rule applies with i² = -1:
# Multiply (3 + 2i) by (1 + 4i)
=IMPRODUCT("3+2i", "1+4i")Result: "-5+14i" (since (3×1 - 2×4) + (3×4 + 2×1)i = -5 + 14i).
Dividing Complex Numbers: IMDIV
The IMDIV function divides a numerator complex number by a denominator complex number using conjugate multiplication:
# Divide (4 + 2i) by (2 - 1i)
=IMDIV("4+2i", "2-1i")Result: "1.2+1.6i".
4. Advanced Engineering Operations: IMABS, IMCONJUGATE, and IMARGUMENT
Absolute Value (Modulus / Magnitude): IMABS
In AC electrical circuits, impedance is expressed as a complex quantity Z = R + jX. To calculate total magnitude (absolute impedance):
=IMABS("3+4j")Result: 5 (computed via Pythagoras: √(3² + 4²) = √25 = 5).
Complex Conjugate: IMCONJUGATE
To reverse the sign of the imaginary component:
=IMCONJUGATE("5+7i")Result: "5-7i".
Phase Angle (Argument): IMARGUMENT
To determine the phase angle (theta, θ) in radians:
=IMARGUMENT("1+1i")Result: 0.785398 radians (or 45 degrees, which you can convert by wrapping in =DEGREES(IMARGUMENT("1+1i"))).
Summary of Key Complex Functions
| Function | Mathematical Operation | Typical Output |
|---|---|---|
COMPLEX(r, i, [s]) |
Builds complex string from real and imaginary parts. | "x+yi" or "x+yj" |
IMSUM(z1, z2, ...) |
Calculates the sum of complex numbers. | "a+bi" |
IMPRODUCT(z1, z2, ...) |
Calculates the product of complex numbers. | "a+bi" |
IMDIV(z1, z2) |
Calculates the quotient of two complex numbers. | "a+bi" |
IMABS(z) |
Calculates magnitude (modulus |z|). | Numeric scalar |
Troubleshooting Common Errors
#VALUE!Error: Occurs when a non-complex text string is supplied, or when suffixes are mixed (for example, attempting to add"3+4i"and"2+5j"will cause an error because suffix types must match).- Case Sensitivity: The suffix must be strictly lowercase (
"i"or"j"). Uppercase"I"or"J"will trigger an error. - Missing Signs: If entering numbers manually into cells, always include the operational sign (e.g.,
"4-3i"or"4+3i"). Omitting the sign (e.g.,"4 3i") renders the string unparseable.