Using the LINEST Function to find equation of my curve

P

PolynomialQs

New Member
Joined
Jul 28, 2016
Messages
7
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
just plot your data as a scatter plot dots only, highlight chart, chart, options, add trendline, choose polynomial power 2
 
Upvote 0
oldbrewer said:
just plot your data as a scatter plot dots only, highlight chart, chart, options, add trendline, choose polynomial power 2
Click to expand...

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
 
Upvote 0
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 ???
 
Upvote 0
PolynomialQs said:
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
Click to expand...

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
 
Upvote 0
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.
 
Upvote 0
if say the data were inaccurate due to a fault in a data measuring device your desired level of accuracy would be pointless.

[TABLE="width: 820"]
<colgroup><col><col><col span="10"></colgroup><tbody>[TR]
[TD]Investment[/TD]
[TD]Bookings[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Investment[/TD]
[TD]Bookings[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1000[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]0.021[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2000[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]0.0215[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3000[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]0.021667[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4000[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]0.02175[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5000[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]109[/TD]
[TD="align: right"]0.0218[/TD]
[TD]MAX[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6000[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]0.020167[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7000[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7000[/TD]
[TD="align: right"]138[/TD]
[TD="align: right"]0.019714[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8000[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]157[/TD]
[TD="align: right"]0.019625[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9000[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]0.019333[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]my TIP OF THE DAY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]in this example[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]do not invest 9000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]by plotting bookings divided by investment[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]you can see the break even point[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]have 9 investments of 1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]is at 5000 investment[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]giving 27 bookings rather than 18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]so just have a line chart plotting the ratio[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]it will peak once, maybe twice[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]depending on investment rules and charges[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.

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

[TABLE="width: 820"]
<tbody>[TR]
[TD]Investment[/TD]
[TD]Bookings[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Investment[/TD]
[TD]Bookings[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1000[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]0.021[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2000[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]0.0215[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3000[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]0.021667[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4000[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]0.02175[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5000[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]109[/TD]
[TD="align: right"]0.0218[/TD]
[TD]MAX[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6000[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]0.020167[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7000[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7000[/TD]
[TD="align: right"]138[/TD]
[TD="align: right"]0.019714[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8000[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]157[/TD]
[TD="align: right"]0.019625[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9000[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]0.019333[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]my TIP OF THE DAY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]in this example[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]do not invest 9000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]by plotting bookings divided by investment[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]you can see the break even point[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]have 9 investments of 1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]is at 5000 investment[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]giving 27 bookings rather than 18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]so just have a line chart plotting the ratio[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]it will peak once, maybe twice[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]depending on investment rules and charges[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
PolynomialQs said:
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.
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
LINEST with start/end conditions to select data.
Replies
3
Views
1K
Anthony47
A
G
  • Solved
Need to compare XY coordinates from two data sets to find the closest point from one data set compared to the other
Replies
2
Views
8K
Gary1234
G
M
  • Question Question
Multi-Variable Curve Fitting
Replies
1
Views
5K
NdNoviceHlp
NdNoviceHlp
D
  • Question Question
LINEST using Filtered Data
Replies
2
Views
6K
dabluebery
D
hip2b2
  • Question Question
Charting Problem (in plural) and Breakeven Calculation
Replies
2
Views
644
hip2b2
hip2b2

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top