Taking the natural log of a negative number is Excel. 3

[pmans]

Is there a way to calculate the natural log of a negative number (It should yield two solutions) in Excel without getting an error message. Your help is greatly appreciated.


pmans

 

[btrueblood]

I believe ln(x) where x is negative gives complex results; you might try looking up the excel functions that deal with complex numbers.

 

[jghrist]

Install the Analysis Toolpak with the Tools|Add-Ins command. Then use function IMLN(number).

 

[pmans]

jghrist,

The IMLN(number) function works, but yields a "text" cell. Do you know how to change this into a "number" cell, so it can be used as part of an equation somewhere else in the spreadsheet.

Thanks in advance,

pmans

 

[electricpete]

imln returns a complex number. That complex number can be manipulated with other im- functions (improduct, imsum, imreal etc).


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

... but if you try to use normal floating point arithmetic functions (+, -,/,*,cos etc) on a complex number, it won't work.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[BML]

The text cell represents both parts of the imaginary number. You'll have to parse out the real and imaginary parts separately into two different cells, using something like the MID and FIND functions to figure out where each part starts and stops.

 

[jghrist]

IMREAL(complex number) will give the number value of the real part and IMAGINARY(complex number) will give the imaginary part.

Doing complex math with the analysis toolpack is a pain in the a**. If you do a lot, get something like MathCAD.

 

[pmans]

Thanks electricpete, BML, and jghrist for all your help. I got what I wanted, by using the IMREAL and the IMAGINARY functions.

Here is a follow-up question, related to the imaginary part of the solution. I've noticed that the imaginary part of the natural log of any negative number is 3.14..i or PIi. Do you guys know why that is? What's the significance of PI in the imaginary part of the solution?

Thanks in advance,

pmans

 

[Denial]

It comes from what I recall is called Euler's Theorem
e^(i*Pi) = -1
from which it follows that
log(-1) = i*Pi

Then from basic (non-complex) maths we have
log[(-1)*(x)] = log(-1) + log(x)

QED

 

[electricpete]

Denial nailed it.

bml and jgrhist - it sounds like you are saying the only thing we can do with a complex result is parse it into real/imaginary and go from there - if so I disagree. excel has complex functions which work directly on complex numbers as posted from help below.

I apologize in advance if I have misunderstood your comments.

IMABS Returns the absolute value (modulus) of a complex number
IMAGINARY Returns the imaginary coefficient of a complex number
IMARGUMENT Returns the argument theta, an angle expressed in radians
IMCONJUGATE Returns the complex conjugate of a complex number
IMCOS Returns the cosine of a complex number
IMDIV Returns the quotient of two complex numbers
IMEXP Returns the exponential of a complex number
IMLN Returns the natural logarithm of a complex number
IMLOG10 Returns the base-10 logarithm of a complex number
IMLOG2 Returns the base-2 logarithm of a complex number
IMPOWER Returns a complex number raised to an integer power
IMPRODUCT Returns the product of two complex numbers
IMREAL Returns the real coefficient of a complex number
IMSIN Returns the sine of a complex number
IMSQRT Returns the square root of a complex number
IMSUB Returns the difference of two complex numbers
IMSUM Returns the sum of complex numbers

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Just to put my previous comment in context, it is not so much the responses that I disagree with, but the original poster's 1/11 post implied the result of imln was just a text field which could not be used in calculations. I thought in response it was important to emphasize that it is not a text field but a complex datatype complete with it's own functions which can be used in calcualtions. Sorry I didn't mean to criticize anyone.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[cummings54]

The functions IMREAL and IMAGINARY also in the analysis toolpack will return teh real and imaginary coefficients of a complex number. For example

IMREAL(IMLN(-15))=2.70805020110221
IMAGINARY(IMLN(-15))=3.14159265358979