How to Use the COMPLEX, IMREAL, and IMAGINARY Functions in Google Sheets for Engineering and Complex Numbers

Google Sheets provides built-in engineering formulas designed to manage complex numbers comprising real and imaginary components. When modelling electrical impedance, alternating current waveforms, or signal processing algorithms, standard arithmetic operators fail on alphanumeric strings such as 3+4i. By leveraging the COMPLEX, IMREAL, and IMAGINARY functions, you can programmatically construct valid complex number strings and extract their constituent coefficients for mathematical analysis.

Understanding Complex Number Representation in Google Sheets

In Google Sheets, complex numbers are stored as text strings formatted as x+yi or x+yj. Because these values are alphanumeric strings rather than native floating-point numbers, standard spreadsheet operators like +, -, *, or / return a #VALUE! error. Google Sheets resolves this through dedicated engineering functions prefixed with IM (imaginary), allowing you to construct, deconstruct, and calculate complex equations seamlessly.

Syntax and Parameters

1. COMPLEX Function

The COMPLEX function converts real and imaginary coefficients into a formatted complex number string.

=COMPLEX(real_num, i_num, [suffix])

• real_num (Required): The real coefficient of the complex number.
• i_num (Required): The imaginary coefficient of the complex number.
• suffix (Optional): The imaginary symbol. Accepts "i" (default) or "j" (frequently used in electrical engineering).

2. IMREAL Function

The IMREAL function extracts the real coefficient from a complex number string.

=IMREAL(inumber)

• inumber (Required): A valid complex number string or cell reference.

3. IMAGINARY Function

The IMAGINARY function extracts the imaginary coefficient from a complex number string.

=IMAGINARY(inumber)

• inumber (Required): A valid complex number string or cell reference.

Step-by-Step Practical Implementation

Step 1: Constructing Complex Numbers with COMPLEX

1. Open your spreadsheet and designate column A for real coefficients and column B for imaginary coefficients.
2. Enter 12.5 into cell A2 and -4.2 into cell B2.
3. In cell C2, enter the following formula:

   =COMPLEX(A2, B2, "i")

4. Press Enter. Google Sheets returns 12.5-4.2i formatted as an engineering string.

Step 2: Extracting Real Components with IMREAL

1. In cell D2, enter the following extraction formula:

   =IMREAL(C2)

2. Press Enter. The formula returns 12.5 as a standard numeric value ready for arithmetic operations.

Step 3: Extracting Imaginary Components with IMAGINARY

1. In cell E2, enter the following imaginary extraction formula:

   =IMAGINARY(C2)

2. Press Enter. The formula evaluates the string and returns -4.2 as a signed numeric coefficient.

Comparison of Engineering Functions for Complex Analysis

Function	Primary Purpose	Input Type	Output Type	Common Use Case
COMPLEX	Combines coefficients into complex text	Two numeric values	Text (e.g. “5+3i”)	Forming impedance variables from resistance and reactance
IMREAL	Extracts the real component	Complex text string	Numeric value	Isolating resistive components in circuit equations
IMAGINARY	Extracts the imaginary component	Complex text string	Numeric value	Calculating reactive power or phase angles
IMABS	Calculates absolute magnitude	Complex text string	Numeric value	Computing resultant vector magnitudes
IMCONJUGATE	Inverts the sign of the imaginary part	Complex text string	Text (e.g. “5-3i”)	Rationalising denominators in complex division

Troubleshooting Common Errors

• #VALUE! Error: Occurs when passing invalid text into IMREAL or IMAGINARY, or when specifying an invalid suffix other than "i" or "j".
• Direct Addition Failure: Attempting to add cells containing complex strings using + returns #VALUE!. Always use =IMSUM(cell1, cell2) instead.
• Locale Formatting: Ensure decimals in numeric parameters use points (.) or commas (,) consistently according to your Google Sheets spreadsheet locale settings.