Ask a new question

Excel formula for logarithmic and polynomial trendline

Hi all,

Is there an equation that returns the variables of a logarithmic trend line for a particular data set? For example, in the attached picture, you can see that the logarithmic trend line's equation is y=0.0791ln(x) + 0.7135. I'm after a formula that can output the "0.0791" and "0.7135" from this equation. Essentially, I want something like the "Linest" formula but for logarithmic equations. Does anyone how to structure this equation?

In addition, does anyone know how to structure an equation for third order polynomials? In attached pic 2, the key outputs I'm hoping to get are the -0.0008, +0.0145, -0.0586,+0.8233.

Many thanks for any help!

Logarithmic Trendline
Equation: y = (c * ln(x)) + b
c =INDEX(LINEST(y,LN(x)),1)
b =INDEX(LINEST(y,LN(x)),1,2)

3rd Order Polynomial Trendline
Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b
c3 =INDEX(LINEST(y,x^{1,2,3}),1,1)
c2 =INDEX(LINEST(y,x^{1,2,3}),1,2)
c1 =INDEX(LINEST(y,x^{1,2,3}),1,3)
b =INDEX(LINEST(y,x^{1,2,3}),1,4)

Where x and y are the cell references with the values.

Andreas.

20 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Excel 365

Since both logarithmic and polynomial curves

eventually go beyond 0% and 100%,

I don't recommend either of them.

A good fit is a DR-Hill regression

with an R^2 of 0.966

Any good math program will serve.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Andreas,

Thank you for your advice! I tried the logarithmic formula and it worked :) But for the polynomial, I'm not sure how to structure the formula, the formula I use gets me a value error.

Does this formula for c3 look correct? =INDEX(LINEST(A3:J3, (A2:J2)^{1,2,3}),1,1)

Assuming Y is at A3:J3, and X is A2:J2?

Please see attached screenshot of the error I get. Would appreciate any help, thank you!

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

The issue is that {1,2,3} is a vertical array, but A2:J2 and A3:J3 is horizontal.

=INDEX(LINEST(A3:J3, (A2:J2)^TRANSPOSE({1,2,3})),1,1)

Andreas.

6 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Does this formula for c3 look correct? =INDEX(LINEST(A3:J3, (A2:J2)^{1,2,3}),1,1)

You have an excellent solution with =INDEX(LINEST(A3:J3, (A2:J2)^TRANSPOSE({1,2,3})),1,1),

Here's another technique:

Image

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated October 7, 2024 Views 16,070 Applies to: