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


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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
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
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 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
Calculate A Multi-variable Equation Based Off Of Data In A Table With Different Criteria
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 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.

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
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
Using Another Cell's Value In Equation
Lets say I write an equation on my sheet such as:

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

View Replies!   View Related
Equation Behind The Function
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 Replies!   View Related
SUM IF- Excluded From The Equation
I have the following formula.


very simple.

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

View Replies!   View Related
Setting Up A Log Equation
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 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
Multiply And Round In One Equation
I need to take a number, multiply it by a precentage, & then round it to the nearest multiple of 5.

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

View Replies!   View Related
Regress Nonlinear Equation
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


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 Replies!   View Related
Get The Result Of An Equation In A Cell
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


in the next cell to display the result


View Replies!   View Related
Removing Sections Of Equation If Any Value = 0
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 Replies!   View Related
Referencing The Value In A Cell For A Max Equation
I have a cell on ' Sheet 2' with the following formula:


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 Replies!   View Related
SUMPRODUCT Equation Based On Multiples
to create the following formula which I'm now repeating in more elaborate forms. At the moment I am using the formula below but I need to get the bit in red in the second half of the formula to search on more than the one condition.

Currently it is searching a postcode abbreviation field and is picking up all those with 'BS' however I don't just want to search on BS. I would like to search on BS, BA, SN, TA, GL. I have tried putting different combination using commas and plus signs etc in but excel doens't seem to like the way I'm doing it.


View Replies!   View Related
Equations :: Solve An Equation For X With Two Variables
Can excel solve an equation for x with two variables.

Example: 102=125000(X-910)/50000.

I need excel to solve for X

The variables are 102 and 125000

View Replies!   View Related
ComboBox: Equation In A Column Of Cells
I want to do is a simple list, 2 items, call them "Thing_1" and "Thing_2". I have an equation in a column of cells. The equation is a simple If statement. If the user selects "Thing_1" then the first equation will run, and if "Thing_2" is selected then the second equation will be used. That's it. No more complex than that.

My original questions about how to initialize a ComboBox are cross-posted here:

View Replies!   View Related
Result Of Text Equation In Cell
I have two cells (c1 and c2). c1 contains (2+3*4)*3 without "=" at start. c2 must contain the result of c1 it means 42. I think it has a very simple sulution but I don't know it. In the attached file I have a few variants I tried to solve it but they both don't fit.

View Replies!   View Related
Compents Of Chart Trendline Equation
I'm in the middle of designing a sheet that (amongst many other things) takes the equation of a graph trendline, pastes it, and calculates the various values along it. I can do it by hand, but when I try to record a macro, it doesn't register that I've highlighted the equation and hit copy. Here's the code I'm working with:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveWindow.Visible = False
Windows("Copy of HRA Design calculator1.xls").Activate
Sheets("Binder @ max. stability").Select

The problem is line 4 - it can't copy because the text isn't selected.

View Replies!   View Related
Complex Equation- Copy Paste To About 300 Cells
im trying to implement the equation bellow in one cell so that i can then copy paste to about 300 cells.

A= 0

A= A + {IF(B279 = B(i), C(i), 0)}

= ‡”i= 6 B(i) & ‡”i= 6 C(i)
........i= 274 .............i= 274

View Replies!   View Related
Sum/count In An IF Equation If The Condition Falls Between 2 Points
Is there a way I can get Excel to sum/count in an IF equation if the condition falls between 2 points?

For instance:

I want to count the amount of days between two dates.. I am currently looking at something like:

=If(cell>[date1]&cell<[date2],sum(cell yada yada....)

Using <> wont work in this instance.

View Replies!   View Related
Set Up An Equation For An Unknown Variable In A Formula Equal To 0
how to set up an equation for an unknown variable in a formula equal to 0 (an Even-Money Probability Formula for Risk Arbitrage assessments).

The formula is as follows, where I am attempting to solve for Pe:


Pe=even money probability (placed in column B1 in Excel)
EP=expected profit if deal closes (placed in column B2 in Excel)
P2=probability of deal breaking up (=1-Pe) (placed in column B3 in Excel)
EL=expected loss if deal breaks up (placed in column B4 in Excel)


So, once again, I am attempting to solve for Pe, or column B1


say, EP (or B2) =$2.00 and EL (or B4)=-$4.00



View Replies!   View Related
Specific Find Followed By Equation Applied And Outputted To A New Column
I have the data sheet, which is the result of another function. It produces 10 columns with numbers in each cell, there can be any number of rows, but the key is that the rows are outlined as follows. The first row has the first column number bolded, followed by the second row not bolded and then a space and a repeat of the bold cell, followed by the non bold cell. Each row fills up all ten columns. I have attached an example of the sheet.

What I would like to do is create a function which says brings up a box that says "Input Row" and the user would input the bolded number of a row. From here the code would take that value entered and divide it by the inputted bolded number’s adjacent cell, to the right, aka B2 column. This value would then be multiplied by every other bolded rows second cell, B2 column, essentially giving rise to a normalization of that column. The output would then be displayed in a new column, column 11 in italics. Each row would now have its italics value corresponding to the bolded number’s row and not the other rows.

View Replies!   View Related
Type Equation In Cell And It Is In Another Cell
I am trying to type in a basic equation in a single cell (343.56 + 46.78) and I want it to be solved in another cell. Can this be done? Can I put this function in a cell where the cell it is trying to solve is not yet put in?

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

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:


And want this results in the B column:


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:


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

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