Complex Number Functions

Excel has quite a few basic functions for working with complex or imaginary numbers. To expand the capability of Excel to include algorithms for complex matrices, polynomial root finding, and other applications, we need to define some new LAMBDA functions. This page summarizes the existing built-in functions and provides the code for additional complex number building blocks.

Examples and Custom Functions

The examples of the complex number functions described here are not included in the main LAMBDA Library template. Instead, you may download the following template which contains both the functions and the examples. This is essentially the test file for these complex number functions.

⤓ Get the Template (Filename: complex-numbers.xlsx)

How Excel Works with Complex Numbers

A single cell in Excel can only hold a single numeric value, so because a complex number has both a real and imaginary part, Excel stores a complex number as text like "23+2.34568i" or "-0.0000123456i".

Because the complex numbers are text, only dedicated imaginary number "IM" functions will work with these values, or other functions that don't care about the data type.

Precision: Excel's precision is limited to the 15th decimal place, and values are rounded rather than truncated. This can be seen in the following example. The complex number π+2/3i is represented as:

=COMPLEX(PI(),2/3)
Result: "3.14159265358979+0.666666666666667i"

Expect precision errors to propagate when using complex numbers in Excel. Consider this example where in theory the two results should be exactly 3 and -3:

=POWER(SQRT(3),2)
Result: 3

=IMPOWER(IMSQRT(-3),2)
Result: "-3.00000000000001+3.67544536472587E-16i"

i or j: The IM functions recognize either i or j for the imaginary part (j tends to be more common in electrical engineering because i is used to represent current). The i (or j) must be placed after the number (not "2+j5").

Avoid Spaces: While some of the IM functions can handle arbitrary numbers of spaces within the text such as "- 2 +4 j", some of them don't always handle extra spaces, such as IMCONJUGATE("1 + i"). So, it's best to just avoid using spaces when entering complex numbers as text. You can also use SUBSTITUTE(text," ","") to remove all spaces.

Existing Imaginary Number Functions in Excel

Basic Info, Creation, Properties

• COMPLEX(real,imag) :: Returns a complex number as text "-2+3i" from a real (-2) and imaginary (3) part.
• IMREAL(inumber) :: Returns the real part of a complex number. IMREAL("-2+3i") returns -2.
• IMAGINARY(inumber) :: Returns the imaginary part of a complex number. IMAGINARY("-2+3i") returns 3.
• IMCONJUGATE(inumber) :: Returns the complex conjugate which has the same real value and reverse sign for the imaginary part
• IMARGUMENT(inumber) :: Returns the angle theta in radians calculated from ATAN(y/x) where z="x+yi"
• IMABS(inumber) :: Returns |z| or SQRT(x^2+y^2) where z="x+yi"

The parameters used in most IM functions in Excel use the name "inumber" which refers to a complex number in text format such as "2+5i".

Complex Number Math Operations

• IMSUM(a, b, c, ...) :: Returns the sum of all complex numbers (can include arrays)
• IMSUB(a, b) :: Subtracts two complex numbers (a-b)
• IMPRODUCT(a, b, c, ...) :: Returns the product of all complex numbers (can include arrays)
• IMDIV(a, b) :: Returns the quotient of two complex numbers (a/b)
• IMPOWER(z, n) :: Returns z to the power of n or z^n. n can be a sequence or vector of powers.
• IMSQRT(z) :: Returns the square root of a complex number
• IMEXP(z) :: Returns the exponential of a complex number
• IMLN(z) :: Returns the natural logarithm of a complex number
• IMLOG10(z), IMLOG2(z) :: Returns the logarithm of a complex number in base 10 or 2
• IMSIN, IMCOS, IMTAN, IMSINH, ... :: Trigonometry functions for complex numbers

Custom LAMBDA Functions for Complex Numbers

• IM_CTRANSPOSE(matrix) :: Returns the Conjugate Transpose of a matrix
• IM_DOT(a,b) :: Returns the Complex Dot Product of vectors a and b
• IM_SUMPRODUCT(a,b) :: Returns the sum of the element-wise multiplication of vectors a and b
• IM_MMULT(A,B) :: Returns the matrix multiplication of two complex matrices
• IM_ISCOMPLEX(A) :: Returns TRUE if the matrix is complex, based on testing for at least one text value and no IMSUM(Aij,Aij) errors.
• IM_ROUNDTOZERO(inumber,[epsilon]) :: Returns a complex or real number after making any |coefficient| less than epsilon (default is ε=1E-14) equal to zero
• IM_ROUND(inumber,num_digits) :: Rounds the Real and Imaginary coefficients separately to num_digits
• IM_TOARRAY(inumber) :: Converts z=x+yi to an array {x,y}. inumber may be a column vector.

Note: The following functions also work with complex numbers: L_POLYVAL, L_POLYROOTS, L_POLYMULT, L_POLYFROMROOTS.

Built-in IM Functions for Matrices

Many of the built-in "IM" functions only work with single values. Using the MAP function can allow you to apply the functions to complex matrices. Here are a few examples.

/**
* Returns the Real coefficient for each element in a complex matrix
* Returns X from Z=X+Yi
*/
IM_REAL = LAMBDA(imatrix,
LET(doc,"https://www.vertex42.com/lambda/complex.html",
    MAP(imatrix,LAMBDA(inum,IMREAL(inum)))
));

/**
* Returns the Imaginary coefficient for each element in a complex matrix
* Returns Y from Z=X+Yi
*/
IM_IMAGINARY = LAMBDA(imatrix,
LET(doc,"https://www.vertex42.com/lambda/complex.html",
    MAP(imatrix,LAMBDA(inum,IMAGINARY(inum)))
));

/**
* Returns a Complex Matrix from matrices of real and imaginary coefficients X and Y
* Returns Z from Z=X+Yi
*/
IM_COMPLEX = LAMBDA(X_matrix,Y_matrix,
LET(doc,"https://www.vertex42.com/lambda/complex.html",
    MAP(X_matrix,Y_matrix,LAMBDA(x,y,COMPLEX(x,y)))
));

IM_CTRANSPOSE(matrix)

IM_CTRANSPOSE returns the Conjugate Transpose of a matrix, A^*. This is done by first finding the TRANSPOSE and then evaluating the conjugate (using IMCONJUGATE) of each individual element. IM_CTRANSPOSE should be equal to TRANSPOSE if all elements are Real.

/**
* Returns the Conjugate Transpose of a Complex Matrix
*/
IM_CTRANSPOSE = LAMBDA(matrix,
LET(doc,"https://www.vertex42.com/lambda/complex.html",
    TRANSPOSE(MAP(matrix,LAMBDA(cell,IMCONJUGATE(cell))))
));

Test: Copy and Paste this LET function into a cell
=LET(
    matrix, {1,"-2-i",5; "1+i","i","4-2i"},
    IM_CTRANSPOSE(matrix)
)
Result: {"1","1-i"; "-2+i","-i"; "5","4+2i"}

IM_DOT(a,b)

IM_DOT returns the Complex Dot Product of two vectors a and b^* where b^* is the complex conjugate of b. This is also equal to IM_MMULT(b^Ha) where b^H is the conjugate transpose of b. IM_DOT should be equal to SUMPRODUCT and IM_SUMPRODUCT if all elements are Real.

/**
* Returns the Complex Dot Product of two vectors
*/
IM_DOT = LAMBDA(a_vec,b_vec,
LET(doc,"https://www.vertex42.com/lambda/complex.html",
    IMSUM(MAP(a_vec,b_vec,LAMBDA(ai,bi,IMPRODUCT(ai,IMCONJUGATE(bi)))))
));

Test: Copy and Paste this LET function into a cell
=LET(
    a, {1;"-2-i";5},
    b, {"1+i";"i";"4-2i"},
    IM_DOT(a,b)
)
Result: "20+11i"

More information: Wikipedia: Dot Product

IM_SUMPRODUCT(a,b)

IM_SUMPRODUCT returns the sum (using IMSUM) of the elementwise product of a and b (using IMPRODUCT). This function is used within the IM_MMULT function for simplifying matrix multiplication. IM_SUMPRODUCT should be equal to SUMPRODUCT and IM_DOT if all elements are Real.

/**
* Returns the sum of the element-wise product of complex vectors a and b
*/
IM_SUMPRODUCT = LAMBDA(a_vec,b_vec,
LET(doc,"https://www.vertex42.com/lambda/complex.html",
    IMSUM(MAP(a_vec,b_vec,LAMBDA(ai,bi,IMPRODUCT(ai,bi))))
));

Test: Copy and Paste this LET function into a cell
=LET(
    a, {2;"-2-i";4},
    b, {"1+i";"-i";"1-2i"},
    IM_SUMPRODUCT(a,b)
)
Result: "5-4i"

IM_MMULT(A,B)

IM_MMULT returns the matrix multiplication of two complex matrices. This is essentially version of the MMULT function for complex numbers. IM_MMULT should be equal to MMULT if all elements are Real.

/**
* Returns the matrix multiplication of two complex matrices
*/
IM_MMULT = LAMBDA(a_mat,b_mat,
LET(doc,"https://www.vertex42.com/lambda/complex.html",
    m,ROWS(a_mat),
    n,COLUMNS(b_mat),
    IF(COLUMNS(a_mat)<>ROWS(b_mat),"Error: invalid sizes",
        MAKEARRAY(m,n,LAMBDA(i,j,
            IM_SUMPRODUCT(TRANSPOSE(INDEX(a_mat,i,0)),INDEX(b_mat,0,j))
        ))
    )
));

Test: Copy and Paste this LET function into a cell
=LET(
    a, {1;"-2-i";5},
    b, {"1+i";"i";"4-2i"},
    IM_MMULT(IM_TRANSPOSE(b),a)
)
Result: "20+11i"

IM_ISCOMPLEX(matrix)

IM_ISCOMPLEX returns TRUE or FALSE if the matrix is a complex matrix. Real numbers are mathematically a subset of complex numbers, but not for this function. The purpose of this function is to determine if a matrix must be treated as complex. If the matrix consists of all real values (or blanks which are treated as 0), this function returns FALSE. Note: If any of the real values are entered or stored as text (such as '24 or ="24"), then this function assumes it is a complex number because most non-complex functions cannot handle real values that are entered or stored as text.

The two requirements to be a complex matrix tested by this function are:

1. At least one value in the array must be text (uses the ISTEXT function).
2. Each value can be treated as a complex number (each values passes the IMSUM(a,a) test)

Some algorithms may be designed to work with real values or complex values or both. However, when an algorithm does not need to use complex functions, it may be better to use the algorithm designed for real values. The complex number functions may have a tendancy to suffer from precision errors or "wandering into imaginary space" when they don't need to.

The IM_ISCOMPLEX function allows you to test a matrix and decide whether to use one algorithm or another.

/**
* Checks if there is at least one values in an array that is a complex number
* and if all values can be treated as complex numbers
*/
IM_ISCOMPLEX = LAMBDA(matrix,
LET(doc,"https://www.vertex42.com/lambda/complex.html",
    istext,IF(SUM(--ISTEXT(matrix))>0,TRUE,FALSE),
    isinum,IF(SUM(--ISERROR(MAP(matrix,LAMBDA(cell,IMSUM(cell,cell)))))>0,FALSE,TRUE),
    AND(istext,isinum)
));

IM_ISHERMITIAN(matrix)

A Hermitian matrix is a matrix that is equal to its conjugate transpose: A=A^*. Evaluating the conjugate transpose converts all of the values to text even if some of the values are real (and the diagonal of a Hermitian matrix is real). So to handle the <> comparision in Excel, we first convert the original matrix to text using IM_CTRANSPOSE(IM_CTRANSPOSE(matrix)).

/**
* Returns TRUE if the matrix is equal to its conjugate transpose
*/
IM_ISHERMITIAN = LAMBDA(matrix,
LET(doc,"https://www.vertex42.com/lambda/complex.html",
    matrix,IM_CTRANSPOSE(IM_CTRANSPOSE(matrix)),
    IF(ROWS(matrix)<>COLUMNS(matrix),FALSE,SUM(--(matrix<>IM_CTRANSPOSE(matrix)))=0)
));

IM_POLYVAL(coeffs,x)

L_POLYVAL has been updated to work with complex numbers.

IM_ROUNDTOZERO(inumber,[epsilon])

The function makes any |coefficient| less than epsilon (default is ε=1E-14) equal to zero. If the imaginary coefficient for a complex number is extremely small, resulting in 0i, it will return just the real value.

=IM_ROUNDTOZERO("5-3.67544536472586E-16i")
Result: 5

/**
* If |coefficient| is less than epsilon (default=1E-14), make it equal zero
*/
IM_ROUNDTOZERO = LAMBDA(inumber,[epsilon],
LET(doc,"https://www.vertex42.com/lambda/complex.html",
    epsilon,IF(ISOMITTED(epsilon),1E-14,epsilon),
    MAP(inumber,LAMBDA(inum,
    LET(real,IF(ABS(IMREAL(inum))<epsilon,0,IMREAL(inum)),
        imag,IF(ABS(IMAGINARY(inum))<epsilon,0,IMAGINARY(inum)),
        IF(imag=0,real,COMPLEX(real,imag))
    )))
));

IM_ROUND(inumber,num_digits)

This function applies the ROUND function separately to the x and y coefficients of a complex number z=x+yi. The num_digits parameter can be a single value (affecting both x and y) or a 2x1 or 1x2 array to round x and y separately using a different number of digits for each. If num_digits is blank, the value is not rounded.

The inumber parameter can be a complex matrix.

=IM_ROUND("5.12345-3.67544536472586E-16i",{"",12})
Result: {5.12345}  (Imaginary part rounded to 0 and the real part was not rounded)

=IM_ROUND("0.12345-8.12345i",3)
Result: "0.123-8.123i"  (Both coefficients rounded to 3 decimal places)

=IM_ROUND("0.12345-8.12345i",{4,3})
Result: "0.1235-8.123i"  (Real rounded to 4 digits; Imaginary rounded to 3 digits)

/**
* Rounds the Real and Imaginary coefficients separately to num_digits
*/
IM_ROUND = LAMBDA(inumber,num_digits,
LET(doc,"https://www.vertex42.com/lambda/complex.html",
    num_digits,IF(ISOMITTED(num_digits),{"",""},num_digits),
    rdigits,INDEX(num_digits,1),
    idigits,IF(OR(ROWS(num_digits)>1,COLUMNS(num_digits)>1),INDEX(num_digits,2),rdigits),
    MAP(inumber,LAMBDA(inum,
    LET(real,IF(ISBLANK(rdigits),IMREAL(inum),ROUND(IMREAL(inum),rdigits)),
        imag,IF(ISBLANK(idigits),IMAGINARY(inum),ROUND(IMAGINARY(inum),idigits)),
        IF(imag=0,real,COMPLEX(real,imag))
    )))
));

IM_TOARRAY(inumber)

Converts a complex number z=x+yi to a row vector {x,y}. If inumber is an nx1 column vector, IM_TOARRAY converts inumber to an nx2 array {x,y}.

=IM_TOARRAY("5.12345-3.67544536472586E-16i")
Result: {5.12345,3.67544536472586E-16}

=IM_TOARRAY("0.12345-8.12345i")
Result: "0.12345-8.12345i"

/**
* Converts a Complex Number z=x+yi to an array of coefficients {x,y}
*/
IM_TOARRAY = LAMBDA(inumber,
LET(doc,"https://www.vertex42.com/lambda/complex.html",
    MAKEARRAY(ROWS(inumber),2,LAMBDA(i,j,
        IF(j=1,IMREAL(INDEX(inumber,i)),IMAGINARY(INDEX(inumber,i)))
    ))
));

More Complex Number Functions

We expect to have more functions for complex numbers in the future. Contact us if you have specific requests.