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:
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 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:
=IF(ISERROR(SQRT(B2)), SQRT(ABS(B2)) & “i”,SQRT(B2))
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.
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.