I am trying to do multivariate polynomial regression in excel, trying to correlate data of the form y=f(x1,x2) with second order polynomials:
Y = c + a1*x1 + a2*x1^2 + a3^x1^3 + b1*x2 + b2*x2^2 + b3*x2^3
Using the following command, I have achieved this:
=LINEST(A2:A10,B2:B10^{1,2,3,0,0,0}*C2:C10^{0,0,0, 1,2,3},TRUE,TRUE)
theory Excel uses for this kind of regression?
I have one question about the regression. i know how can i do it lineair, exponentiel ... but how can i make a personnalised regression ? for example, if i have i function f(x)=a+b*x^2 or another function ... how can i introduce my function to draw the regression ? i have excel 2007
View 2 Replies View RelatedDoes anyone have VBA code to sample from a multivariate normal distribution? The correlation can be the same for all variables.
View 2 Replies View RelatedI have a multivariate dataset with a large number of cases. eg. 50.000 cases (data points) and 40 variables. I need to calculate the distances between each pair of data points in this 40-dimensional space. Obviously i cannot produce a whole data matrix, which would have to be 50.000 x 50.000 big. but i need at least the average distance - if N is the number of cases there are (N*N-N)/2 possible pairs of points. Idealy, the (N*N-N)/2 distances could be placed in a column so i could make a histogram of the values. Still this doesn't seem like a very good solution as the (N*N-N)/2 distances if N=50.000 would fill about 75 worksheets!
Here is a simplified exaple of what i want to do, but with N=4 and only 3 variables:
original data:
IDv1v2v3
1103010
2202510
3202020
4402015
Distance matrix:
1234
10 11,1803398917,3205080832,01562119
211,18011,1803398921,21320344
317,32 11,18033989020,61552813
432,01 21,2132034420,615528130
I have plotted a graph which compares the miles done with the petrol used.
I'm trying find a formula which allows me to apply the petrol and would give me the miles covered.
From excel I have applied the tread line of polynomial and obtained the following formula...
y = 1E-06x5 - 0.0002x4 + 0.0161x3 - 0.4893x2 + 7.5295x - 20.87
R² = 0.9989
When is it applied to excel with the value of petrol it does not give me the miles.
I'm working out the formula in a incorrect manner?
I have attached the excel file.
y = 1.083333333x4 - 7.3333333x3 + 16.416667x2 - 6.16667x + 4
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 have this data:
x y
310 516
320 526
325 535
330 587
335 640
340 715
345 857
350 1040
When I perform a third degree polynomial curve fit I get this equation:
y = 0.0106x3 - 10.0266x2 + 3152.3381x - 329943.8370
R2 = 0.9987
Which isnt right. On a calculator, I get negative numbers. Does excel have problems with 3rd degree polynomials?
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.
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}
Often there will be many more than four terms...
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?
View 12 Replies View RelatedI 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.
The above is a screenshot of the data analysis (regression) I want to automate with vba code. Like all macros, I tried to record first and only got the following
View 3 Replies View RelatedI was handed the attached file. understand everything except how the values in row 6 were derived No formula present when I received the file, just the numbers. Row 7 is hard entered scores the units achieved
View 8 Replies View Relatedi 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):
cp25 = 560
cp26 = 570
cp27 = 580
cq25 = .414
cq26 = .479
cq27 = .536
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);
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))
but it only extracts the first variable.
4th degree polynomial function: f(x) = ax^4 + bx^3 + cx^2 + dx + e
Example of one of the equations: Y = -42.4276 + 319.989 X - 809.094 X^2 + 942.247 X ^3 - 397.587 X^4
I'll like to have the final result like below (Based on the Example equation above)
edcba-42.4276319.989-809.094942.247-397.587
I have the following dataset and was wondering how I can run a constrained regression in Excel with the constraint being that the total allocation of assets is 100%:
Total return (y): 12 data points
Asset 1 (x1): 12 data points
Asset 2 (x2): 12 data points
Asset 3 (x3): 12 data points
Asset 4 (x4): 12 data points
Asset 5 (x5): 12 data points
Asset 6 (x6): 12 data points
[Attached is a spreadsheet with the actual dataset]
I know the regression equation I need is R = b1X1 + b2X2 + ….+ (1 - b1 - b2 -….- b5 )X6 + e
I am wanting to write a macro which uses the excel multiple regression function (a part of the data analysis add-in). I tried recording a macro while I selected the regression function (Tools> Data Analysis... etc.) which produced the following:
View 3 Replies View RelatedI would like to run a multiple linear regression in vba. I have one dependent and three explanatory variables. I will have to use a macro of some kind, since I need to run too many regressions to do it manually. To simplify things a little bit:
- There will always be exactly three independent variables
- There are no missing values
- The data is allways numerical
I've already got four ranges defined: Yrange, X1range, X2range, X3range. I would like to take these ranges as input parameters for the regression model. The only two parameters I need are Sum Square for Regression (SSR) and the degrees of freedom. I understand that you can use excel's matrix formulas to calculate some of the input parameters, but one doesn't really get around vba. Any (simple) source code allowing me to conduct a regression with three input parameters?
I am trying to set up the formula y = ax^2 + bx + c. Is there a function for that in excel?
To get a little more into my overall goal. I will have a x constant that will remain the same, but I have 8 different sets of a,b, & c coefficients. So, I would like to set up something like, if a row is labeled A1, find the A1 set of coefficients and use them in the quadratic equation. I was thinking I would need to use a CSE, is that correct?
Ok so heres an example workbook:
Book5.xlsx
As you can see from the workbook, each column's amount of numbers depends on the data pulled from an index/match function.
These numbers will change and I want the TREND function to dynamically change with the data.
If I have a set of numbers 1,2,3,4,5,6,7,8,9 in cells, how can I get a line of best fit, linear regression, in the cells next to them?
View 1 Replies View RelatedGiven 100 samples, to calculate: numbers of hours spent on study is dependent on numbers of hours spent on social networking site.
x (social networking site)
y (study)
x^2
xy
6
4
36
16
24
[Code] .......
SUMMARY OUTPUT
SUMMARY OUTPUT
Regression Statistics
Regression Statistics
Multiple R
0.571423290877713
[Code] ....
So, I've got the coefficient of correlation equals to 0.57, how can I plot my scatter graph?
I have set up a linear regression array in Excel and now want to test the significance of my r2 value at a certain level of significance. I've only been able to find tables that give the critical r value, but I want to test it at 99.73% level of confidence and none of them contain that specific value. Is there a way to do this in Excel?
View 3 Replies View RelatedI have a set of data. I know how to do linear regression over the whole set of data. How do I have another linear regression over the first 5 points in the set of data on the same graph ?? I am using Excel 2007
I'm trying to write a macro that will analyze data from one spreadsheet and do a regression. The information I want to be output on the same sheet. I tried to use the record function, but I got an error. It said "Run-time error '1004': ATPVBAEN.XLA could not be found. The code read:
View 2 Replies View Relatedto getting accurate regression coefficients from a dataset with *small* (standard) numbers, which contains centered predictors. More specifically, I have a dataset with 18 observed data points containing a criterion (y), a centered predictor variable (x), another centered predictor variable (z), and the interaction of the two centered predictor variables (xz). This multiple regression equation is structured to test for interactions between the two continuous predictor variables (x and z) as prescribed by Aiken and West (1991) in their classic book.
When I run the regression in Excel with the centered predictors, some of the regression coefficents in the output are estimated to be 0, although they are clealry *not* 0 as estimated by SPSS 14.0.2. I have spent many hours troubleshooting this problem (and searched many forums on the internet) and still do not know why this is happening.
Initially, I thought the problem might have to do with the cross-product of the centered predictors, but even just doing a regression with one of the centered predictors (for certain centered predictors) yields a regression coefficient of 0 (although it should be non-zero as per SPSS 14.0.2). When doing these multiple regressions with non-centered predictors, all regression coefficients are estimated accurately.
I was wondering if anyone had any insights on why I am experiencing these problems.... If anyone wants a sample of some test data I have used to troubleshoot these problems, you can download a file from: [url] and/or email me at (email deleted by Mod) for more datasets or questions.
However I have a subject at uni that requires me to create a series of regression models, histograms, correlation matrices etc.
For part of the assignment, i have to run 4 regressions (one for men & one for women) with dependent variable as average wages, and independent as bfast 1,2,3 and dinner 1,2,3 (all of which are dummy variables) (0 for male and 1 for female).
The second two regressions are exactly the same, except average wages must be transformed into log which i have already done.
I dont know how to differentiate the regression models into male and female (if possible). We must also include residuals and residual plots.
I keep getting an error that says non-numeric data, the other says input range must be a contiguous reference.
I have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)
Sub aaa()
'
' aaa Macro
'
[Code].....
I have data in the following format-----
Name : XYZ
City : ABC
Place : sdfg
Error :
price : [X]
cost : [ ]
time : [ ]
[code]....
I want only name, place,desc and under error, i want that type which is marked cross in the brackets(In the above example it is price ) .
name, place,desc,error should be pasted to separate columns in second excel sheet.