Please note javascript is required for full website functionality.


A to Z of Excel Functions: the COMPLEX Function

15 September 2017

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the COMPLEX function.


The COMPLEX function

Imagine this complex function.  This function converts real and imaginary coefficients into a complex number of the form x + yi or x + yj, where i or j denotes the square root of -1.

The COMPLEX function employs the following syntax to operate:

COMPLEX(real_num, i_num, [suffix])

The COMPLEX function has the following arguments:

  • real_num: this is required and represents the real coefficient of the complex number
  • i_num: this is also required.  This denotes the imaginary coefficient of the complex number
  • suffix: this argument is optional.  The suffix for the imaginary component of the complex number.  If omitted, suffix is assumed to be "i".

NOTE: All complex number functions accept "i" and "j" for suffix, but neither "I" nor "J".  Using uppercase results in the #VALUE! error value, given the possible confusion with cell references etc.  All functions that accept two or more complex numbers require that all suffices match.

It should be further noted that:

  • if real_num is non-numeric, COMPLEX returns the #VALUE! error value
  • if i_num is non-numeric, COMPLEX returns the #VALUE! error value
  • if suffix is neither "i" nor "j", COMPLEX returns the #VALUE! error value
  • arithmetic operations involving imaginary numbers require specific functions such as IMSUB (subtract), IMSUM (add), IMPRODUCT (multiply) and IMDIV (divide).  If these are not used, the calculations will return the #VALUE! error value.

Please see my example below: 

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every other business day.

A full page of the function articles can be found here.