I have an excel chart of a set of data points to which I have added a polynomial
curve fit. is there an excel built in feature allowing me to display a parallel trend line?
if not, would it make sense to seek out someone to custom program such a
feature [i.e.] I don’t have any idea how difficult such a task would be.
Could you post a copy of your data and chart and manually add the sort of trendline that you are looking for?
Martin
I already have the polynomial trendline [order 2] displayed on the chart. I simply want to display a line parallel to that line [ie] displaced by aOriginally Posted by mrice
constant amount from that line.
The first choice for a collection of parallel polynomials is usually to vary the y intercept. So, for a given 2nd degree polynomial (y=ax^2+bx+c), simply choose another value for c.
what i don't understand is the mechanics of how i get the parallel curve displayed on the chart after excel has
calculated and plotted the original curve fit line? for example, are there controls on the excel insert tab to do
it?
i tied to edit the last reply saying ......
if I specify a y intercept I still get only one line.
I hope to be able to display the two lines simultaneously
Joe,
You'll need to plot your trend line AND your displaced line, the second line being displaced by changing your y intercept or c value. Now to do this you'll need to creat data series for both in your sheet and plot this second line too. In reality though a vertical displacement won't give you a true parallel line. Think train tracks going around a curve. Your displaced line will be the same radius just pushed up where as a true parallel curve will be a different radius so that the perpendicular distance is the same (rather than the vertical offset distance). Just wanted to check this is what you want first.
Best to post your data up if you need more direction on this
Happy with my advice? Click on the * reputation button below
The way I would usually do it looks something like this:
1) Enter raw data.
2) Regress best fit polynomial using LINEST() function: https://support.office.com/en-us/art...rs=en-US&ad=US If you are using the chart trendline to generate the coefficients of the polynomial, you will need to copy/enter the coefficients from the chart into the spreadsheet.
3) Compute/enter new c parameter for parallel curve.
4) Add a column or columns to the table that computes the desired Y (and X if needed) values for the parallel curve.
5) Then plot the original data, the best fit curve, and the parallel curve in a scatter plot.
conceptually, my proposal is to provide another option in the chart
‘’format trendline’’ panel [ie] a box to request the addition of a
parallel curve plus an associated box to specify the positive or
negative displacement amount.
i will investigate having it done by a custom programmer if it makes
sense to do so. thoughts, suggestions & advise will be appreciated.
if it can be done and I do it, I will post it on the forum for all to use.
I don't know how to modify the built in dialogs, if it can even be done at all. As you can see, my approach never gets into those dialogs -- everything is done in the spreadsheet.
If one can add to those dialogs, it will require programming (in VBA or other language) beyond what I get into. I will ask around to see if anyone knows.
I've had a go at putting something together - please see the attachment.
It works by using a user defined function to parse the equation of the trendline and create a new function with a different constant. This is used to create a second series on the chart.
I haven't tested this with a range of negative coefficients so it might need some tweaking to be robust.
The function is in module1 (Alt F11) and is copied below.
Please Login or Register to view this content.
yep, I'm thinking i can contract with a programmer [via ODesk for instance] to write a custom excel ''add-in'' to do the job. an Odesk programmer did some custom programming for me once. I will
investigate on that route depending on what develops on this thread.
this is going to take me some time to digest. busy today so it may be a while ... but i'll be back.I've had a go at putting something together - please see the attachment.
It works by using a user defined function to parse the equation of the trendline and create a new function with a different constant. This is used to create a second series on the chart.
I haven't tested this with a range of negative coefficients so it might need some tweaking to be robust.
The function is in module1 (Alt F11) and is copied below.
Please Login or Register to view this content.
to summarize, my goal is to have to take no other action than to say i want the second curve and to specify
the displacement value.
All you need to do is change the displacement value in E2. It's current set as 10.
conceptually, my proposal is to provide another option in a chartI've had a go at putting something together - please see the attachment.
It works by using a user defined function to parse the equation of the trendline and create a new function with a different constant. This is used to create a second series on the chart.
I haven't tested this with a range of negative coefficients so it might need some tweaking to be robust.
The function is in module1 (Alt F11) and is copied below.
Please Login or Register to view this content.
‘’format trendline’’ panel [ie] a box to request the addition of a
parallel curve plus an associated box to specify the positive or
negative displacement amount see [see reply #9 in this thread].
I'm not sure [ie] I know just enough to be dangerous, but my goal
might be able to be accomplished with a custom add-in ... which I
would pay someone to write for me. please forgive me if that is exactly
what you have proposed and I just don't realize it.
As an alternative, is it necessary for all of this to occur in the trendline (or other) dialog? Properly programmed, it should be easy to structure a spreadsheet that will have a single cell into which a user can enter a value to specify where to put the parallel curve. In the example, one would only need to interact with cell C4 to move the parallel curve.to summarize, my goal is to have to take no other action than to say i want the second curve and to specify
the displacement value.
Mr Shorty's post of 12-27-2014, 07:23 PM had a very straightforward approach: use LINEST to compute the poly fit parameters in the worksheet, use these parameters to calculate the fitted Y for an appropriate series of X values, then compute a set of Y values offset from these fitted Y values according to an adjusted intercept.
Why go to all the problem of writing (or commissioning) code to extract the parameters from the trendline equation in the chart (with insufficient digits for a good fit, by the way) and add this to the chart? Even crazier, why hope that Microsoft might add such an option to the dialog box?
Jon Peltier
http://PeltierTech.com/
my goal is to be able to see the 2-curves instantly for various polyomial fits of
orders 2 thru 6. if I understand correctly, that is the only way I can do it. by
the way, thanks for the "even crazier" comment ... very charming.
I'm sure once you set up the formulas, you can get a near-instant update when applying it to a new data set.
Just wondering, does this capability exist in any other statistics package? The strength of Excel is that it allows you to take some basic statistics (or other analysis) and tailor it to suit any modifications you need. Other packages, not so much.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks