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})

ADVERTISEMENT

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

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.

View 14 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 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

Calculate A Multi-variable Equation Based Off Of Data In A Table With Different Criteria

Jan 8, 2010

I'm trying to calculate a multi-variable equation based off of data in a table with different criteria. If you reference the attached spreadsheet I am trying to calculate cost based on weight and zone. For the data entered in cells B1 and B2 944.01 would be the cost. If you look at the table in G2:N3 you'll notice that the values in G2 and G3 are the weight limits G2 is from 0-4999 and G3 is 5000+, this should impact where the value in B1 falls. B2 should select from H1 to N1.

View 3 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

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

Equation Behind The Function

May 9, 2006

How do I get to see the equation behind Excel Functions? For instance, the
result of pmt() is not making sense to me, I'd like to see what this function
is doing and how it is calculating its value. Trial and Error is rather
time-consuming and tedious.

View 13 Replies View Related

Setting Up A Log Equation

Oct 9, 2009

It looks like this (log(X/Y))/log2. The X and Y are celldirections (E2 or F4 and so on). How do i setup that formula in excell?

View 2 Replies View Related

1 Equation For 4 Variables

Jul 24, 2012

Can I use excel to get make an equation for 4 variables (x,y,z,w)

E.g.

a,b,c,d,3,f,g,h,i, would be constants

w = ax + by + cz + dx^2 + ey^2 + fz^2 + gxy + hyz + iyz

I tried regression but the equation looks like

w = ax + by + cz + c

Is it possible on ay other software e.g MATLAB

View 9 Replies View Related

SUM IF- Excluded From The Equation

Oct 18, 2007

I have the following formula.

=SUMIF(hfx!A:A,"123456789",hfx!J:J)

very simple.

for the totals in J, if there is a (-) negative number, i would like that excluided from the equation.

View 9 Replies View Related

Using Another Cell's Value In Equation

Oct 22, 2008

Lets say I write an equation on my sheet such as:
='A1'!$H422*AH$138

but instead of hard coding the A1 sheet name, I want that to be a value that I can put in a different cell (lets say cell C1) on my same sheet in such a way that it will reference the value in my other cell (Cell C1) and put its value into that equation.

So if I make the value in C1 = A99 then the equation becomes
='A99'!$H422*AH$138

View 9 Replies View Related

Vba To Get Best Fit Equation Into Cells

Mar 20, 2007

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 2 Replies View Related

Regress Nonlinear Equation

Nov 20, 2008

Below is a nonlinear model, together with calibration data for which I would like to find the parameters Ro, Kc, a and b.

R = Ro + Kc * (1/(1 + exp(-(a + b*lnC))

Calibration Data

C R
0.00.3347
4.80.4201
7.70.5141
11.20.6177
14.40.6887

Given the calibration data above, how can I calculate or at least estimate these parameters?
I do not want to use an add-in program, like Solver, in Excel. I do, however, want to be able to use any existing functions, if necessary, in Excel. i'd appreciate it if someone can walk me through the steps.

View 9 Replies View Related

Removing Sections Of Equation If Any Value = 0

Jan 13, 2009

The equation is basically: 10^[A/20]+10^[B/20]+10^[C/20]. Now imagine that there is no input value for C. I need to make it so that it won't add the bit I've highlighted in green.

View 5 Replies View Related

Get The Result Of An Equation In A Cell

May 13, 2009

I will like to ask, if there is a way to get in a cell the result of a simple equation without having to copy the contents of that cell with a = in front for example if i have in one cell

15*2+35*4+30/2

in the next cell to display the result

185

View 11 Replies View Related

Multiply And Round In One Equation

Mar 1, 2007

I need to take a number, multiply it by a precentage, & then round it to the nearest multiple of 5.

ex:
A1=142.5
B2=A1*50%,(then rounded to nearest multiple of 5)

View 9 Replies View Related

How To Substitute Cell Value Into Equation

Aug 6, 2012

if i obtain an eqn by plotting a graph in excel, etc,

y = 1.22952117E-06x3 - 1.08197863E-05x2 + 9.97531712E-01x + 3.99014495E-01

and i wish to subsitube a cell value from another sheet into the 'x' in the above eqn and i need to do this 20 times.

View 3 Replies View Related

Move Value To Another Field (with Equation)?

Mar 18, 2013

I need to move a value to another field (with equation) and use the output for the same equation till limit is reached..

View 2 Replies View Related

How To Add Equation To Dropdown Selection

Jan 11, 2014

How to add an equation to a drop down selection if you take a look at the picture in the link supplied, what I want to do is: I want the calculator to take the number in D12 then if the drop down box under it the selection is (as it is) too high then subtract 50 (k11) from D12 and display the answer in D14. I have listed the 3 drop down choices in column L, Lap 2 I would do exactly the same and so on [URL] .........

View 6 Replies View Related

Referencing The Value In A Cell For A Max Equation

May 20, 2007

I have a cell on ' Sheet 2' with the following formula:

=MAX('Sheet1'!C3:'Sheet1'C10)

ie. it chooses the maximum value from column C between rows 3 and 10 on "Sheet 1".

Lets say the cell reference C10 is now entered into cell B1 on Sheet 2. How do I modify the above formula to reference B1?

I have tried INDIRECT function but can't get it to work.

View 5 Replies View Related

Equation To Find Top 2 Highest Values?

Mar 8, 2014

I have the following 2 formulas that determine the top 2 scores and return the respective headers, but they aren't perfect.

=INDEX(T1:V2,1,MATCH(MAX(T2:V2),T2:V2,0))

The above returns the max, however doesn't take into account scores that are a tie or 0 values

=INDEX(T1:V2,1,MATCH(LARGE(T2:V2,2),T2:V2,0))

The above returns the 2nd highest value, but some instances the output is correct (if there is a tie or a 0).

How can I fix both of these equations and also determine the 3rd highest score?

View 5 Replies View Related

ADVERTISEMENT