Curve-fitting

I have a graph that I'd like to fit a curve to, but the 'trend-line' 
function doesn't seem to get close.

Does anyone know of a spreadsheet that'll calculate a curve equation 
from a given set of data?
-- 
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)
0
vidcapper (121)
3/14/2009 8:03:34 AM
excel 39879 articles. 2 followers. Follow

6 Replies
774 Views

Similar Articles

[PageSpeed] 32

Start here:

http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/


-- 
Gary''s Student - gsnu2007L


"Paul Hyett" wrote:

> I have a graph that I'd like to fit a curve to, but the 'trend-line' 
> function doesn't seem to get close.
> 
> Does anyone know of a spreadsheet that'll calculate a curve equation 
> from a given set of data?
> -- 
> Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)
> 
0
GarysStudent (1572)
3/14/2009 10:14:01 AM
On Sat, 14 Mar 2009 at 03:14:01, Gary''s Student 
<GarysStudent@discussions.microsoft.com> wrote in microsoft.public.excel 
:

>Start here:
>
>http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/
>
Thanks. I'll have a look at Log, Power & Exponential - though in the 
past none have provided that close a fit to my data.
-- 
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)
0
vidcapper (121)
3/14/2009 5:33:50 PM
Paul Hyett  -

Your question is much too general.

For your specific situation, you are more likely to get an appropriate reply 
if you (a) provide the data, (b) describe the pattern of the "average 
relationship," and/or (c) explain the underlying physical phenomenon that is 
being modeled.

If the trendline forms are not appropriate for your data, there are other 
functional forms that can be fitted using Solver to search for the parameter 
values.

-  Mike

http://www.MikeMiddleton.com



"Paul Hyett" <vidcapper@invalid83261.co.uk> wrote in message 
news:QixNCVHHQ2uJFwP9@blueyonder.co.uk...
>I have a graph that I'd like to fit a curve to, but the 'trend-line' 
>function doesn't seem to get close.
>
> Does anyone know of a spreadsheet that'll calculate a curve equation from 
> a given set of data?
> -- 
> Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me) 


0
mike5208 (300)
3/14/2009 6:33:51 PM
On Sat, 14 Mar 2009 at 11:33:51, Mike Middleton <mike@mikemiddleton.com> 
wrote in microsoft.public.excel :

>Paul Hyett  -
>
>Your question is much too general.
>
>For your specific situation, you are more likely to get an appropriate reply
>if you (a) provide the data, (b) describe the pattern of the "average
>relationship," and/or (c) explain the underlying physical phenomenon that is
>being modeled.
>
>If the trendline forms are not appropriate for your data, there are other
>functional forms that can be fitted using Solver to search for the parameter
>values.

Solver - now why didn't I think of that?

Thanks!
-- 
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)
0
vidcapper (121)
3/15/2009 7:17:37 AM
Try the "smoothed-line" charting option - this looks like the shape a 
flexible strip passing through your points could take up. A link to the basic 
formula is here:
http://www.mvps.org/directx/articles/catmull/

If you have data in A2:B10 and an x-value in E2, enter this formula for the 
y-value:

=SUM((1+1/IRR(MMULT({0,0,2,0;0,1,0,-1;-1,4,-5,2;1,-3,3,-1},
OFFSET($A$2:$A$10,MATCH(E2,$A$2:$A$10)-2,0,4,1)-E2
)))^-{0;1;2;3}*MMULT({0,2,0,0;-1,0,1,0;2,-5,4,-1;-1,3,-3,1},
OFFSET($B$2:$B$10,MATCH(E2,$A$2:$A$10)-2,0,4,1)))/2

To get values in the first and last data intervals, extend your data range 
by one row at each end - you can do this simply by selecting A2:B3 and 
dragging the fill handle up to row 1 and also selecting A9:B10 and dragging 
down to row 11. 

(Adjust the ranges to suit your data and fill down for more data values.)


"Paul Hyett" wrote:

> I have a graph that I'd like to fit a curve to, but the 'trend-line' 
> function doesn't seem to get close.
> 
> Does anyone know of a spreadsheet that'll calculate a curve equation 
> from a given set of data?
> -- 
> Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)
> 
0
LoriMiller (18)
3/16/2009 12:59:01 AM
On Sun, 15 Mar 2009 at 17:59:01, Lori Miller 
<LoriMiller@discussions.microsoft.com> wrote in microsoft.public.excel :

>Try the "smoothed-line" charting option - this looks like the shape a
>flexible strip passing through your points could take up. A link to the basic
>formula is here:
>http://www.mvps.org/directx/articles/catmull/
>
>If you have data in A2:B10 and an x-value in E2, enter this formula for the
>y-value:
>
>=SUM((1+1/IRR(MMULT({0,0,2,0;0,1,0,-1;-1,4,-5,2;1,-3,3,-1},
>OFFSET($A$2:$A$10,MATCH(E2,$A$2:$A$10)-2,0,4,1)-E2
>)))^-{0;1;2;3}*MMULT({0,2,0,0;-1,0,1,0;2,-5,4,-1;-1,3,-3,1},
>OFFSET($B$2:$B$10,MATCH(E2,$A$2:$A$10)-2,0,4,1)))/2

Wow, that's quite a formula. :)
>
>To get values in the first and last data intervals, extend your data range
>by one row at each end - you can do this simply by selecting A2:B3 and
>dragging the fill handle up to row 1 and also selecting A9:B10 and dragging
>down to row 11.
>
>(Adjust the ranges to suit your data and fill down for more data values.)

I'll experiment - thanks.
-- 
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)
0
vidcapper (121)
3/16/2009 7:49:53 AM
Reply:

Similar Artilces:

Calculation of Area Under a Continuos curve......Scatter Curve.
The variables are part of a scatter diagram, i.e. the variables plotted on X axis do not have any defined relationship with the Variables of Y axis...... Hi, I am working on calculating area under a Continuous Curve.. I have data poitnts...On X axis i have % of Cumulative numbers(Variable X) and on Y axis i have % of Cumulative numbers(Variable Y). If i plot a curve by joinig thses points i get a smooth curve. If i have to calculate the exact area under the curve using Integration Function in Excel Sheet how should i proceed. Data points are : Issuers Defaulters Cum Issuers % ...

Curve radius
I use Visio 2000 (SP2) and have Accident Reporting and Crime Scene add-ins to create scale diagrams of traffic accident scenes. Is there any way to automate curve length, chord and radius? Any tip that allows the use of the "Size & Position" window would be most helpful, but any tip at all will be appreciated! I have used Visio since 2000 came out and am comfortable with it, i.e., I can use it to automate other shapes, but I dont see any where to automate curves!!! Thank you in advance! It can be automated by creating some VBA code. Most highway and residential curves are comp...

calibration curve
I am setting up a chemistry calibration curve using data below OD reading Concentration ..020 0 ..045 10 ..245 40 etc Plotting in XY scatter chart and get trendline using polynomial function. How can I determine the concentration of an unkown solution ? I want to use trendline data to do this. Put the Conc data in column A and the OD data in B You can have headers ("Concentration", "OD" ) in row 1 Let's say the last row is 10 Select A1:B10, click the chart tool, select XY chart, use the first subtype (ma...

Excel 2000: Can I display more than one curve in a single chart
Hi All, Can I display more than one curve (i.e., more than a single set of data points) in a single chart? I am using Excel 2000. tia Avraham Makeler. Sure. Select more than 2 columns when you create the chart. Alternately select the chart and follow the prompts at Chart|Add Data Jerry Avraham Makeler wrote: > Hi All, > > Can I display more than one curve (i.e., more than a single set of data > points) in a single chart? > > I am using Excel 2000. > > tia > > Avraham Makeler. ...

I need a bezier curve the example? Who may provide? Extremely thank!
I need a bezier curve the example? Who may provide? Extremely thank! ...

Auto Fit row height and merged cells
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I use excel to do complex formatted design spreadsheets for our business. Wrapped, merged cell refuse to autfit row height and it is driving me nuts. <br><br>If I open a new workbook and set up a cell to wrap and auto fit. All is right with the world. When I add characters to the cell and hit enter the row height behaves as expected. When I remove characters in the cell it contracts to the appropriate row height. <br><br>However, if I simply merge this same cell with its neighbor to...

curve fitting a charging capacitor type curve
Hi all, Newbie to this forum, but need some excel help. I have collected a bunch of data from an object as it heats up. The curve is very similar to a capacitor charging current curve. In my case, manually trying to approximate this curve, I come up with a formula of y= (rise)*(1-exp(-time))+ambient. Is there a way in excel to fit a curve like this or do I need an aftermarket solution? I need something that will use the data points and perform calculations to determine the curve. Thanks, Sean -- mcgradys ------------------------------------------------------------------------ mcgradys&#...

Calculation of Area Under a Continuos curve......
Hi, I am working on calculating area under a Continuous Curve.. I have data points...On X axis i have % of Cumulative numbers(Variable X) and on Y axis i have % of Cumulative numbers(Variable Y). If i plot a curve by joinig thses points i get a smooth curve. If i have to calculate the exact area under the curve using Integration Function in Excel Sheet how should i proceed. Data points are : Issuers Defaulters Cum Issuers % Cum Defaulters % C 3 0 0.287907869 0 B 4 2 0.671785029 7.407407407 BB 35 5 4.030710173 25.92592593 BBB 242 15 27.25527831 81.48148148 A 293 4 55.37428023 96.2962963 AA...

looking for a template of bell curve graph
http://www.tushar-mehta.com/excel/charts/normal_distribution/index.htm -- Regards, Peo Sjoblom "darrich" <darrich@discussions.microsoft.com> wrote in message news:4465F7C1-22CB-4D4D-8344-6C5C0B33AC72@microsoft.com... > I have the percentages for a bell curve; I just want to plug in the percentages with a professional-looking bell curve model. I am a graduate student assisting a professor with his Power Point lectures and I am having a difficult time making a bell curve that would look professional. darric wrote: looking for a template of bell curv...

Bell Curve Chart
I have 2 columns of data - see below. My boss wants me to create a bell curve chart, but if I use the chart wizard and select "Line" from the Standard Types tab then my chart has steep angles. I reviewed Article 213930 "How To Create A Bell Curve Chart" - 4 pages of complicated how to's and got hung up on the first page. Can anyone provide me with some easy to follow instructions? A-1 11 A-2 16 A-3 23 B-1 60 B-2 32 B-3 18 C-1 9 C-2 4 C-3 2 Try using an XY chart with smoothed lines. As for creating a Bell Curve Chart - those are the simples...

Help Please. Where do I find a curved double arrow connector???
Help Please. Where do I find a curved double arrow connector??? Any connector can be double arrow simply by selecting the arrowhead dialog box (it's a button on the toolbar). Just put in a curved connector (stencil Visio Extras > Connectors), and then change the arrowhead settings. >-----Original Message----- >Help Please. Where do I find a curved double arrow connector??? >. > Thank you for your help but I may have asked my question wrong. I am using the CURVED ARROW from stencil: BLOCK DIAGRAMS>BLOCKS. I would like to use a curved arrow like this but I need i...

how to create a 4 parameter logistic equation curve ( 4 P L Curve
how to create a 4 parameter logistic equation curve ( 4 P L Curve ) with VB , VBA or Excel? instead of using MatLab or other Math add-in ? thanks in advance In article <3CDB610D-71E4-4593-A808-928EA4B5AE89@microsoft.com>, sunlite@discussions.microsoft.com says... > > how to create a 4 parameter logistic equation curve ( 4 P L Curve ) with VB , > VBA or Excel? instead of using MatLab or other Math add-in ? > > thanks in advance Program the solution to equation, then use your favorite plotting tool to plot it. D thanks David so much! Would y...

How do I get the Fitness Chart for Men to work longer?
I have been using the Fitness Chart for Men Excel template to track my weight loss. I find that the downloaded version only has 31 rows, at which point the borders end and it no longer graphs any data that I might enter. How do I extend this template so that it can track my weight for longer than one month? Prospect That will be quite a project. You can simply copy down to extend it then clear data from columns B through E below row 31 but.......... Now you would have to adapt all the formulas in columns F through I to return a blank if nothing entered in A through E. =IF(B32="...

How do i get an excel spreadsheet to fit on one piece of paper
I want to get an entire spreadsheet to fit on one piece of paper when printing. I am now using Windows XP. My older version in Windows 98 had a print option tht allowed you to click on a button to fit to one page. File|Page Setup|Page Tab Fit to 1 page wide by 1 page tall. You sure it wasn't word that had that the "Shrink to Fit" on the print preview window? takiled wrote: > > I want to get an entire spreadsheet to fit on one piece of paper when > printing. I am now using Windows XP. My older version in Windows 98 had a > print option tht allowed you to click ...

Visio normal curves
The Normal Curves in Viso 2003 Professional should actually look like they came from the normal distribution. They are the wrong shape! A normal distribution curve is not as pointed at the top. It is much more smooth. I would be embarassed to say this was a normal curve! Justin ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based News...

drawing curves
I am trying to draw a curve following this Publisher Help: < On the Objects toolbar, click AutoShapes , point to Lines, and then click Curve . In the publication, click where you want to start the curve, and then continue to click wherever you want to add a curve. Do one of the following: To complete a shape, leaving it open, double-click. To complete the shape by closing it, click near its starting point. > I follow these instructions but I can't get it to work. I expected to get a curve which would have a centre point that I could drag into the curve shape I want. Where am ...

cards will not fit in regular envelopes
Does anyone have a solution for envelopes to mail the printable cards. The cards, when folded will not fit in a regular envelope - is there a template that can be printed & folded in which these cards can be mailed. Thanks for your help. Define a "regular envelope"> JoAnn Paules MVP Publisher "bill102" wrote: > Does anyone have a solution for envelopes to mail the printable cards. > The cards, when folded will not fit in a regular envelope - is there a > template that can be printed & folded in which these cards can be mailed. > Thanks for your ...

How to fit a row height to a cell contents automatically?
Hello How to fit a row height to a cell contents automatically? Double clicking on the row edge doesn't decrease height. There's free space left below text. This happens to some rows only. -- Please, inform me of your reply: click Message menu, then 'Reply to all' in Outlook Express (this sends your reply to the newsgroups and to my email address at the same time) or inform me by sms to 79163876746 from www.mts.ru/sms or to my ICQ#135557327 or by messenger to kopnichev@hotmail.com Mr. Dmitriy Kopnichev e-mail: kopn@hotbox.ru Select whole row and set font size to, say, 6. R...

Bell Curves #2
I am looking to graph a bell curve for a set of numbers. I did not find anything undertemplates ot one of the listed charts. If anyone has a template or can direct me to where I may find instruction on programing a bell cure i would appreciate it. "rob" <rob@discussions.microsoft.com> schrieb im Newsbeitrag news:FDD5300E-757F-4550-A172-5E711C86029C@microsoft.com... >I am looking to graph a bell curve for a set of numbers. I did not >find > anything undertemplates ot one of the listed charts. If anyone has a > template or can direct me to where I may find ...

Line of best fit
Hello, Does anyone know how to draw a line of best fit through points of data on a scatter diagram in Microsoft Excel 2000? I would be grateful for any help, thank you very much ...

An Inventory order and tracking db for hydraulic fittings
I am starting a new db to track hydraulic fittings and inventory. I want to do it right, from the start by having it normalized, and not quite sure what I am doing. My employer and head office is use to the old part # of the fitting and wants to use it for billing and organization on the parts shelves, but the parts have to be ordered using a new # part # because they changed suppliers. Yes they should use the new #'s but they don't want to and they sign my pay cheque, so I have to try to keep everyone happy. Each fitting comes in different sizes, and is going to hav...

Curve Fitting to Data
Any one know how to fit a second-order polynomial to a data series? Charlie - Bernard Liengme has an example on his web site: http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Charlie Webb wrote: > Any one know how to fit a second-order polynomial to a > data series? ...

Gradient of curve
How can I use Excel to find teh gradient at any point of a cumulativ frequency curve ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Use LINEST to find the equation of best fit. Differentiate the equation and evaluate at the points of interest. Bernard "loz-quiches" <loz-quiches.xeg2b@excelforum-nospam.com> wrote in message news:loz-quiches.xeg2b@excelforum-nospam.com... > > How can I use Excel to find teh gradient at any point of a cumulative > f...

distribution curve (skewness)
Hi all, How can i draw a distribution curve (normal curve) which skew to th left or right?Thank -- hendr ----------------------------------------------------------------------- hendra's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3551 View this thread: http://www.excelforum.com/showthread.php?threadid=56353 The normal distribution is not skewed. The gamma distribution is skewed, and Excel will calculate its probability density function. Jerry "hendra" wrote: > > Hi all, > > How can i draw a distribution curve (normal curve) whic...

Automating column "best fit".
I have Access2003 & WindowsXP. I have a crosstab query to give me a debtors trial balance, and many other crosstab queries such as sales per year etc. Now and then I use the right-click "column-width best fit" to size the crosstab columns so as to reduce the size of the column widths. Can this be done *automatically* so that the columns are so sized on opening the crosstab? Please help, Fran ...