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:

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Investment[/TD]
[TD]Bookings[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3000[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]4000[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]5000[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]6000[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]7000[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]8000[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]9000[/TD]
[TD]18[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[PolynomialQs]

Hi, thanks for your reply.

I think you're right. I was just trying to use excel as this is the medium in which the data was presented.

I have looked at the rate of change, but I still can't see where one would achevie diminish returns based on another £1,000 investment. I think the answer is probably staring me in the face but I can't work it out.

Can you advise based on that sample data?

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

 

[TenKittens]

Sure:

I added the derivatve and second derivative in there
[TABLE="width: 712"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Investment[/TD]
[TD]Bookings[/TD]
[TD]Derivative = Change Investment/Change Bookings[/TD]
[TD]Secon derivative =Change Derivative/Change of bookings[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]3[/TD]
[TD]--[/TD]
[TD]--[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]5[/TD]
[TD]500[/TD]
[TD]--[/TD]
[/TR]
[TR]
[TD]3000[/TD]
[TD]7[/TD]
[TD]500[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4000[/TD]
[TD]9[/TD]
[TD]500[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5000[/TD]
[TD]11[/TD]
[TD]500[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6000[/TD]
[TD]13[/TD]
[TD]500[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7000[/TD]
[TD]15[/TD]
[TD]500[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8000[/TD]
[TD]16[/TD]
[TD]1000[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]9000[/TD]
[TD]18[/TD]
[TD]500[/TD]
[TD]-250[/TD]
[/TR]
</tbody>[/TABLE]


If you plot your data set, there is a linear growth on Investment to bookings in the selected area except for 8000 where it grew at a higher pace. Therefore, there are no maximum or minimum in this section. It grew linearly for the most part. If we look at the second derivative in between 8000 and 9000 it changes from positive to negative. This implies that there is a little inflexion point in that area, which makes sense given the tiny bump in 8000. None of this points have diminishing returns

 

[PolynomialQs]

Hi, that's really helpful thank you.

I can apply that method to the remainder of the data.

I have now done so, but got this slightly strange looking curve from plotting the second derivative against investment:

What do you make of that?

Sure:

I added the derivatve and second derivative in there
[TABLE="width: 712"]
<tbody>[TR]
[TD]Investment[/TD]
[TD]Bookings[/TD]
[TD]Derivative = Change Investment/Change Bookings[/TD]
[TD]Secon derivative =Change Derivative/Change of bookings[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]3[/TD]
[TD]--[/TD]
[TD]--[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]5[/TD]
[TD]500[/TD]
[TD]--[/TD]
[/TR]
[TR]
[TD]3000[/TD]
[TD]7[/TD]
[TD]500[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4000[/TD]
[TD]9[/TD]
[TD]500[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5000[/TD]
[TD]11[/TD]
[TD]500[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6000[/TD]
[TD]13[/TD]
[TD]500[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7000[/TD]
[TD]15[/TD]
[TD]500[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8000[/TD]
[TD]16[/TD]
[TD]1000[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]9000[/TD]
[TD]18[/TD]
[TD]500[/TD]
[TD]-250[/TD]
[/TR]
</tbody>[/TABLE]


If you plot your data set, there is a linear growth on Investment to bookings in the selected area except for 8000 where it grew at a higher pace. Therefore, there are no maximum or minimum in this section. It grew linearly for the most part. If we look at the second derivative in between 8000 and 9000 it changes from positive to negative. This implies that there is a little inflexion point in that area, which makes sense given the tiny bump in 8000. None of this points have diminishing returns

 

[PolynomialQs]

This image does not seem to have shown up. You can see if at this URL: Imgur: The most awesome images on the Internet