logarithmic trendlines

[Wilson Farrell]

Hi,
Is it possible to have Excel create logarithmic trendlines using log
base 10 instead of ln. Its a little confusing when the you can only
logarithmically scale axes using log base 10.

Thanks,
wilson

--

~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~
\>>/ Wilson Farrell
oo: wfarrell_at_bbn_dot_com
/>>\
~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~

[Bernard Liengme]

Hi Wilson.
First, scaling axis and computing trendlines are independent concepts.
The equation for a log trendline is y=cln(x)+b. The function LOGEST uses
y=bm^x. Of course, these are very much interconnected.
Please tell us what equation you wish to fit your data to.
Best wishes
Bernard

[Thomas Bartkus]

It's tough for an inarticulate fool (me!) to explain but I'll try.

You have an Excel chart create a logarithmic trendline, then you ask it to
display the equation on the chart. Excel *expresses* this in terms of the
natural log of x.

y = a*Ln(x) + b

You wish the formula shown were expressed in terms of Base 10 Log(x) but
its not.

Nevertheless

The trendline is the trendline, whether you express it in terms of the
natural log or a base 10 log - the line (and its equation!) is identical.
If you multiply the (a) constant in that formula by the constant 2.3026 -
you convert the expression to Base 10 terminology.

y = (a * 2.3026)*Log(x) + b
is the same as
y = a * Ln(x) + b

Your b constant is the same, you just multiply the a constant by 2.3026.
That number (2.3026) is (1/Log10(e)) just in case you were dying to ask :-)

You can't change the fact that the text formula in the label is expressed in
terms of Ln (Base e) rather than Log (Base 10) that you would like to see.
However, you know how to convert it. You could also calculate your own a
and b constants for Base 10 trendline formula directly. Just add a column
of values for Log(x) and use the Slope (a) and Intercept (b) formulas:

a = Slope(yRange, xRange)
b = Intercept(yRange, xRange)

Just use your Log(x) values for the xRange. If you do that you can show
your own (Base10) equation on a textbox in the chart.

Whewww!
Hope that was a help rather than a hindrance.
Thomas Bartkus

"Wilson Farrell" <wfarre...@nospam.bbn.com> wrote in message
news:3CA0E493...@nospam.bbn.com...

[Wilson Farrell]

Thanks for your very thorough response. I was aware of the simplicity
of converting ln to log base 10. I was hoping that, since it was so
simple, I could get Excel to do it for me. Alas, no.

Thanks again,

[Thomas Bartkus]

Can you do a little VBA?

The DataLabel that displays the trendline formula can be written to. You
know how to calculate your a & b equation coeficients. Just form your own
equation string and put it into the DataLabel.

chrt.Series(1).Trendline.DataLabel.Caption = "y = " & a & "Log(x) +
" & b

Put it in the worksheets "Calculate" event and there you go. Your Base 10
log expression will appear in spite of Excel.

VBA is Excels salvation
Thomas Bartkus

"Wilson Farrell" <wfarre...@nospam.bbn.com> wrote in message

news:3CA1C7E3...@nospam.bbn.com...

[Tushar Mehta]

That's an interesting suggestion. Is the trendline datalabel caption
read-write?

Irrespective of whether it is or not, why not just add a textbox to the
chart and update its content. That way, even if XL refreshes the
trendline datalabel, the textbox contains the information you want
shown.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Microsoft MVP -- Excel
--
In <gOGo8.6170$VJ1.5...@bin3.nnrp.aus1.giganews.com>, Thomas Bartkus
<thomas...@comcast.net> wrote

[Thomas Bartkus]

"Tushar Mehta" <ng_p...@bigfoot.com> wrote in message
news:MPG.170d85e6b...@msnews.microsoft.com...

> That's an interesting suggestion. Is the trendline datalabel caption
> read-write?

Yes it is R/W. It is just a textbox meaning you can format individual
characters - real exponents superscripted and all that. It has the
advantage of being "attached" to the trendline. In other words it will
reposition itself in a *somewhat* appropriate manner as the data changes and
your trendline moves.

Beyond this problem at hand, it is nice to express the trendline equation in
more user friendly terms "Volts" or "mg/L" rather than just x and y.

All these fancy tricks!
I'm sure that anyone who uses Excel serious graph work builds his own
trendlines anyway. Excel never gets anything *quite* right except for this
wonderful VBA programming facility.
AND I am becoming increasingly concerned (pessimistic?) for the future of
VBA as of late.
Thomas Bartkus