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 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
Find Max In Xy Chart Curve Fit
How I can find a max in an xy chart curve fit?

View Replies!   View Related
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 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
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
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
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
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
Fit To Page
I have the following VB code, which doesnt do the job. I want to fit my report to one landscape page. if i manaully click "fit to Page" its reduces the size to 49%. I tried outting this in the code, but seems to ignore it,

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.LeftMargin = Application.CentimetersToPoints(0.7)
.RightMargin = Application.CentimetersToPoints(0.4)
.TopMargin = Application.CentimetersToPoints(1.4)
.BottomMargin = Application.CentimetersToPoints(0.8)
.HeaderMargin = Application.CentimetersToPoints(0.5)
.FooterMargin = Application.CentimetersToPoints(0.5)
.Zoom = 49
.PrintErrors = 0
End With

View Replies!   View Related
Multiple Best Fit Line
I have excel 2007, i am doing scientific graphing via an X Y scatter plot graph. Is it possible someone could give me step by step instructions on how to fit multiple best fit lines. In particular two lines one for the upper portion data and top portion of curve. The second on the flatter bottom portion ultimately intersecting in the left inner portion of graph. the graph plots appear as a rounded L.

View Replies!   View Related
Fit Image To Cell
I'm looking to see if there is a feature similar to using F2 to paste text into a cell and having the cell automatically expand to the needed row height. I just need it for images. Is there a feature that will allow me to paste a picture/image into a cell and have the cell automatically expand to the height &/or width needed to accommodate the size of the image?

View Replies!   View Related
Best Fit Listbox Columns
best fit listbox columns. i'm filling a listbox with a range name (works great...)

ListBox1.RowSource = "HistoryCust_Item!History_CurrentMonthLast"

but the columns don't adjust ("best fit)...i would like to add that feature

View Replies!   View Related
Fit One Page When Printing
I need to create a macro code to adjust the width of the columns so it fit in one page for printing. So If it only has 3 columns, the widht will be wider but if it has 200 columns then the width will be much smaller.

View Replies!   View Related
More Complex Fit Function Than LOGEST
The function LOGEST fits to the equation y=b*m^x.
Does anyone know if more complex functions can be found in Excel?
Ideally I would like to fit to y=b*m^(x-a) + c. If anyone has come across a method, or some code, which allows this.

View Replies!   View Related
Print Selection Fit 1 Sheet.
i have a buttion on a whole heap of worksheets that is linked to this sub. I want it to print the range on one sheet of paper. though it will print over 4 sheets.

View Replies!   View Related
Scatter Plot With Line Best Fit
I got the following table and it generates a random number either 1 or 0 (win or loss)

trade win/loss Account value
0 10,000
rand() 1 0 9,800
rand() 2 1 11,000
rand() 3 1 12,000
rand() 4 0 11,500

Each time I press F9, the Account value changes because the win/loss is generated randomly. Assuming, I press F9 100 times, how do I create a scatter plot chart with a best line fit for all the account values. I know how to create the scatter plot but how do I save all the values everytime I press F9.

View Replies!   View Related
Resize Print Range To Fit A 4
I am trying to print a range that falls just outside of a normal A4 size. Is there a way of scaling this range to fit the page. Withough changing the column sizes before printing(each column is diffrent size). Or will i need to load the column sizes into a array and resize for the print then back to original size from array.

View Replies!   View Related
Automatically Fit TextBox To Text
I have a textbox showing 7 lines of words.

When it is shown in my computer, everything looks fine.

When I use another computer, the lines in the textbox could be shown completely since the height of the textbox is not enough to show the whole text. I guess it is because the resolution of the screen is different.

How can I let the text be shown completely in a textbox even on different computers?

View Replies!   View Related
Print To Fit Shrinks Too Small
I created an Excel timesheet, no VBA, which 65 people are using. 64 without a problem but the 65th cannot print the sheet as others can. The last cell on the single sheet is AB61. For the 64 who can print it by selecting the fit to print on one page the percent is 71. For the one person with problems the percent goes down to 10. Even when she opens the read only version on the network it becomes 10%. I checked the magins and everything else I can think of. Short of reinstalling Excel (2000 with Windows 2000)

View Replies!   View Related
Zoom Worksheets To Fit Used Range
to apply a zoom to Fit Selection on all worksheets upon file opening? I've read many posts on this and understand how to apply this via worksheet activation, but haven't discovered how to make this to happen to all worksheets when the file is opened.

Zooming the selection needs to happen in the workbook module due to additional sheets being added at random and because it will facilitate the flow of the meeting so that we don't have to wait for the user to select and zoom.

Private Sub Workbook_Open()

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets

LCol = Cells. Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
Range(Cells(1, 1), Cells(1, LCol)).EntireColumn.Select

' Need code help here to apply to all worksheets
ActiveWindow.Zoom = True ' this works for the worksheet activation event only

Next ws

End Sub

View Replies!   View Related
Vba To Get Best Fit Equation Into Cells
i am plotting graphs which i need the equation of the line from. i have been manually typing the x^3, X^2, X^1 and X^0 values into 4 cells which is then used elsewhere, but wanted to know if its possible to get some code to do that for me?

View Replies!   View Related
Count Cells That Fit Criteria
Is there a way to count cells only if they fit a certain criteria in another cell? Example: I want to count the number of moving violations in column F. But, I only want to count this moving violation if it is out of the Monaca terminal, which is defined in column C.

View Replies!   View Related
Delete Rows That Fit Criteria
I have an excel 2007 file, which is quite big: ~10mb. There are situated plenty of columns with coordinates. I would like to transcribe it, modify those data, but it will take me ages. I'm not acquainted with macros, but I heard that people can do with them everything using computers' abilities. I'll abridge my whole problem.

I give you a sample of that file, so please open it. In the file you can see two 'columns'. Every column has two other columns called x and y. As you are thinking now these two columns are containing arguments (in x column) and their values (in column y). Every third column is empty. In attached file you see, that arguments are repeating and values are increasing by one. Sometimes there is an argument without repeats, and sometimes there are only two repeating arguments.

What I wanted to do is delete those repeating arguments leaving arguments with the highest and the lowest value. Example: Please look at range A230:B232. There are three repeating arguments and for this case I should delete row 231 from columns A&B and then move cells up to join separated columns. There are also not repeating arguments, which I need to double. If there will be only two repeating arguments I would like leave them. In the end I should have a clear view for all those coordinates.

View Replies!   View Related
Delete Multiple Columns If They Fit Different Criteria
Macro below would be to only go down to the cell = to the bottom-most cell with data in column A.

These columns below are all in row A (the title row)
If column named "paper" says "white" OR "grey" AND
column named "pizza" says "mushroom" AND
column named "size" says "large" AND
column named "flavor" says "butter pecan"..
(again, only going down to the same matching cell with any data in column a)

Then delete these columns.

View Replies!   View Related
Fit And Convert Data To Table Template
Need to fit my raw data (Raw Data Sheet) to fit my table template.

Need to convert my daily data to weekly to fit to my table template.

Please see attached file for reference.

View Replies!   View Related
3D Linear Algebra. Translate To Best Fit Plane
I have several hundred points in cartesian x,y,z coordinates. I used an Excel add-in program to calculate the equation of best fit to the points. The equation is:

z=A*x+B*y+C
where (for this case):
A= 2.29091
B= -1.77338
C= -192.494

My objective is to define the several hundred x,y,z points in cartesian coordinates about a new cartesian coordinate system whose origin is the centroid of the data points and whose z plane (ie z=0) is equal to the best fit plane defined above (when referenced from the original coordinate system).

Using Pro/e (a cad program), I was able to create the new coordinate system using the x, y, and z intercepts from the best fit plane, and then was able to redefine the points about the new coordinate system. After the points were redefined in Pro/e, I was able to export the points back to Excel; however, I would prefer to only use Excel / VBA because I prefer the calculations be transparent for optimal accuracy and validation.

View Replies!   View Related
Auto Fit Row On Sheet Activation
I have a macro that returns a lot of data and I need it to automatically resize the row in which the data is being returned. Here is an example of what I mean:

Function MyMacro()
ReturnString = "help" & chr(10) & "me"
MyMacro = ReturnString

' resize the row
' need to define "Location", based on the location of the cell in which the data is being returned
Rows(Location).EntireRow.AutoFit
End Function

Even if I specifically hardcode the location of the row to be resized in the macro, it still doesn't work. The cell has "Wrap Text" turned on.

View Replies!   View Related
Adjust Cell Height To Fit Text
way to get Excel to automatically expand the cell height when the text becomes to long to display. Is there something I am missing that will accomplish this, or do I need to write some VBA that will make the cell larger once the character limit that will fit on one row is exceeded?

View Replies!   View Related
Dynamic Print Range To Fit 1 Page
I have a print range set up that is based on a named range that is an offset formula. The problem is that I would like to set it up as portrait instead of landscape, but if I do that the area to the right doesn't all fit on the page. The print area will always be a certain width, just different hights. I just want to be able to have the print range adjust downwards and still fit the whole area on one page wide.

View Replies!   View Related
Automatically Auto-Fit Visible Columns Only
In the code below, I found out how to make the column widths automatically adjust on a worksheet based on the text entered. This code works great but it also reveals the hidden columns on the worksheet. Can i prevent it from doing that?

Private Sub Worksheet_Change(ByVal Target As Range)
With Me.Cells
.Columns.AutoFit
End With
End Sub

Found here: Automatically Adjust Row Height & Column Width

View Replies!   View Related
Paste Data To Fit Range Of Cells
I am working on a Budgeting worksheet. I want to use UserForms as one method of Data entry. I would like to provide users with an input box that would prompt for a start and end date, then the category they are forecasting, and finally the quantity and price by day of the week. Is it possible to paste a 7 day set of data into a 10 (or more or less) day range? For example if the dates entered were for Jan 1 through Jan 10 this would repeat 3 of the days entered. I have attached a sample, not useing UserForm, to better illistrate what I am trying to achieve.

View Replies!   View Related
How To Size App. Window To Fit Cells? (not Zoom)
This "smells" like a simple question, but I've had no luck finding a way to [programmatically] adjust the App.Window to fit around some cells.

I've found "Application.Goto", also the Application.Width/Height setting. The App.Width setting is pixel based, so if there's an easy way to know a cell's pixel-location, that'll work too!

View Replies!   View Related
Formula For Calculating Largest Square To Fit In A Quadrilateral
I know this ain't going to be easy if at all do-able, but would anyone know if there can be a formula/s made to calculate the largest size "square" shape (e.g. all 4 corners at 90 deg.) that would fit in to a known size Quadrilateral?

Value A,B,C,D and Z are all known values as are the diagonal lengths between corners

I failed dismally in Geometry at High school, but how was I to know that 35 yrs later I would need to know this stuff!!

Please see diagram for an idea of what I'm trying to achieve.


View Replies!   View Related
‘The PivotTable Report Will Not Fit On The Sheet. Do You Want To Show As Much As Poss
Since I'm making a PivotTable with my macro, when it runs I always get this...

‘The PivotTable report will not fit on the sheet. Do you want to show as much as possible?’

I always choose yes, because in the end the user isn't going to use all of the data, they will be narrowing it down. Anyway, this comes up a few times througout the macro. Is there something I can put in the code that, if pop up box, always choose yes?

View Replies!   View Related
Auto-fit Rows & Column For Printing
I've got a report that is filled out every day and submitted to the VIP's within the company.

They normally just open the file, press print and read the hard-copy.

Quite often the text of the column is larger than the cell size and the text is cut off.

View Replies!   View Related
Resizing A Form To Fit A Variable Number Of Checkboxes
create a form that will look at the spreadsheet and figure out which of the 32 possible checkboxes will be used and resize accordingly. I can make it so that only the checkboxes I want are visible using the .visible property, but for the sake of aesthetics I'm wondering if there is a way to disable the checkboxes I don't want and to resize the form to fit the checkboxes I do want. I just don't want a form that looks like it has checkboxes put on it at random spots.

View Replies!   View Related
Cell With Formula Won't Expand To Fit Text
I have a cell set up with a formula to copy the content from another cell. However, the cell won't expand to fit the text. If you delete the formula and just type in the cell, it does expand. See attachment.

View Replies!   View Related
Sumproduct #Num!,#N/A (select Specific The Sum Of Various Lines Of Data That Fit Within 3 Parameters)
I am having a problem with a sumproduct strand. I've written 4 different ways to no avail. What I'm trying to accomplish is to select specific the sum of various lines of data that fit within 3 parameters

1.) =SUMPRODUCT((Sheet1!$H:$H="XYZ"),(Sheet1!$G2:$G136="Withdrawal"),(Sheet1!$I2:$I136="113183"),Sheet1! $K2:$K136)
Response: #NUM!

2.)=+SUMPRODUCT(--(Sheet1!$H2:$H136='Sheet2'!$A$5),--(Sheet1!$G2:$G136="Withdrawal"),--(Sheet1!$I2:$I136='Sheet2'!$B$5),Sheet1!$K:$K)
Response: #Num!.................

View Replies!   View Related
Create UserForm TextBox At Run-Time & Auto Size To Fit Text
I have a user form on which I need to create a textbox every time the user clicks a button. There are too many to create them all in advance and make them visible when needed. The sample attached, from this forum, is good, except, the numbers in the text box just scroll along and I need to see all the text typed into my textboxes. I would like the textboxes to be multiline, wordwrap and have them resize according to the amount of text.

View Replies!   View Related
I Want To Be Able To Take The Solution To The Formula And "fit It" Into Another Formula.
=IF(AC4509="","",IF(W4509=1,ROUND(AR5+(0*(9.693/C4509)),1),ROUND(AR5+(X4509*(9.693/C4509)),1)))

If the value of W4509 is equal to 1, the solution would be whatever is dictated, otherwise, it uses the alternative formula to achieve the answer.

My question: I want to be able to take the solution to the formula and "fit it" into another formula.

Example: ((the "If W4509=1" formula)-88)*(C4509*.09693)+100-0) and ((2nd formula)-88)*(C4509*.09693)+100-X4509)

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
Copyright © 2005-08 www.BigResource.com, All rights reserved