How to fit a "ln" function

[Scott P]

I am in the process of fitting various data sets to functions. Some of these
data sets fit nicely using the LOGEST function in Excel, but several of the
data sets include negative and "zero" values -- LOGEST does not work with
such inputs. In charting the data sets with negative and zero values, I can
add a logarithmic trendline and get a function that includes the natural
logarithm (ln) that appears in the chart itself. Is there a function
accessible through Excel/VBA that will fit such a curve (similar to LOGEST)?

 

[Jerry W. Lewis]

ln(0) is -infinity and ln(-x) is a complex number (involving
i=sqrt(-1)). What do you propose that Excel should do with these?

Comparing LOGEST to the logarithmic trendline is comparing apples to
oranges. LOGEST takes logs of the yData while the logarithmic trendline
takes logs of the xData.

If you want the equation of the logarithmic trendline, you can request
it from the Options tab of the Add Trendline dialog; alternately you can
use LINEST as in
=LINEST(yData,LN(xData))
array entered (Ctrl-Shft-Enter).

Jerry

 

[Scott P]

Thanks, Jerry. I actually just figured out the use of LINEST as mentioned
below using LN(xdata). Just to clarify, in terms of your points below:

1. All of my x-values are >= 0. I can deal with the case of ln(0)
individually and use the curve fit equation generated by
=LINEST(yData,LN(xData)) for the remainder of my x-values. Ln(-x) does not
apply (it is just my y values that contain negative numbers).

2. I was not comparing LOGEST to a logarithmic trendline. I was asking if
a function in Excel/VBA fit a curve in a logarithmic fashion and worked in a
way (e.g. takes inputs and provides parameters of the fitted curve) similar
to that of LOGEST.

Thanks.