COMPLEX

What it does

COMPLEX(real_num, i_num, [suffix])

COMPLEX(real_num, i_num, [suffix]) constructs Excel’s text representation of a complex number from its two numeric coefficients. The optional imaginary suffix defaults to "i"; "j" is also supported.

Try it

=COMPLEX(2,-3) returns "2-3i". =COMPLEX(0,1,"j") returns "j", since the real zero and imaginary coefficient one are suppressed.

Quirks

Only lowercase i and j are documented suffixes; another suffix gives #VALUE!. A nonnumeric coefficient also gives #VALUE!. The result is text, not a new numeric cell type. The current implementation also admits an empty suffix as the default i; that is an implementation detail beyond the documented two spellings.

How to implement

Coerce the two coefficients, validate the suffix and normalise zeros. Format each nonzero coefficient under the complex decimal-format rules. If both are zero, produce text zero. Join the real part and signed imaginary part without spaces, omitting the imaginary coefficient when its magnitude is one. For 2,-3, emit the real two, then the minus sign, then 3i. Do not use an arbitrary language debug representation: exponent spelling, precision and sign suppression are reader-visible.

Rust code

30 complex "COMPLEX" (2, 3) [V "real_num", V "i_num", V "suffix"] |s, a, sv| {
    let re = coefficient(s, a, 0)?;
    let im = coefficient(s, a, 1)?;
    let suffix = match a.get(2).map(CalcValue::core_ref) {
        None | Some(CoreRef::Missing | CoreRef::Empty) => 'i',
        Some(CoreRef::Error(code)) => return fail(code),
        Some(CoreRef::Text(_)) => match text(s, a, 2)?.trim() {
            "" | "i" => 'i',
            "j" => 'j',
            _ => return fail(WorksheetErrorCode::Value),
        },
        Some(_) => return fail(WorksheetErrorCode::Value),
    };
    let text = format_complex_text(ParsedComplex::new(re, im, None), suffix).map_err(Exit::Error)?;
    Ok(text_value(&text))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

IMREAL and IMAGINARY recover the coefficients; IMSUM and IMPRODUCT perform complex arithmetic.

Named variants

No named variants are recorded yet.