Avatar of BlosMusic
BlosMusic
 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

Avatar of undefined
Last Comment
Saqib Husain

8/22/2022 - Mon
BlosMusic

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

Are you sure there actually is a trend?
StephenJR

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
BlosMusic

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
StephenJR

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

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

BlosMusic

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

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
BlosMusic

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

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

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

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
Patrick Matthews

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
StephenJR

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!

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
BlosMusic

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
StephenJR

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

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!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
StephenJR

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

ASKER
Well, thanks anyway! Thanks for trying.
Patrick Matthews

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

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
d-glitch

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

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Patrick Matthews

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.
ASKER CERTIFIED SOLUTION
Saqib Husain

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
Sign up - Free for 7 days
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
BlosMusic

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
Saqib Husain

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
Saqib Husain

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

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
Patrick Matthews

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
BlosMusic

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
Saqib Husain

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

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Saqib Husain

Select the formula and the press Ctrl-1
BlosMusic

ASKER
Hi Saqib,
I tried this but nothing happened.
Richard
Saqib Husain

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
BlosMusic

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 . . . . . )
Saqib Husain

Which excel are you using?
Saqib Husain

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
Saqib Husain

Looks like did not work.
Saqib Husain

Worked this time
ssaqibh-489954.flv
BlosMusic

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 . . . . .
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Saqib Husain

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

ASKER
Saqib,
It works well!! This is what I did . . . .
See below.
Richard
BlosMusic-490141.flv
Saqib Husain

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

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

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

ASKER
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.
Saqib Husain

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
BlosMusic

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

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