Using Excel to calculate imaginary numbers

In garden-variety algebra, there is no such thing as the square root of a negative number. That’s because a square root is a number multiplied by itself, and there is no number that you can square to create a negative number. If you square a negative number, the result is positive.

If you try to take the square root of a negative number in Excel, it throws the #NUM error:
square root of negative 9 throws the #NUM error

But sometimes, you really do need the square root of a negative number to solve an equation, although it’s usually short-lived and gets canceled out.

Calculus provides a solution: an imaginary number, represented as i. So the solution to Square root of negative 9 is 3i.

Excel doesn’t have a built-in way of handling imaginary numbers, so here is a way of calculating it using the ISERR function (determines if a calculation throws an error) nested inside the IF function. This technique also uses the ABS function to take the absolute value of a negative number. With a negative number in B2, the function is:

The formula says: if it’s true that the square root of B2 throws an error, then take the square root of the absolute value of B2 and append the letter “i”. If it’s false that there’s an error, then take the square root of B2.

formula produces result of 3i

There is a downside to this: when a cell contains a number and text, Excel treats the whole cell as text. That means you can’t do any calculations with the result.


On Key

Related Posts

Get in touch