Forecast - polynomial trends

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have data that i created a scatter chart with and added a polynomial trend line. I need to now read off of the chart coresponding y values for all values of x. However with the forecast function it uses a linear trend instead of a polynomial to find corresponding values. Is there anyway that i can set the function Forecast to use a polynomial trend or is there a specific function that uses polynomial trends

Thank you for your hel

chris
 
I have data that i created a scatter chart with and added a polynomial trend line. I need to now read off of the chart coresponding y values for all values of x. However with the forecast function it uses a linear trend instead of a polynomial to find corresponding values. Is there anyway that i can set the function Forecast to use a polynomial trend or is there a specific function that uses polynomial trends.

Thank you for your help

chris
Click to expand...

Depending on exactly how you want to do things, there are a few possibilities.

You could use the formula shown when you check the option to display formula on
the graph. However, you must then format that result (right click on the box
in which the formula appears) to a large number of decimals.

You might be able to use the TREND worksheet function: You can use TREND for
polynomial curve fitting by regressing against the same variable raised to
different powers. For example, suppose column A contains y-values and column B
contains x-values. You can enter x^2 in column C, x^3 in column D, and so on,
and then regress columns B through D against column A.

but in earlier versions of Excel than 2003, the results are occasionally less
predictable than using the formula from the graph.


--ron
 
See example of LINEST at www.stfx.ca/people/bliengme/ExcelTips
Bernard

chrisxyz said:
I have data that i created a scatter chart with and added a polynomial
Click to expand...
trend line. I need to now read off of the chart coresponding y values for
all values of x. However with the forecast function it uses a linear trend
instead of a polynomial to find corresponding values. Is there anyway that i
can set the function Forecast to use a polynomial trend or is there a
specific function that uses polynomial trends.
 
Thank you very much Ron for your help the equation was exactly what i was looking for.

Thanks again

chris

Barbados

----- Ron Rosenfeld wrote: -----

I have data that i created a scatter chart with and added a polynomial trend line. I need to now read off of the chart coresponding y values for all values of x. However with the forecast function it uses a linear trend instead of a polynomial to find corresponding values. Is there anyway that i can set the function Forecast to use a polynomial trend or is there a specific function that uses polynomial trends.
Click to expand...

Depending on exactly how you want to do things, there are a few possibilities.

You could use the formula shown when you check the option to display formula on
the graph. However, you must then format that result (right click on the box
in which the formula appears) to a large number of decimals.

You might be able to use the TREND worksheet function: You can use TREND for
polynomial curve fitting by regressing against the same variable raised to
different powers. For example, suppose column A contains y-values and column B
contains x-values. You can enter x^2 in column C, x^3 in column D, and so on,
and then regress columns B through D against column A.

but in earlier versions of Excel than 2003, the results are occasionally less
predictable than using the formula from the graph.


--ron
 
Thank you very much Ron for your help the equation was exactly what i was looking for.

Thanks again

chris

Barbados
Click to expand...

I'm glad that worked for you, Chris.


If you do a Google search, you may be able to locate a VB routine written by
one of the contributors here, that can automatically extract that formula.


--ron
 
Ron said:
You might be able to use the TREND worksheet function: You can use TREND for
polynomial curve fitting by regressing against the same variable raised to
different powers. For example, suppose column A contains y-values and column B
contains x-values. You can enter x^2 in column C, x^3 in column D, and so on,
and then regress columns B through D against column A.
Click to expand...


Or just use
=TREND(A1:A30,B1:B30^{1,2,3})
where the powers of x are computed on the fly.
You could use the formula shown when you check the option to display formula on
the graph. However, you must then format that result (right click on the box
in which the formula appears) to a large number of decimals.
Click to expand...

Or use David Braden's VBA code to extract the coefficients directly from
the chart into cells

http://groups.google.com/[email protected]

Jerry
 
Or use David Braden's VBA code to extract the coefficients directly from
the chart into cells

http://groups.google.com/[email protected]

Jerry
Click to expand...

That's the reference I was hoping someone would be kind enough to remember and
post!

And, as you are probably aware but I repeat here for those who are not, the
results of the chart formula, in versions prior to XL2003, is likely more
robust.


--ron
 
Back
Top