How Do I Calculate A Polynomial Curve Fit

Sep 7, 2009

For Excel 2007 -

I am looking for a function [or method] to calculate a polynomial curve fit. I can do it on a graph with the existing graph and chart tools, but I need to calculate it myself. Does such software exist somewhere in my Excel 2007 package, or can anyone recommend a freeware program? I am hesitant to download an unknown, un-recommended add-in.

ADVERTISEMENT

Calculate 2nd Degree Polynomial Trendline Coefficients In VBA Without Using Cells

Jul 17, 2014

i have a function in a cell (that works) to extract coefficients from a range of cells in a workbook:

VB: =INDEX(LINEST(CP25:CP27,CQ25:CQ27^{1,2}),1)}

i have variables for cp25:cp27 and cq25:cq27 already defined in my vba code. the values for these in the case i am working on are as follows (returns 110.5):

cp25 = 560
cp26 = 570
cp27 = 580
cq25 = .414
cq26 = .479
cq27 = .536

1) how to do this function in VBA only - this is part of a UDF and cannot have any helper cells
2) how to refer to 560,570,580 as a 'range'. is there a way to put these six variables into my ranges for later processing?

All of the google searches i have deal only with linear regression, taking from existing graphs, or say to just use the function i have above.

I have tried

VB:

Var = Application.WorksheetFunction.LinEst(Sheets("references").Range("CP25:CP27"), Sheets("references").Range("CQ25:CQ27^{1,2}"), 1)
[COLOR=#333333][/COLOR]

but return #value! errors. when i remove the ^{1,2} portion, i do return a value but it is incorrect (returns 160), what is the correct syntax for adding in the ^{1,2}? if you do that would be fantastic, but brings me back to issue #2 in that i need to refer to my variables in the vba code and not this range (as they will eventually be going away).

View 5 Replies View Related

Calculate Area Under The Curve

Apr 13, 2008

i am getting some data via RS 232 port and plotting it straight to excel, which comes out to be in forms of waves you can say sine waves, now i need to calculate area under the curve, say like if i highlight some area it will automatically calculate the area, or if i select two points on the curve it will calculate the area. please help me out i am really stuck at this part.

View 9 Replies View Related

Calculate Best Fit Equation Of Curve

May 16, 2008

I am trying to calculate the equation of a curve which best fits some data. On the x-axis is date and y-axis the readings. The curve is quadratic, so on the chart I am fitting a 2nd order polyomial and displaying the equation. To prove that the equation the trendline gives me is reasonable, I am applying the equation on the x parameter to get the y parameter estimate and looking to see the error between predicted and actual - see attached.

The equation and the output from it are clearly rubbish, even though on the graph the trendline is a reasonable fit - see attached. Has anyone any ideas why? I think it has something to do with using a date on the x-axis but no idea why. Is there a way to correct for this? I also tried using the following I to calculate the coefficients which came up with a reasonable forecast (although linest should only be used for linear data): =LINEST(C3:C15,B3:B15^{1,2,3})

View 2 Replies View Related

Calculate Maximum Point On Curve From Xy Scatter Graph

Jan 18, 2008

I have put my values into a XY scatter graph but need to find the maximum point i.e when the gradient is zero. I am using visual basic to obtain my data and draw my graph.

View 7 Replies View Related

Derive Curve From Values Then Apply Curve To Another Dataset?

Jun 5, 2014

I have a data set (Set#1) that consists of a positional rank (1-240) and a dollar value ($46-$1) for each positional rank. It's not a straight line set.

DataSet.JPG

What I need to do is take that curve and apply it to another data set (Set#2) (1-240) and assign a dollar value ($46-$1) to each positional rank.

A "corner cutting" way of doing it would be an Index/Match to simply carry over the dollar value from (for example) #45 from my Set#1 to #45 in my Set#2. This could work reasonably well with data sets of the same size, but mine aren't always the same size, so I need to apply the curve itself. I

View 1 Replies View Related

Polynomial Function

Feb 9, 2010

I have plotted a graph which compares the miles done with the petrol used.

I'm trying find a formula which allows me to apply the petrol and would give me the miles covered.

From excel I have applied the tread line of polynomial and obtained the following formula...

y = 1E-06x5 - 0.0002x4 + 0.0161x3 - 0.4893x2 + 7.5295x - 20.87

R = 0.9989

When is it applied to excel with the value of petrol it does not give me the miles.
I'm working out the formula in a incorrect manner?

I have attached the excel file.

View 10 Replies View Related

Polynomial Equations

May 14, 2006

y = 1.083333333x4 - 7.3333333x3 + 16.416667x2 - 6.16667x + 4

Above is the polynomial equation of a trend line as shown on my chart, I have extracted the numbers into separate cells using LINEST, so now I have in cells A20 to E20 the numbers 1.083333333, -7.333333, 16.416667, -6.16667 and 4 respectively.With a "y" value of 25 how do I go about solving for a value of "x".

I've been stubbornly struggling with this on and off for a couple of weeks now and I'm not getting any closer to working it out and the websites I've found explaining the procedure all seem to go over my head by the time I get into the fourth or fifth paragraph.

View 11 Replies View Related

3rd Degree Polynomial

Jul 31, 2008

I have this data:

x y
310 516
320 526
325 535
330 587
335 640
340 715
345 857
350 1040

When I perform a third degree polynomial curve fit I get this equation:
y = 0.0106x3 - 10.0266x2 + 3152.3381x - 329943.8370
R2 = 0.9987

Which isnt right. On a calculator, I get negative numbers. Does excel have problems with 3rd degree polynomials?

View 9 Replies View Related

More Digits From Polynomial Regressions?

Nov 21, 2008

The functions of trend lines in charts are nice, but the coefficients of the output equations are all with only one-digit for the polynomial fits. The single-digit fits by the fitting equations often result in higher deviations from what we have seen in the charts.

I also tried to use the statistical functions but it seams that I cannot get any outputs of these coefficients except for the slope of LINEST, which is not sufficient for polynomials.

I also use addins such as as regressions but cannot get anything either to help for these coefficients.

May someone tell me how to make such outputs with more digits so the equations can be more precisely fitting please?

View 6 Replies View Related

Solving Polynomial Equations

Feb 16, 2007

I don't actually own Excel as yet - but I'm wondering if someone could tell me whether Excel is capable of solving what I'm told are 'polynomial equations'. An example of the sort of equation I hope to solve is: d/(1.18 + d) + d/(1.86 + d) + d/(9 + d) + d/(8.8 + d) = 2
d=? {I happen to know d = 3.65(approx) in this example btw}

Often there will be many more than four terms...

View 6 Replies View Related

Create A Polynomial Equation Of Z From The X And Y Inputs?

Jan 20, 2014

I'm familiar with 2d graphs, trendlines, and regression equations. Now I have some tabular data that has 2 input variables and a result. As with my 2d data, I would like Excel to create a polynomial equation of z from the x and y inputs. Can Excel do this, or do I need a plug-in or another software package?

View 12 Replies View Related

Multivariate Polynomial Regression In Excel?

Feb 8, 2010

I am trying to do multivariate polynomial regression in excel, trying to correlate data of the form y=f(x1,x2) with second order polynomials:

Y = c + a1*x1 + a2*x1^2 + a3^x1^3 + b1*x2 + b2*x2^2 + b3*x2^3

Using the following command, I have achieved this:

=LINEST(A2:A10,B2:B10^{1,2,3,0,0,0}*C2:C10^{0,0,0, 1,2,3},TRUE,TRUE)

theory Excel uses for this kind of regression?

View 4 Replies View Related

VBA Array Function For Polynomial Coefficients

Mar 9, 2007

I have a VBA function that calculates polynomial coefficients for a series of data pairs. One selects the range of cells that the coefficients are to be stored in, and enters the polynomial formula:

{POLFIT(Xa, Ya, N)}

Where Xa is the array of ordinate values, Ya is the array of data values, and N is the polynomial order to be fit.

It is obvious that one needs to select at least N+1 cells when the array funtion is typed in. But, it is easy to select too few cells.

I am looking for a way to test whether enough cells were selected for the range formula: The function declaration is

Function POLFIT(Xa, Ya, N As Integer) As Variant

Various means I have tried to count POLFIT do not return the correct value.

View 7 Replies View Related

Extracting Variables From Quartic Function (4th Degree Polynomial)

Apr 10, 2012

I have quite a number of 4th degree polynomials and attempting to extract the variables from every equation. I used the formula below (from another thread);

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))

but it only extracts the first variable.

4th degree polynomial function: f(x) = ax^4 + bx^3 + cx^2 + dx + e

Example of one of the equations: Y = -42.4276 + 319.989 X - 809.094 X^2 + 942.247 X ^3 - 397.587 X^4

I'll like to have the final result like below (Based on the Example equation above)

edcba-42.4276319.989-809.094942.247-397.587

View 4 Replies View Related

Formula For S Shape Curve?

Dec 13, 2013

I have a few dates and one Total value. I need to distribute this Total Value in such a way so I can get the same shape S curve (Date vs Value).

View 4 Replies View Related

Calculating The Radius Of A Curve

Feb 19, 2009

I am trying to find a formula to calculate the total size from a to b with the curve being in cluded in the total.

View 14 Replies View Related

Price Curve Formula

Apr 9, 2009

I'm trying Calculate Curved Price Rate by SF. phil.freestyle@gmail.com

View 5 Replies View Related

Apply A Curve To A Column

Jan 28, 2009

I'd like to apply a curve to a column of student exam grades and have the curved grades displayed in the adjacent column. Here is how I'd like to curve:

I like to make a linear function out of two coordinate pairs.

First Pair (x1,y1) = (Highest Exam Grade, 99) = (H,99)

Second Pair (x2,y2) = (Class Exam Average, Desired Exam Average) =(C,D)

Slope = (99-D)/(H-C) = m

Linear Function = Y-y = m(X-x) = Y-99=m(X-H)

Each student grade would = "Y" i.e. Y=m(X-H)+99

View 9 Replies View Related

Find Max In Xy Chart Curve Fit

Apr 13, 2006

How I can find a max in an xy chart curve fit?

View 9 Replies View Related

Create Growth Curve

Aug 28, 2007

Does anyone know of a statistical formula that would create a normal growth curve?

For example. Let's assume that I want to forcast growth from January through December. I know that in January I will have 100 units. In December, I will have 1,000 units. Is there a formula to predict the the units in Feb-Nov (using a normal growth curve)?

View 3 Replies View Related

Create S Curve Based On Dates

Apr 4, 2014

I am working on Project Planning, I need to create the Progress curve for our Project based on Weight Factor every month.

I need to create the Plan and actual curve based on the dates defined in particular activities. Once the activity progress is calculated every month based on cut off date then it will sum product with corresponding WF to get the progress achieved every month. Presently I am calculating the values every month and pasting the values in Chart data every month. I had created the S Curve and attached here.

Now my objective is to automatically calculate the values for the entire cut off date at one time (From Project Start to End). i.e I don't want to do any manual work in the Chart. I will update only the actual Dates in the file, then the curves need to get automatically updated.

1) I don't want to calculate the progress for individual activities, I would like to calculate the overall progress directly.

2) When I change the Cut off date, then the previous values needs to become static.

View 1 Replies View Related

Formula To Distribute A Value Along A Bell Curve.

Nov 19, 2008

I'm trying to find a formula that would spread out a value amongst multiple cells along a bell curve. Say I have $x,xxx. I want to spread that amount out in a certain proportion, regardless of how many cells I'm spreading it throughout.

Say I have a $10,000 project. I want to be able to spread it out like a bell curve in the cells I choose. For instance, if it was a five month project it would be distributed as

January - $1,000
February - $2,500
March - $3,000
April - $2,500
May - $1,000

If I added another month it would change to
January - $833.33
February - $1,666.66
March - $2,500.00
April - $2,500.00
May - $1,666.66
June - $833.33

I already thought of manually adding percentages to each cell, but the formula needs to be dynamic.

View 5 Replies View Related

Drawing Tangent Line On Curve?

Mar 8, 2014

Can we draw tangent to a curve in excel graphs..? If yes if i could have some demo....** this.. I am working on finding rate ** change at an instantaneous point and average rate ** change ie: secant line

View 1 Replies View Related

Excel Split A Curve And Fit Trendline Using VBA

Feb 2, 2014

I need to solve about 450 excel sheets for my project.

The spreadsheet has X and Y data and curve is plotted. I need to split the curves into 3 parts based on slope change.

I can do it manually but I need an excel Macro which does this work.

A sample is attached : Excel forum-Lab data.xlsx‎

View 14 Replies View Related

Given Points On Curve, Solve For Equation

Feb 25, 2009

Is it possible in excel to find the equation for a curve?

I have 6 known x,y data points on a curve. I need to find the equation for the curve.

View 4 Replies View Related

Use Excel To Plot Growth Curve?

Mar 15, 2012

I would like to use excel to plot a growth curve. I would like it to plot the points that correlate to my data without connecting them by a line. I would then like to give excel a formula and have it plot that line on the graph.

View 9 Replies View Related

Graph Bell Curve Chart

Jul 14, 2014

I'm trying to graph a bell curve chart using the following data

Hotels per state
Total

AK
4

NH
5

[Code] ......

As you can see I have states and the number of hotels per state what I need not for statistical purposes but more for "pretyness" is to sort this data in a way that what you get is a somewhat of a bell shape curve graph.

View 2 Replies View Related

Excel 2010 :: Creating Bell Curve?

Jul 29, 2014

I want to create a bell curve for my team's annual appraisal. There are 12 people in my team. The scale is 1-5, where normally rating range from 2.9 to 4.0.

View 1 Replies View Related

Display Mean And Confidence Intervals On Bell Curve

Nov 26, 2013

I've managed to figure out how to normalize my data and create several bell curves. However, these charts do not display the mean, std deviation, or confidence intervals. Any way to add these into my charts?

View 7 Replies View Related

Put Together A Cancel Curve On An Annual Membership Model

Nov 15, 2009

What would be the best way to put together a cancel curve on an annual membership model?

View 5 Replies View Related

Histogram Plot With Normal Curve Overlay

Aug 28, 2002

I would like to create a histogram from experimental data with the normal distribution curve overlaying the histogram.

View 7 Replies View Related

Generating Normal Curve From Statistical Values?

May 14, 2013

Is there a way to generate a normal curve from just the statistical values (mean, std dev, etc)? I used the NORMDIST function but have to manually do the x values and it generates what I would descirbe as a "sample" curve. I would like to make one based on user input.

View 5 Replies View Related

Excel 2013 :: Plotting Gaussian Curve Over Bar Chart

Apr 14, 2014

I'm trying to overlay a Gaussian distribution on some data. The data is a frequency of particle size diameters, ranging from 200-1000 um (see attached). I'm using Excel 2013.

View 1 Replies View Related

Creating Curve With Multiple Values Already In Excel Spreadsheet?

Jul 11, 2013

Here's the spreadsheet I am talking about : [URL]

So as you can see each row = one day divided in 2 cycles of 12 hours : every 12 hours the cat gets a new injection of insulin (the columns "U" being the number of units of insulin the cat receives)

Now I want to turn this sheet into two different curves that would look like these : [URL]

As you can see the first graph shows data based on days (x) and insulin dose (y), while the second curves shows data based on days (x), and blood glucose values (y). This is basically the only way we could clearly see how the cat's disease is evolving over a long period of time.

If he had just started to take all these values from his cat I guess it would be simple to do, but since he already has a lot of data entered in his spreadsheet, I was wondering if I could use this data (and not write each number in a new document), to make things a little more simple and to avoid mistakes.

View 9 Replies View Related

Calculate The Value In The NPV

Aug 29, 2008

I have two columns in a payment schedule (which adjusts according to certain user inputs) that I need to use in my NPV calculation.

The first column is the Total Payment and the second is Inducements.

Therefore each value in the NPV calc. needs to be the sum of a given period's payment and inducement (but i don't want/have a separate column which calculates the sums). The number of periods adjusts with the users input of Term. There also may be periods where there is a payment but no inducement.

View 14 Replies View Related

Calculate Down To Last Row

Dec 22, 2011

The formula below calculates between rows 2 and 109. How can I change it to calculate between row 2 and the last used row in the sheet.

Code:
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=(RC[-1]-MIN(R1C[-1]:R109C[-1]))/(MAX(R1C[-1]:R109C[-1])-MIN(R1C[-1]:R109C[-1]))"

View 2 Replies View Related

Calculate IRR & NPV

Jan 10, 2007

i got a problem to calculate IRR and NPV for my company cash flow. i so confuse how to calculate cos the initial investment (expense) is pay in installment by yearly basis. i hope anyone can help me to solve this problem.

I m not sure whether what i'm doing is correct or not. thanx

http://spreadsheets.google.com/ccc?k...Hd2JSrIj7H-Pew

View 9 Replies View Related

Calculate How Many Of The Same Value In A Row?

Feb 17, 2007

If I have this serie of values in the A column:

34
33
33
33
30
29
26
26
20
19
17
17

And want this results in the B column:

1
3
3
3
1
1
2
2
1
1
2
2

Those numbers will indicate how many of the same are in a row.

What's the easiest way to accomplish this?

View 7 Replies View Related

How To Calculate Age

May 13, 2009

to calculate the age from the format date of birth shown below.

SQL Data S1Date of Birth2Jun 9 1947 12:00AM3Jan 1 1957 12:00AM4Jan 1 1958 12:00AM5Jan 1 1956 12:00AM6Jun 4 1951 12:00AM7Dec 10 1963 12:00AM8Jun 17 1958 12:00AM Excel tables to the web >> Excel Jeanie HTML 4

View 9 Replies View Related

Sub Calculate

Feb 24, 2010

I am trying to call a sub calculate but I keep getting errors, is calculate a reserved sub name?

View 9 Replies View Related

ADVERTISEMENT