Create A Polynomial Equation Of Z From The X And Y Inputs?
Jan 20, 2014
I'm familiar with 2d graphs, trendlines, and regression equations. Now I have some tabular data that has 2 input variables and a result. As with my 2d data, I would like Excel to create a polynomial equation of z from the x and y inputs. Can Excel do this, or do I need a plug-in or another software package?
I need the code to create a find box into which a search string is input. The code should find the string at a particular cell, then copies this cell and four cells to the right to a location in sheet 2 of the same workbook
The code runs but comes back with a runtime 424 error. I believe the issue is being caused by "nextCell" but I cant see why.
Sub FindStrings() Dim firstCell, nextCell, stringToFind As String ' Show an input box and return the entry to a variable. stringToFind = _ Application.InputBox("Enter J Number and Stage Number. For Example J1234 ST1", "Search String") ' Set an object variable to evaluate the Find command.
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.
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.
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?
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}
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.
i have a function in a cell (that works) to extract coefficients from a range of cells in a workbook:
VB: =INDEX(LINEST(CP25:CP27,CQ25:CQ27^{1,2}),1)}
i have variables for cp25:cp27 and cq25:cq27 already defined in my vba code. the values for these in the case i am working on are as follows (returns 110.5):
1) how to do this function in VBA only - this is part of a UDF and cannot have any helper cells 2) how to refer to 560,570,580 as a 'range'. is there a way to put these six variables into my ranges for later processing?
All of the google searches i have deal only with linear regression, taking from existing graphs, or say to just use the function i have above.
I have tried
VB:
Var = Application.WorksheetFunction.LinEst(Sheets("references").Range("CP25:CP27"), Sheets("references").Range("CQ25:CQ27^{1,2}"), 1) [COLOR=#333333][/COLOR]
but return #value! errors. when i remove the ^{1,2} portion, i do return a value but it is incorrect (returns 160), what is the correct syntax for adding in the ^{1,2}? if you do that would be fantastic, but brings me back to issue #2 in that i need to refer to my variables in the vba code and not this range (as they will eventually be going away).
I have quite a number of 4th degree polynomials and attempting to extract the variables from every equation. I used the formula below (from another thread);
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.
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
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?
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.
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.
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
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] .........
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})