Introduction
The complex number system is an extension of real numbers.
Historically, the invention of complex numbers allowed mathematicians to find the roots of previously unsolvable polynomials.
Nowadays, engineers use complex numbers to solve problems related to electronics, signal processing, and fluid dynamics.
The way the formula engine implements complex numbers is an example of functional programming.
These new functions make it easier to use complex numbers in Excel without needing helper columns or array formulas.
What is a complex number?
For example, to add two complex numbers together, we pass them to theIMSUMfunction like this.
you might also construct a complex number using theCOMPLEXfunction, which avoids dealing with strings altogether.
To get the real part of the complex number, use theIMREALfunction.
Use theIMAGINARYfunction to get the imaginary part of the complex number.
Conceptually, the complex number system is a two-dimensional number system that describes rotations.
We perform this same multiplication in Excel using a function call.
As shown in this basic example, complex numbers describe rotations.
Because of this, a complex number is often characterized by its radius and angle.
The angle of a complex number is measured inradiansfrom the positive real axis, where counterclockwise is positive.
The angle is also referred to as the phase of the complex number.
To get the angle of the complex number, use theIMARGUMENTfunction.
To get the magnitude or length of the complex number, use theIMABSfunction.
Complex Operations
To add two complex numbers together, use theIMSUMfunction.
This arrow is equal to the sum of the complex numbers.
Use theIMPRODUCTfunction to multiply complex numbers together.
TheIMPRODUCTpage discusses this in more detail.
Each function corresponds to an operation that you perform with complex numbers.
Complex Functions in Excel
Below is a table of the functions we’ve discussed so far.
The full list of 20+ complex functions is documented in the Engineering section of our functions referencehere.
In practice, using complex numbers in Excel involves translating math into a series of complex function calls.
Nothing is a better example of this thanIMEXPor the complex exponential function.
For example, the formula for a discrete Fourier transform looks like this.
We’ll discuss the specifics of this example later.
Complex numbers typically appear with the exponential function.
This formula is calledEuler’s Formula.
Visually, this corresponds to rotating the number counterclockwise around the origin by the angle.
Much more can be said about theIMEXPfunction’s useful properties and how it relates to trigonometry.
See also the related functions, such asIMLN,IMSIN,IMCOS, and more.
Signal Processing Example
Complex numbers appear in contexts involving rotations, oscillations, and wave-like phenomena.
This means using a function likeIMSUMinstead of the plus (+) operator to describe addition.
Recently, Excel’s formula engine has been changing to make these calculations easier without helper columns.
New functions likeLET,LAMBDA,MAP, andSEQUENCEallow for a more functional programming approach when calculating results.
To be clear, using complex numbers in Excel is already an example of functional programming.
These new functions just make the intermediate calculations easier to perform.
A good example is the formula for a Discrete Fourier Transform.
This formula is the same version as the one you’ll find on the Discrete Fourier TransformWikipedia page.
This formula has a lot going on, so let’s break it down.
In Excel, we calculate this intermediate value using theIMPRODUCTfunction and theIMEXPfunction.
This is where the new functions come in handy.
Note thatmhas been substituted forNbecause Excel does not distinguish the lowercase n from the capital N in the formula.
Here is the full formula, which “spills” the results in the cell’s column.
This is the result when you enter the formula in Excel.
This number is really close to the value of -2i but not quite exactly equal to -2i.
When testing this formula on sample arrays of less than 500 samples, it performs well.
This completes almost immediately for the same-sized sample array.
It’s a good example of how the new functions can be used to describe something quite complicated.
This article was written by Kurt Bruns.
Have you solved an interesting problem with complex numbers in Excel?Let us know.
