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

BlosMusic
BlosMusic used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I would rather not send the actual data, as it is sensitive.
Most Valuable Expert 2011
Top Expert 2011

Commented:
Are you sure there actually is a trend?
On what basis do you think that there is a trend?

Author

Commented:
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.

Author

Commented:
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.

Author

Commented:
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.

Author

Commented:
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
Top Expert 2010

Commented:
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
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!

Author

Commented:
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
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.

Author

Commented:
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!
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!

Author

Commented:
Well, thanks anyway! Thanks for trying.
Top Expert 2010

Commented:
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

Author

Commented:
OK, thanks, Patrick. I guess I was hoping for more than Excel or anything else can give.
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?

Author

Commented:
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
Top Expert 2010

Commented:
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.
I think this will let you do what you want. It will interpolate within the data.

Do this on the sheet which contains the data then move it elsewhere.

In cell C2 enter the x-value

in cell D2 enter this formula
=MATCH(C2,B5:B51)

In cell E2 enter this formula
=INDEX(AD5:AD51,D2)+(INDEX(AD5:AD51,D2+1)-INDEX(AD5:AD51,D2))/(INDEX(B5:B51,D2+1)-INDEX(B5:B51,D2))*(C2-INDEX(B5:B51,D2))

E2 will contain the Y-value corresponding to the x-value

Author

Commented:
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
Work with higher precision
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
Top Expert 2010

Commented:
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

Author

Commented:
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
Just select the formula box on the graph. The rest is the same as you do for the cells.

Author

Commented:
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.
Select the formula and the press Ctrl-1

Author

Commented:
Hi Saqib,
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

Author

Commented:
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 . . . . . )
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.
Looks like did not work.

Author

Commented:
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!!

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.

Author

Commented:
Saqib,
It works well!! This is what I did . . . .
See below.
Richard
BlosMusic-490141.flv
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.

Author

Commented:
I DID IT!!!!
I deleted the trendline and started again - and - - -  hey presto!!!
Many thanks Saqib.

Author

Commented:
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.

Author

Commented:
Very good all round. Saqib in particular has done much . . . . .
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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial