asked on # How do I get a better trendline than those offered by Excel 2010?

I have a chart derived from data, which gives a line that none of the Excel trend lines seem to match very well. Is there a better source of trend lines that i can access to give me a line from which i can get a formula?

Microsoft ExcelMath / Science

I would rather not send the actual data, as it is sensitive.

Are you sure there actually is a trend?

On what basis do you think that there is a trend?

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

Ah - I see. Perhaps what I better mean is "how do I get a 'best fit' mathematical line to superimpose on my graph to give me something that has a formula". In fact, I will send you the file, as I want to know the answer to this. Proposed-pricing-graph--W-.xlsx

Well your r squared value is 0.246 so the validity of fitting a trend line at all is questionable.

To do it though you just go to Chart > Add Trendline > Options and there is a tickbox for showing the equation and the R value.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

That is 2003. xl 2007 instructions here;

http://office.microsoft.com/en-us/excel-help/add-change-or-remove-a-trendline-in-a-chart-HP010007461.aspx

http://office.microsoft.com/en-us/excel-help/add-change-or-remove-a-trendline-in-a-chart-HP010007461.aspx

Yes, but we are still talking about trend lines here, and I understand what you say about the r^2-value; it is 0.246 as you say, and so not much good. But what I want to do is just fit some sort of suitable curve - almost, if you like, by eye, to give me a mathematical curve of some sort from which I can get a formula. Then I can interpolate as finely as I like to get any value of y for any x. These are prices for a product which is almost infinitely variable, and I need a coherent pattern of prices, derivable down to any accuracy, for some clients. Surely there is a curve, albeit complex in mathematical terms, which would give a reasonable fit.None of the Excel trend lines (a different beast, I know) works. Sorry if I sound obtuse, but I understand that I don't want a trend line - but I do want a "nice" curve!

You could try a polynomial trendline, that is a better fit.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

I tried that but whatever polynomial trend line I use, it doesn't do the trick.At lower powers it doesn't even vaguely match, and at higher powers it simple creates a quasi-sinusoidal curve which wouldn't work for me at all. You'd imagine there must be a facility somewhere that would allow a line to be stretched and pulled about until it got somewhere close, and then give you its formula. Or perhaps not.

I don't understand the basis on which you want to draw your curve. The trendline is calculated mathematically and gives you what it gives you. I'm not sure what you think would be better.

Btw the power option probably gives you the best option, but is not valid for your dataset.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

If I could somehow pick up the right hand end of that power line and lift it up towards the actual graph, that would be brilliant (!!)

Proposed-pricing-graph--W-.xlsx

BlosMusic,

I concur with Stephen. "Power" is going to give you the best fit. Indeed, using Power I see a 0.86 r^2, which is rather strong.

Note that "curve of best fit" does NOT mean "curve that includes every single one of my data points".

:)

Patrick

I concur with Stephen. "Power" is going to give you the best fit. Indeed, using Power I see a 0.86 r^2, which is rather strong.

Note that "curve of best fit" does NOT mean "curve that includes every single one of my data points".

:)

Patrick

Excel help says "A power trendline is a curved line that is used with data sets that compare measurements that increase at a specific rate — for example, the acceleration of a race car at 1-second intervals."

It might look brilliant, but I don't think it's necessarily brilliant in any more signifcant sense!

It might look brilliant, but I don't think it's necessarily brilliant in any more signifcant sense!

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

Hi,

Yes, I know that I can't get a curve that fits all the data points. I don't expect that. But there must be a mathematical curve that is a better fit than any of the trend lines in Excel. So, as I said, maybe I'm not looking for a trend line at all, but rather a curve that is just a more reasonable fit to my graph. The power one is strong, but I don't think the best fit to my curve is a power at all. The curve doesn't have to fit all the parts - just needs to be "reasonable". I attach a scribble on my graph to show what I mean. Maybe its some form of logarithmic thingamijig I need, or an exponential cosine or something exotic! Proposed-pricing-graph-amended--.xlsx

Yes, I know that I can't get a curve that fits all the data points. I don't expect that. But there must be a mathematical curve that is a better fit than any of the trend lines in Excel. So, as I said, maybe I'm not looking for a trend line at all, but rather a curve that is just a more reasonable fit to my graph. The power one is strong, but I don't think the best fit to my curve is a power at all. The curve doesn't have to fit all the parts - just needs to be "reasonable". I attach a scribble on my graph to show what I mean. Maybe its some form of logarithmic thingamijig I need, or an exponential cosine or something exotic!

That looks suspiciously close to the curve you have. You haven't defined "reasonable" - you appear to be going by how it looks - and if you look at your curve it it not obvious what would happen as you move right along the x-axis.

I am going by how it looks, indeed. The orange curve I drew is what i am looking for, and I just imagined that somewhere there would be a method of deriving a mathematical curve of similar shape. The power curve (Series One on the chart) is OK as far as it goes (up to x = 4000) but then it continues on downwards, whereas there just has to be a mathematical equation giving a curve which starts going up again - more like my orange line. I am not trying to be difficult or stupid, and, yes, it is a bit of a case of "what it looks like" rather than being a true trend, but even if it's a somewhat esoteric formula, there must be one that more nearly follows my results. If I could find an equation that approximates to my ornage scribble, I would be happy!

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Well I'm not saying you can't, I don't know, but any mathematical equation will be based in some way or other on the data as it is so it will not make any arbitrary allowance for anything else. Good luck on your quest!

Well, thanks anyway! Thanks for trying.

BlosMusic,

I highly doubt that you are going to get a neater and tidier fit to your data. 0.86 is a very, very high r^2.

Looking at your data, I'd be surprised if there were a single continuous function that describes your data.

Patrick

I highly doubt that you are going to get a neater and tidier fit to your data. 0.86 is a very, very high r^2.

Looking at your data, I'd be surprised if there were a single continuous function that describes your data.

Patrick

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

OK, thanks, Patrick. I guess I was hoping for more than Excel or anything else can give.

Anyway, I'm grateful you tried.

Richard

Anyway, I'm grateful you tried.

Richard

You really need to understand your data before trying to do a curve fit.

Units are important.

Is the y-axis dollars and the x-axis the size of the order?

So the price for 1 widget is $140. The price for 50 is $125 each. And the price for 3000 is $41.50

Lots of products might have a similar price profile.

I expect you could find a very good fit for this section of the graph maybe even the odd bump

between 1000 and 1750.

But why (what is the rational) for the price rising on quantities above 3000?

Wouldn't any sane customer buy three lots of 3000 instead of one lot of 9000?

Units are important.

Is the y-axis dollars and the x-axis the size of the order?

So the price for 1 widget is $140. The price for 50 is $125 each. And the price for 3000 is $41.50

Lots of products might have a similar price profile.

I expect you could find a very good fit for this section of the graph maybe even the odd bump

between 1000 and 1750.

But why (what is the rational) for the price rising on quantities above 3000?

Wouldn't any sane customer buy three lots of 3000 instead of one lot of 9000?

Pretty close! The x-axis is the size of the product (which is a complicated mechanical engineering assembly which is purchased in units - and the value of each may be between £25,000 and £200,000) in cubic metres capacity, and the y-axis is the cost per final effective surface square metre of this product (which is a complicated tank) in pounds sterling. We can do this, as although there are many items making up the product, the result is measurable for our purposes in £/square metre of surface area.

So really it is as follows:- A 1,000 cubic metre tank costs £52/m^2 (surface area) and a 3,500m^3 tank costs £42.80/m^2, and so on. When the tanks are very small, they cost a lot per square metre, and when they are pretty big they don't cost so much, and when they get very big they cost a bit more per square metre again. The bump between 1000 and 1750 is quirky, but as I say, it's complicated, and bits have to be added to our tanks at those capacities. But commercially we are quite happy to give our client a price/m^2 that irons out these quirks, and so long as I can personally see that a good "mathematical" (by which I mean smooth and calculable) curve has been fitted, then I am happy, and I can tell the client the exact price for any size, even if he wants one that is 4,125.6m^2 (or some such) in capacity. So, if he wants this size of tank, I know the exact surface area, and I am perfectly happy that the price I give him will be good for him and profitable for us. But I do want it to be predictable, and I don't want to give him any reason (as you have observed) to question the vagaries of quirky humps in our price range. Trust me! Hope that helps, and sorry for the length of answer.

Richard

So really it is as follows:- A 1,000 cubic metre tank costs £52/m^2 (surface area) and a 3,500m^3 tank costs £42.80/m^2, and so on. When the tanks are very small, they cost a lot per square metre, and when they are pretty big they don't cost so much, and when they get very big they cost a bit more per square metre again. The bump between 1000 and 1750 is quirky, but as I say, it's complicated, and bits have to be added to our tanks at those capacities. But commercially we are quite happy to give our client a price/m^2 that irons out these quirks, and so long as I can personally see that a good "mathematical" (by which I mean smooth and calculable) curve has been fitted, then I am happy, and I can tell the client the exact price for any size, even if he wants one that is 4,125.6m^2 (or some such) in capacity. So, if he wants this size of tank, I know the exact surface area, and I am perfectly happy that the price I give him will be good for him and profitable for us. But I do want it to be predictable, and I don't want to give him any reason (as you have observed) to question the vagaries of quirky humps in our price range. Trust me! Hope that helps, and sorry for the length of answer.

Richard

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

What you are describing sounds like a common problem for items that have increasing economies of scale--until they don't.

For example, a school will have a principal. You can get economies of scale on the principal's salary by increasing the number of students, but only up to a point, because eventually you'll have to add an assistant principal. But then you can again get additional economies of scale by adding more students, until eventually you have to add another assistant principal. And so on.

The problem is that it looks like you do not, and cannot, have a single continuous function that covers all of your possible situations. The power function gives you a very good approximation, though.

For example, a school will have a principal. You can get economies of scale on the principal's salary by increasing the number of students, but only up to a point, because eventually you'll have to add an assistant principal. But then you can again get additional economies of scale by adding more students, until eventually you have to add another assistant principal. And so on.

The problem is that it looks like you do not, and cannot, have a single continuous function that covers all of your possible situations. The power function gives you a very good approximation, though.

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
Thanks for that. That would work.

However, here is another problem. I have another graph which gives me actual price against actual volume, and the graph from this gives me a very acceptable trend (6th order polynomial), with an r-squared of nearly 1! But when I put these values back into the spreadsheet, using the formula, the values seem to be completely different towards the upper end of the graph. Any idea why? See attached graph. (I am very grateful to all concerned for their really useful input and expertise). Example-for-trend-curve-values.xlsx

However, here is another problem. I have another graph which gives me actual price against actual volume, and the graph from this gives me a very acceptable trend (6th order polynomial), with an r-squared of nearly 1! But when I put these values back into the spreadsheet, using the formula, the values seem to be completely different towards the upper end of the graph. Any idea why? See attached graph. (I am very grateful to all concerned for their really useful input and expertise).

Work with higher precision

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

Select the equation and format it as scientific with say 8 places of decimal.

Here is the formula

=-4.902497E-18*C11^6 + 1.53061438E-13*C11^5 - 0.00000000185767037*C11^4 + 0.0000108872704*C11^3 - 0.0307921119*C11^2 + 55.3559319*C11 + 17241.8824

=-4.902497E-18*C11^6 + 1.53061438E-13*C11^5 - 0.00000000185767037*C11^4 + 0.0000108872704*C11^3 - 0.0307921119*C11^2 + 55.3559319*C11 + 17241.8824

BlosMusic,

After making Saqib's adjustment you get a tremendous fit to the data.

I am confused, though: the data from your latest example does not seem to bear any relationship at all to your previous samples :)

Patrick

After making Saqib's adjustment you get a tremendous fit to the data.

I am confused, though: the data from your latest example does not seem to bear any relationship at all to your previous samples :)

Patrick

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Hi both.

Firstly, Patrick - the second set of data was for [price for a capacity of tank] v. [capacity of tank] (in other words, price for each size we do). The first set I sent was for [price per square metre of tank surface (it is a complicated tank!)] v. [capacity of tank]. Using Saqib's formula works well enough for me to be able to interpolate values from the original graph I sent, but then I thought about it and have decided that the second graph (the price for each size of tank one) will also work OK for what I need. Also, the trend curve works really well.

Secondly, Saqib - how do I arrive at the formula you gave me? Where do I select the 8 places of decimal in Excel to give me your perfect formula? I know how to do that for a cell, but how do I do it for a trend line formula? Or is it staring me straight in the face?

Thanks, all, for your excellent support. Your knowledge is formidable.

Richard

Firstly, Patrick - the second set of data was for [price for a capacity of tank] v. [capacity of tank] (in other words, price for each size we do). The first set I sent was for [price per square metre of tank surface (it is a complicated tank!)] v. [capacity of tank]. Using Saqib's formula works well enough for me to be able to interpolate values from the original graph I sent, but then I thought about it and have decided that the second graph (the price for each size of tank one) will also work OK for what I need. Also, the trend curve works really well.

Secondly, Saqib - how do I arrive at the formula you gave me? Where do I select the 8 places of decimal in Excel to give me your perfect formula? I know how to do that for a cell, but how do I do it for a trend line formula? Or is it staring me straight in the face?

Thanks, all, for your excellent support. Your knowledge is formidable.

Richard

Just select the formula box on the graph. The rest is the same as you do for the cells.

Sorry, you've lost me. I select the formula on the graph, and then where do I go from there? There is no "format cells" equivalent for the formula that I can see, once I have selected the formula. When I select the formula, there is no "format cells" option, and if instead I go to "chart tools" and then "format" and then "format selection" and then (the only option I can see) "format trendline label" and then go to "number" and select 8 decimal places - - - - nothing changes. How did you get your very exact looking formula?

I am not trying to be obtuse - I just don't understand.

I am not trying to be obtuse - I just don't understand.

Your help has saved me hundreds of hours of internet surfing.

fblack61

Select the formula and the press Ctrl-1

Hi Saqib,

I tried this but nothing happened.

Richard

I tried this but nothing happened.

Richard

Open the file

right-click on the y= portion of the formula

select Format trendline label

from the left pane select number

from the right pane select scientific

give 8 decimal places

Close

right-click on the y= portion of the formula

select Format trendline label

from the left pane select number

from the right pane select scientific

give 8 decimal places

Close

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

I do exactly that, but the formula remains the same (y=-5E-18x^6+2E . . . . etc etc - not your

y=-4.902497E-18*C11^6 + 1.53061438E-13*C11^5 . . . . . )

y=-4.902497E-18*C11^6 + 1.53061438E-13*C11^5 . . . . . )

Which excel are you using?

I have attempted to record a screencast for the first time. I am not sure how it works. lets see.

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

Looks like did not work.

Worked this time

ssaqibh-489954.flv

Saqib,

Brilliant - how did you do that?!! Amazing demo! I like it!!

However - - - - - that is exactly what I was doing, and when I click on "close" in the "Format Trendline Label" dialogue box, nothing has changed, despite setting Number/Scientific/8 etc. etc. I am using Excel 2010. Your support is very much appreciated. I think we are nearly there . . . . .

Brilliant - how did you do that?!! Amazing demo! I like it!!

However - - - - - that is exactly what I was doing, and when I click on "close" in the "Format Trendline Label" dialogue box, nothing has changed, despite setting Number/Scientific/8 etc. etc. I am using Excel 2010. Your support is very much appreciated. I think we are nearly there . . . . .

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

>>>>Brilliant - how did you do that?!! Amazing demo! I like it!!

Just below your comment box on EE you will find a link called Screencast

Try it yourself and send back what you are doing for us to see if you are missing something.

Just below your comment box on EE you will find a link called Screencast

Try it yourself and send back what you are doing for us to see if you are missing something.

I am at a loss. Is this the same file which you have uploaded.

- Try downloading the file back and do the same on it.

- Try deleting the trendline and then start again.

- Try moving to another computer.

- Try downloading the file back and do the same on it.

- Try deleting the trendline and then start again.

- Try moving to another computer.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

I DID IT!!!!

I deleted the trendline and started again - and - - - hey presto!!!

Many thanks Saqib.

I deleted the trendline and started again - and - - - hey presto!!!

Many thanks Saqib.

I've requested that this question be closed as follows:

Accepted answer: 0 points for BlosMusic's comment http:/Q_27243789.html#36390316

Assisted answer: 20 points for rorya's comment http:/Q_27243789.html#36328083

Assisted answer: 20 points for StephenJR's comment http:/Q_27243789.html#36328096

Assisted answer: 20 points for StephenJR's comment http:/Q_27243789.html#36328323

Assisted answer: 20 points for StephenJR's comment http:/Q_27243789.html#36328343

Assisted answer: 20 points for StephenJR's comment http:/Q_27243789.html#36328581

Assisted answer: 20 points for StephenJR's comment http:/Q_27243789.html#36328657

Assisted answer: 0 points for BlosMusic's comment http:/Q_27243789.html#36328708

Assisted answer: 20 points for StephenJR's comment http:/Q_27243789.html#36328890

Assisted answer: 20 points for StephenJR's comment http:/Q_27243789.html#36329055

Assisted answer: 20 points for StephenJR's comment http:/Q_27243789.html#36329117

Assisted answer: 20 points for matthewspatrick's comment http:/Q_27243789.html#36329546

Assisted answer: 20 points for d-glitch's comment http:/Q_27243789.html#36330152

Assisted answer: 20 points for matthewspatrick's comment http:/Q_27243789.html#36330661

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36330823

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36334332

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36334338

Assisted answer: 20 points for matthewspatrick's comment http:/Q_27243789.html#36334682

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36344144

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36347605

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36383667

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36383741

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36384166

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36384173

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36386602

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36388264

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36390253

for the following reason:

Brilliant.<br />Many thanks to Saqib, but also to the others. Very good indeed.

Accepted answer: 0 points for BlosMusic's comment http:/Q_27243789.html#36390316

Assisted answer: 20 points for rorya's comment http:/Q_27243789.html#36328083

Assisted answer: 20 points for StephenJR's comment http:/Q_27243789.html#36328096

Assisted answer: 20 points for StephenJR's comment http:/Q_27243789.html#36328323

Assisted answer: 20 points for StephenJR's comment http:/Q_27243789.html#36328343

Assisted answer: 20 points for StephenJR's comment http:/Q_27243789.html#36328581

Assisted answer: 20 points for StephenJR's comment http:/Q_27243789.html#36328657

Assisted answer: 0 points for BlosMusic's comment http:/Q_27243789.html#36328708

Assisted answer: 20 points for StephenJR's comment http:/Q_27243789.html#36328890

Assisted answer: 20 points for StephenJR's comment http:/Q_27243789.html#36329055

Assisted answer: 20 points for StephenJR's comment http:/Q_27243789.html#36329117

Assisted answer: 20 points for matthewspatrick's comment http:/Q_27243789.html#36329546

Assisted answer: 20 points for d-glitch's comment http:/Q_27243789.html#36330152

Assisted answer: 20 points for matthewspatrick's comment http:/Q_27243789.html#36330661

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36330823

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36334332

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36334338

Assisted answer: 20 points for matthewspatrick's comment http:/Q_27243789.html#36334682

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36344144

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36347605

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36383667

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36383741

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36384166

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36384173

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36386602

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36388264

Assisted answer: 20 points for ssaqibh's comment http:/Q_27243789.html#36390253

for the following reason:

Brilliant.<br />Many thanks to Saqib, but also to the others. Very good indeed.

Very good all round. Saqib in particular has done much . . . . .

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Richard, with due respect to you and to other experts,

This is not how EE expects you to close a question. You should select one comment which actually answered your question because these questions are stored in the knowledgebase for the benefit of others and with so many accepted comments it would not help to identify which comment was the most relevant. You might select one or two others which had contributed to the selected answer.

I would suggest that you select my response #36330823 which actually addressed the original question. If one of the prior comments by other experts were useful towards the original question then you may select it as an assisted solution, but looking at your responses to them none appears to qualify as such.

Saqib

This is not how EE expects you to close a question. You should select one comment which actually answered your question because these questions are stored in the knowledgebase for the benefit of others and with so many accepted comments it would not help to identify which comment was the most relevant. You might select one or two others which had contributed to the selected answer.

I would suggest that you select my response #36330823 which actually addressed the original question. If one of the prior comments by other experts were useful towards the original question then you may select it as an assisted solution, but looking at your responses to them none appears to qualify as such.

Saqib

With apologies for my slow learning curve :-), thanks Saqib for all the effort. It is much appreciated.

Richard

Richard

Thankyou for understanding.

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23