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

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?

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.

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

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

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

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!

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.

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!

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

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?

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

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.

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

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

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.

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

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

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.

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

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.

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.

I DID IT!!!!
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.

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

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

Thankyou for understanding.