Forum Discussion
Complex numbers in Excel
Did you know that Excel can handle complex mathematical operations with complex numbers? Complex numbers, expressed as "a + bi," where 'a' and 'b' are real numbers and 'i' is the imaginary unit, can be powerful tools in various scenarios. Think engineering, physics, signal processing, or even financial modelling.
Excel offers native support for complex numbers, and you can perform basic operations like addition, subtraction, multiplication, and division just as easily as with real numbers. If you haven't explored this yet, give it a shot! It's as simple as entering a formula like =IMSUM(A1:B1) or =IMPRODUCT(A1:B1)
One nifty feature is the ability to convert complex numbers from rectangular to polar form and vice versa. This can be a game-changer in certain calculations, especially when dealing with phase angles or magnitude-based analyses. For this you can make use of the =IMABS(A1) and =IMARGUMENT() functions.
As complex numbers are considered to be text within Excel you will need special functions to perform arithmetic with them, such as IMSUM, IMSUB, IMPRODUCT, and IMDIV.
Excel can help you plot complex numbers on the complex plane using the XY Scatter chart! you will need the IMREAL and IMAGINARY functions to extract the real and imaginary coefficients from the complex number to use them to plot the numbers. This feature can be invaluable when dealing with complex data sets or analysing the behaviour of complex functions.
If you're interested in learning more about complex numbers, I made a video covering all functions that work with complex numbers in Excel: https://www.youtube.com/watch?v=_A2DIUibkmk
Have you worked with complex numbers before in Excel? What specific use case did you have? Were there any obstacles you had to overcome?
3 Replies
I see it is a while since this was first posted.
I have used complex numbers but, in the main prefer to implement it using pairs of floating point numbers rather than relying upon Microsoft's text-based implementation. Many years ago I implemented spectral analysis calculations based upon the Bretschneider spectrum
representing sea state in the North Atlantic. My reason for using tables of real numbers was mainly to control the number formatting of both the real and imaginary parts to show 4 decimal places rather than varying length strings. A creative application that does not really have anything to do with complex numbers is to split a range held as text using
Back to more serious matters, LAMBDA and recursion turn the Excel formula into a Turing complete programming language. Rather than plunging into the theory to back up that assertion, I chose to demonstrate it by programming a truly horrendous problem based upon the complex roots of unity, namely to implement a fast Fourier transformation (FFT). This time I used arrays of complex numbers held as real number pairs, not because of appearance, but rather to achieve the necessary performance.
I am an electrical engineer. So we use complex numbers all the time for RLC circuits etc. Not everyone has a Matlab license so I thought I would show my calculations in Excel. Having to use the functions to manipulate the numbers (add/multiply etc.) was a little clunky, but ok. It would also be nice if they could be formatted (to limit the number of decimal places displayed). Still, as it stands, it is useful, and a good start.
- Excellent video. Very informative.
Is it possible that a mathematical expression contains more than one imaginary number? Because in your example all complex numbers have only one imaginary number. If so then how would we break real and imaginary parts?
Thanks
