Using the LINEST Function to find equation of my curve

[PolynomialQs]

Hi,

I'm trying to find the point of diminishing returns on some econometric data we have received that shows money invested to bookings made.

To do this I have been trying to find the equation of my curve in excel. I've seen a few other posts about using the LINEST function to do this, but after following the instructions, I've had no luck. Each time excel has a problem with the values I use or says I'm not doing something else correctly.

Partial Data Set:

Investment	Bookings
1000	3
2000	5
3000	7
4000	9
5000	11
6000	13
7000	15
8000	16
9000	18

<tbody>
</tbody>

This data goes on for 250 lines (£250,000 investment).

Can anyone shed some light on what I might be doing wrong with the LINEST function? I just want to find the formula of the curve produced by plotting this data on the x and y axis, differentiate twice, solve = to 0 and find the x and y coordinates of the PODMR (point of diminishing marginal returns)

Many thanks in advance,
Matt

 

[oldbrewer]

just plot your data as a scatter plot dots only, highlight chart, chart, options, add trendline, choose polynomial power 2

 

[PolynomialQs]

just plot your data as a scatter plot dots only, highlight chart, chart, options, add trendline, choose polynomial power 2

Hi, thanks for your reply. I need this data to be as accurate as possible, and a Polynomial Trend Line to order 2 won't cut it, neither will a 6 which is the maximum I can go.

I also am keen to understand how to effectively use the LINEST function.

Many thanks,
Matt

 

[oldbrewer]

Matt - is your data that accurate - is the normal variation of the data very small? - are there any special causes affecting the data - I thought linest was for straight line plots only ???

 

[TenKittens]

Hi, thanks for your reply. I need this data to be as accurate as possible, and a Polynomial Trend Line to order 2 won't cut it, neither will a 6 which is the maximum I can go.

I also am keen to understand how to effectively use the LINEST function.

Many thanks,
Matt

LINEST provides the least squares approximation of any curve you plot. LINEST is not designed to find the inflexion points on second derivatives.
The easiest way to solve your problem is to manually calculate the rate of change between cells (change bookings)/ Change of investements), i.e the derivative. That will give . Now calculate the change of your previous result ls with respect to the change of investment (i.e. the second derivative) that way you can characterize the behavior of the function

 

[PolynomialQs]

I have seen threads on here stating that is can be used to determine the coefficients of quadratic curves? I have no seen reference to the normal variation of this data set, I've just been given some data to manipulate.

In trying to find the point of diminishing returns, but potentially this is not the most efficient way to go about doing it.

 

[oldbrewer]

if say the data were inaccurate due to a fault in a data measuring device your desired level of accuracy would be pointless.

Investment	Bookings						Investment	Bookings
1000	3						1000	21	0.021
2000	5						2000	43	0.0215
3000	7						3000	65	0.021667
4000	9						4000	87	0.02175
5000	11						5000	109	0.0218	MAX
6000	13						6000	121	0.020167
7000	15						7000	138	0.019714
8000	16						8000	157	0.019625
9000	18						9000	174	0.019333
my TIP OF THE DAY
							in this example
do not invest 9000						by plotting bookings divided by investment
							you can see the break even point
have 9 investments of 1000						is at 5000 investment
giving 27 bookings rather than 18
							so just have a line chart plotting the ratio
							it will peak once, maybe twice
							depending on investment rules and charges

<colgroup><col><col><col span="10"></colgroup><tbody>
</tbody>

 

[PolynomialQs]

Hi, thanks for your reply. Not sure where you have got the data you've shown in the right hand table.

For this data I'm looking to see when an additional £1000 investment starts to reduce in a diminishing returns on marginal bookings.

if say the data were inaccurate due to a fault in a data measuring device your desired level of accuracy would be pointless.

Investment	Bookings						Investment	Bookings
1000	3						1000	21	0.021
2000	5						2000	43	0.0215
3000	7						3000	65	0.021667
4000	9						4000	87	0.02175
5000	11						5000	109	0.0218	MAX
6000	13						6000	121	0.020167
7000	15						7000	138	0.019714
8000	16						8000	157	0.019625
9000	18						9000	174	0.019333
my TIP OF THE DAY
							in this example
do not invest 9000						by plotting bookings divided by investment
							you can see the break even point
have 9 investments of 1000						is at 5000 investment
giving 27 bookings rather than 18
							so just have a line chart plotting the ratio
							it will peak once, maybe twice
							depending on investment rules and charges

<tbody>
</tbody>

 

[oldbrewer]

it is made up example data to show logic and principle

 

[TenKittens]

I have seen threads on here stating that is can be used to determine the coefficients of quadratic curves? I have no seen reference to the normal variation of this data set, I've just been given some data to manipulate.

In trying to find the point of diminishing returns, but potentially this is not the most efficient way to go about doing it.

LINEST is an approximation using least squares, not an exact match. Given that you first said that you want to find the exact point, this would be a poor way of doing it

If you want to use Linest with a quadratic formula you need to follow these steps

You are trying to create a curve Y=a+bX+cX^2
1) Hence you will need to have three columns, Y, X (which you already have) and create X^2

2) After that you need to go to the Data tab and click on the Data analysis box (you have to enable the Data Analysis ToolPak ) and click on regression. Choose the right Y for X select the two columns that contain X. This will give you the coefficients of the curve along with a bunch of stats

That being said, I think that you don't have an Excel question but a methodological question. Why do this when you can manually calculate a derivative and a second derivative? A derivatice is just the rate of change, which you can easily calculate
Hope this helps