Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










How Do I Calculate A Polynomial Curve Fit


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.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Calculate Area Under The Curve
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 Replies!   View Related
Calculate Best Fit Equation Of Curve
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 Replies!   View Related
Calculate Maximum Point On Curve From Xy Scatter Graph
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 Replies!   View Related
Polynomial Function
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 Replies!   View Related
3rd Degree Polynomial
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 Replies!   View Related
Polynomial Equations
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 Replies!   View Related
More Digits From Polynomial Regressions?
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 Replies!   View Related
Solving Polynomial Equations
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 Replies!   View Related
VBA Array Function For Polynomial Coefficients
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 Replies!   View Related
Calculating The Radius Of A Curve
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 Replies!   View Related
Find Max In Xy Chart Curve Fit
How I can find a max in an xy chart curve fit?

View Replies!   View Related
Apply A Curve To A Column
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 Replies!   View Related
Price Curve Formula
I'm trying Calculate Curved Price Rate by SF. phil.freestyle@gmail.com

View Replies!   View Related
Create Growth Curve
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 Replies!   View Related
Given Points On Curve, Solve For Equation
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 Replies!   View Related
Formula To Distribute A Value Along A Bell Curve.
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 Replies!   View Related
Put Together A Cancel Curve On An Annual Membership Model
What would be the best way to put together a cancel curve on an annual membership model?

View Replies!   View Related
Calculate IRR & NPV
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 Replies!   View Related
Sub Calculate
I am trying to call a sub calculate but I keep getting errors, is calculate a reserved sub name?

View Replies!   View Related
How To Calculate Age
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 Replies!   View Related
Calculate The Value In The NPV
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 Replies!   View Related
Calculate How Many Of The Same Value In A Row?
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 Replies!   View Related
Calculate Net Pay After Tax
building a spreadsheet. I have the total price in cell f6. In cell C6 I need price with no taxes. D6 should be the pst of 8% and E6 should be the gst of 5%.

View Replies!   View Related
How To Calculate Combinations ?
I want to see how much combinations are possible when i got 6 numbers..

-- got this numbers 1--2--3--4--5--6
-- want to calculate how much combinations of ( 2 ) numbers possible
-- want to calculate how much of ( 3 ) numbers possible
-- want to calculate how much of ( 4 ) numbers possible
-- want to calculate how much of ( 5) numbers possible

-- never 2 same numbers together (2-2) or (2-2-3) or (2-2-3-4) may not be in list

-- How can i make the result visible in kind of list ?


I would be nice if somebody knows a good solution..


View Replies!   View Related
Formula To Calculate Age
i am trying to build a formula to calculate a persons age.

I have tried using the DATEIF function but i keep getting a N/A


View Replies!   View Related
Calculate Variances
Just wanted to find out if the formulas in the attached apreadsheet are correct. Formulas from E6 to E10.

Also, if you multiply the "new daily target" with "working days" should'nt this give you the "remaining" figure? Currently it's not doing this

View Replies!   View Related
VBA Calculate Pause
Hi everyone I am having a little trouble with a sheet i'm currently working on. Having exhausted what i believe to be every avenue trying to do this without VBA, i've finally had to sucomb to it as i can't see an alternative and i'm no VB expert by any means! This is what i have so far:

View Replies!   View Related
Calculate Attendance
I got struck in preparing absenteesm report from 22nd Sep 08 to 21st Oct 08. I've to put the dates on which a employee was absent ...

View Replies!   View Related
Calculate Totals
I have a spread sheet that is used to review calls placed by a call center.

Column A has the extensions of the phones, and Column E has the type of call (Outgoing or Incoming). Each line is a new phone call.

We have about 8 extensions, but the worksheet could have a couple thousand calls. So, Column A could have extension 1401 from rows 1-100 as extension 1401 made 100 calls. I'm looking for a formula or macro that will summarize how many outgoing and incoming calls extension 1401 had. Thoughts?

My initial thought was something like this:

=SUMPRODUCT(--(A2:A5000=H2),(E2:E5000))

In this formula, I would type in the extension of H2 and it would scan Column A and add up the values in Column E. The only problem is, that Column H doesn't contain a numerical value. It only has "Incoming" and "Outgoing" (minus the quotes), so this doesn't work.

View Replies!   View Related
Calculate Ratio
I'm trying to create a quote... basically I need to find out about calculating ratios.

We have 47 students going on a trip to different venues, we offer 1:10 teachers (i.e. one teacher free, for every ten students going), however some venues offer a different ratio e.g. 1:5 teachers free of charge.

I need to somehow create a column where it will calculate the amount charged for the different ratios the venues offered.

View Replies!   View Related
Calculate The Variance
i have a dynamic list of numbers....currently 10 numbers in the list.

how can i calculate the variance?

i have the upper limit (=MIN(1,(mean+half width))
i have the lower limit (=MAX(0,mean-half width)
i have the mean (avg of all numbers)
i have the t value (TINV(alpha, (n-1)))
i have the half width (t value * SQRT of Var/N)

i just don't know how to get the VAR/N


View Replies!   View Related
Function To Calculate IRR
My code below comes up with #VALUE! error msg


Function PIRR(NumPer As Single, StartVal As Double, EachVal As Double)
Dim Values() As Double
Values(0) = StartVal

For i = 1 To NumPer
Values(i) = EachVal
Next

PIRR = Application.WorksheetFunction.IRR(Values())

End Function

View Replies!   View Related
Calculate If There Is Anything In The Cell
Im wanting to do a sum calculate if there is anything in the cell.
Ive tried using the 'count' and 'count if' but both return values of zero.

There are words in the cell, rather than numbers.

I want it to return the value of cells with data in them, and exclude those with nothing ("") in them.


View Replies!   View Related
Calculate Button
I am trying to create a Radio button in a cell that calculates the entire workbook rather than pressing F9.

View Replies!   View Related
Calculate Age Down To Milliseconds
where i can get this download for Excel?


My husband sent me this program in Excel where you type in your birthdate and not only does it give you your age to the millisecond, but it also gives you a list of traits the person has. For example, I was born on November 20, 1987 and this popped up:

Age in years 19.61
Age in months 235
Age in days 7157
Age in hours 171760
Age in minutes 10305599
Age in seconds 618335921
Age in Milli seconds 61833592095
Age in weeks 50097
You born on Friday

View Replies!   View Related
Calculate Hours
I am designing a simple time card. Column D=time in, Column E=time out. My formula in column F to calculate total hours is =TEXT(E2-D2,"[h]hrs"). The result is not correct. Example: In at 9:30 out at 5:00 and the calculated total is -4 hours.

View Replies!   View Related
Calculate The Probabilities
How to calculate probabilities... say that I am buying pop bottles that say "1 in 6 wins" or drawing buttons from hats where 5 of them are numbered 1-5 and the other one is the winner. (Note that you would have to keep putting the buttons back after drawing so there is always 1-5 and a winner whenever you draw)

The possible combininations are easy to figure out... just do 6 to the power of how ever many bottles you buy or how many times you draw a button. So obviously if you draw once, there is 6^1 possible... if you draw twice there are 6^2 (or 36) possible combinations.

To figure out how many winning possibilities (win atleast once) there are it's not too hard at this level... you can just list the possibilities:

First - Second
1-1
1-2
1-3
1-4
1-5
1-W (1 winning combination)
2-1
2-2
2-3
2-4.................................

View Replies!   View Related
Calculate Overtime
I have a time sheet for my employee's that I need to calculate their overtime in 1.5x and 2x rates.

Their overtime totals are done in individule columns from D33 to S33. The first 4 hours per day are charged at 1.5x and anything over that is 2x. I want to show the 1.5x in one box and the 2x in another. I do believe that I need two formulas one in each of the boxes where the final totals would go.

Here's an example, in columns D37 to D41 the employee has worked 12, 14, 9, 16 and 14.5 hours. so that's 17 hours @ 1.5x and 8.5 @ 2x.

View Replies!   View Related
Add 2 Row After Each 9 Lines And Calculate A
I need a macro that add's after each 9 lines 2 new rows. The data I've it's not the same every month, one month I can have 27 lines the next one I can have 90 lines for exemple.

I need also to calculate in one of those added rows this "=B8+B4-B6". This calculation is to be apllied in each block of nines lines.

View Replies!   View Related
Calculate The Percentage
I have attached a file with both a sample section of data on the first sheet and the outcome I would like on the second sheet... I would do it all manually but there are over 200,000 rows in the actual file.

The macro needs to calculate the percentage of sale for each reference number within each part number and move down to the next part number and do the same until it reaches the bottom.


View Replies!   View Related
Formula To Calculate How Much Tax To Pay
Working from cell D19 I am trying to find a formula to calculate how much tax to pay.
I have a TAXABLE INCOME figure (in cell D18) , from this figure

The FIRST 1520 should be multiplied by 0.1 (in cell C19,)
The next 26880 should be multiplied by 0.22 (in cell C20, the answer to be put into D20)
Any remaining from the Taxable Income figure (put into cell C21), should be multiplied by 0.4 (the answer placed in cell D21)


View Replies!   View Related
Formula - How To Calculate For The 35 Min At Say 15.24
i just started with a company Royal Mail and they have some very strange timings for starting and finishing and i need a formula to help me work my money out and my hours to the precise min.

for example i have a shift comming up this week and my start time is down for 22:45 and my calculated finishing time is 07:23. i need a formula that will show on a spread sheet my exact time of work.

the other time i had was 22:35 to 06:35...it all seems prity simple to alot of people but this is my other problem. if i work from 06 - 18:00 i get one rate and if i work from 18 - 06:00 i get another rate.

so i need to know how to calculate for the 35 min at say 15.24 ph.

View Replies!   View Related
Calculate The Hours
How to calculate the Hours
Check the attached file

View Replies!   View Related
How To Calculate Time
WHen I was a beginner at Excel, I came up with the following formula to calculate the time difference between two "time" values. Since I was too lazy to add a ":" between my hour and minute, I had decided to simply enter the start (column B) and end time (column C) as a military time (e.g., 0100, 1230, 1500, 1930, 2300, 2359). The following formula would be in Column D.

=IF(C7

View Replies!   View Related
Calculate Retrospectively
I need to create a formula to work backwards from a closing balance to establish an inventory value on First In, First Out principles. The attached spreadsheet probably explains it better.

View Replies!   View Related
Calculate Age In Months
I need to be able to calculate age in months, and round whatever remainder to the middle of the month (.5). I am calculating z-scores and percentiles for in a childhood obesity project. The DoB and Date of measurements must be converted to read anything from 142.0 to 142.9 as 142.5.

This value will be used to look up the the appropriate LMS (Box Cox, Median, and Standard Deviation) in another excel table. I have to be able to compute age in months so 142.5, 143.5, 144.5, 144.5 etc. It is just not rounding off to the nearest half month.

View Replies!   View Related
How To Calculate Date
how to calculate the date for one year. If i have 24-Jun-08 (A1) i need to calculate 24-Jun-09. If i do =A1+365 it gives me an error (the little green thing in the corner of the box) is there a way to do this without the error showing up.

View Replies!   View Related
Calculate Total
I need a formula that will calculate the total of the items in column "W" when the row directly below it contains a "1" in column "V". It will need to calculate the totals from that row all the way up to the next row that contains a "1" in column V.

Example:

In X14 (where I will put the formula) it would total W9 through W14. However the formula still needs to be able to calculate the total if there are more or less cells to sum. So that the same formula could calculate that in X8 it will total W5 through W8.

View Replies!   View Related
Calculate The Date..
Is there a formula for calculating date for every 2nd thursday of the month excluding holidays?

View Replies!   View Related
Formula To Calculate A %
I use this formula to calculate a %, but naturally when there is a 0 excel returns the #DIV/0! error. Can someone fix my formula so the error gets replaced with a 0
in the formula the 0 would be in cell W2 =IF(N2="yes",BN2/W2,0.5).

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved