Calculate Best Fit Equation Of Curve
I am trying to calculate the equation of a curve which best fits some data. On the xaxis is date and yaxis 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 xaxis 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:
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, unrecommended addin.
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 A Multivariable Equation Based Off Of Data In A Table With Different Criteria
I'm trying to calculate a multivariable 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 04999 and G3 is 5000+, this should impact where the value in B1 falls. B2 should select from H1 to N1.
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 = (99D)/(HC) = m Linear Function = Yy = m(Xx) = Y99=m(XH) Each student grade would = "Y" i.e. Y=m(XH)+99
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 FebNov (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
Using Another Cell's Value In Equation
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 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 timeconsuming and tedious.
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 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 addin 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
Referencing The Value In A Cell For A Max Equation
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 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. =SUMPRODUCT((Sheet1!$T$3:$T$10000=A49),(Sheet1!$S$3:$S$10000="BS"))
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 crossposted here: http://www.excelforum.com/excelprog...ml#post1996135
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: Sheets("Graphs").Select ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select Selection.Copy ActiveWindow.Visible = False Windows("Copy of HRA Design calculator1.xls").Activate Sheets("Binder @ max. stability").Select Range("A1").Select ActiveSheet.paste The problem is line 4  it can't copy because the text isn't selected.
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 EvenMoney Probability Formula for Risk Arbitrage assessments). The formula is as follows, where I am attempting to solve for Pe: (Pe*EP)+(P2*EL)=0 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 (=1Pe) (placed in column B3 in Excel) EL=expected loss if deal breaks up (placed in column B4 in Excel) B1*B2)+(B3*B4)=0 So, once again, I am attempting to solve for Pe, or column B1 B1=((1B1)*B4)/B2 say, EP (or B2) =$2.00 and EL (or B4)=$4.00 B1=((1B1)*4)/2 B1=2*(1B1)
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
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...Hd2JSrIj7HPew
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
How To Calculate Combinations ?
I want to see how much combinations are possible when i got 6 numbers..  got this numbers 123456  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 (22) or (223) or (2234) 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
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 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 1100 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,meanhalf width) i have the mean (avg of all numbers) i have the t value (TINV(alpha, (n1))) 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 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
