BlosMusic

asked 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?

Last Comment

Are you sure there actually is a trend?

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

ASKER

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

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

ASKER

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.

ASKER

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.

ASKER

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!

ASKER

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.

ASKER

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!

ASKER

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

ASKER

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?

ASKER

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

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.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

ASKER

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

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

ASKER

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.

ASKER

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.

Select the formula and the press Ctrl-1

ASKER

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

ASKER

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.

Looks like did not work.

Worked this time

ssaqibh-489954.flv

ASKER

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

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

ASKER

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.

ASKER

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.

ASKER

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.

ASKER

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

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

ASKER

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

Richard

Richard

Thankyou for understanding.

Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K

Questions

--

Followers

--

Top Experts

Get a personalized solution from industry experts

TRUSTED BY

ASKER