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?

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

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

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

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

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

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?

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.

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

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

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

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

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

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.

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

- Try deleting the trendline and then start again.

- Try moving to another computer.

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

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial