The set of complex numbers consists of all numbers of the form a + bi where a and b are real numbers and i = 
In Excel, complex numbers are represented as text of the form “a + bi”. Excel provides a variety of worksheet functions to deal with complex numbers. Most of these begin with the letters “IM”. Some examples are shown in Figure 1. Note that values that are left justified are text, while values that are right justified are real numbers.
Figure 1 – Complex number operations in Excel
Excel also provides functions for log (IMLN, IMLOG10, IMLOG2), exponential (IMEXP), various trigonometric functions (IMSIN, IMCOS) square root (IMSQRT) and angle in radians (IMARGUMENT). Additional trigonometric functions are available in Excel 2011, 2013 and 2016.
Excel does not support complex numbers as numeric values, but we can use a 1 × 2 range to represent the complex number a + bi, where the first cell contains the value for a and the second cell contains the value for b.
We now show how to perform the usual operations on complex numbers and define Real Statistics functions which perform the same operations in Excel.
Addition and subtraction are performed using the usual rules from algebra, as is multiplication where we need to use the fact that i2 = -1.
| Addition | (a + bi) + (c + di) = (a+c) + (b+d)i |
| Subtraction | (a + bi) – (c + di) = (a–c) + (b–d)i |
| Multiplication | (a + bi) · (c + di) = (ac–bd) + (ad+bc)i |
Note using the fact that a real number c can be expressed as c + 0i, we see that the multiplication of a complex number by a real number can be expressed as
c · (a + bi) = ac + (bc)i
Note too that
i · (a + bi) = –b + ai
The reciprocal of a complex number is equal to its conjugate divided by the square of its absolute value, as shown by the following
Thus, division of c + di by a + bi can be accomplished by first expressing the reciprocal of c + di as described above and then multiplying by a + bi.
We can also express raising a complex number z to a positive integer power recursively by performing repeated multiplications: z1 = z and zn+1 = z · zn. If n is not an integer, then things get a bit more complicated, but we won’t get into that here.
Real Statistics Function: The Real Statistics Resource Pack supplies the following array functions, where z1, z2 are 1 × 2 ranges which represent complex numbers and a and b are real numbers. We also suppose that z1 represents the complex number c + di.
| CReal(z1) = c | CAdd(z1, z2) = z1 + z2 | CExp(z1) = exp(z1) = ez1 |
| CImag(z1) = d | CSub(z1, z2) = z1 – z2 | CLn(z1) = ln(z1) |
| CAbs(z1) = |z1| | CMult(z1, z2) = z1 * z2 | CSet(a,b) = a + bi |
| CConj(z1) = c – di | CDiv(z1, z2) = z1 / z2 | CMap(“a+bi”) = a + bi |
| CPower(z1, n) = z1n | CText(z1) = “c+di” |
Here c – di and a + bi are the 1 × 2 range representations of the corresponding complex number. Note that CReal, CImag, CAbs, CConj and CText are ordinary functions, while the others are array functions.
A constant complex number can be represented in the form {a, b}. Thus the complex number 3–4i can be represented by {3,-4}. The complex number i can be represented by {0,1} and the complex number 5.2+0i can be represented by {5.2,0} or simply by 5.2.
In Figure 2, we show the results of various complex number operations.
Figure 2 – Complex number operations
You can convert a complex number in Real Statistics format to one in Excel format via the formula =CText(z1), which is equivalent to the formula
=COMPLEX(CReal(z1),CImag(z1))
You can convert from Excel format to Real Statistics format using the array formula =CMap(z1).
Note that for the complex operations not supported by Real Statistics, you can use the Excel functions (when available). E.g. to get the sine of z1 you can use the formula
=CMap(IMSIN(CText(z1))
