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 RepliesI'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 RelatedThe 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 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 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.
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 RelatedI 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?
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
to 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 2 macros one hides a selection of rows and the other unhides them. What i want to be able to do is when an IF function is true then macro 1 (hide) is used. If the function is false then macro 2 (unhide) works. There is a code in VBA
View 3 Replies View Relatedif its possible to run a macro from an if statement as in
=if(weekday(A1)=5,Executemacro,"")
Or else how to program the result of a macro to a cell when a certain condition exists?
I already have a macro that takes data from an active cell with a running number:
Range("B5").Select
Selection.Copy
Range("E26").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Now I need to run this Macro when its friday (example)
Can't find it in the VB help files.
I have found this code to spred my data from master worksheet to clients worksheets!
Is there a code to add the Sum function in the last row on each of the new worksheets in specific columns where is needed?
Sub ExtractReps()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("MAIN")
Set rng = Range("A:AG")
What I would like to do is create a macro (use a shorcut key to activate ie crtl+q)or if there is a barcode I could scan to do that when the function is called for it would move down one cell then left 4 cells. I am looking to to this when walking around doing some scanning I could do that function rather than pressing the keys all the time.
View 9 Replies View RelatedI'm having a little problem with the function Sumif. I have data like this:
Date
0
0
Total
0
78[code]...
I want to some all the Totals that appear before the "End Notification" text. I have more totals after that text and the number of rows are variable.
In my data, I have always 4 totals before that text, so I thought perhaps that the way to do this would be to impose to the sumif function to stop summing after counting 4 totals. I didn't find a way to do that.
I thought a little more on the problem and I find out that the easy way would be to record a macro with the sum if function, where, in the range part, I would do a Find for "End Notification"; and select all the cells to the top of the sheet. The problem is that the macro dont record that part, only the row number.I think I need to turn the range a variable, where the variable is equal to the find selection.
I am wanting to create a custom function that i can enter into a cell to run a macro (MyMacro). I do not know how to write a function, but so far i have:
Public Function Run(MacroName As String)
Application.Run MyMacro
End Function
I have a quote system which takes data from tabbed sheets if there value is greater than 0, then does a simply formula which is then times by a discount which found by searching for the sheet name and then attaching the appropirate discount. In short this means that the whole forumale doesn't work and is not displayed
View 11 Replies View RelatedI have Asset Class in Column I and if it is 364A-EL or 365A-EL or 368A-EL or 368B-EL or 369A-EL or 371A-EL or 371B-EL or 373A-EL or 397C-EL then 'POLES' assigned in to Owner Class (Column J).
If Asset Class is 366A-EL or 367A-EL or 367B-EL or 368C-EL or 369B-EL or 371C-EL or 373B-EL then assign UGCBL to Owner Class.
Should we write the macro or can it be accomplished by any Excel Function?
What i am trying to do is count the number of rows that contain data.
It will always start in A7 and when creating the macro i hit control-shift-down arrow.
There will be a differnt number of rows for each data set. When i ran the macro it kept the result of the first data set that i used to create the macro.
here is the
I'm using this to unhide rows one at a time:
View 4 Replies View RelatedIt then places all the data on a new sheet. My problem is that some of the formula that the macro places on the new sheet come back with the result #N/A - which is fine, as they are the result of a lookup.
What I want to try and do, is at the end of the macro, get it to delete the row if the activecell is showing as #N/A
I've tried to write a small bit of code for it...
I am trying to use the Vlookup function in a macro but I can't figure out how to write it. I am using a controlled loop to cycle through my data. So for the first entry, my lookup data is in cell A2. The lookup table is located in a sheet called 'Trade Table' and the lookup field is in Column B and value needed is in column C. The results go in column G. The excel formula is as follows:
View 2 Replies View RelatedTwo months ago, DonkeyOte help me (http://www.excelforum.com/excel-prog...e-formula.html) solve a problem, with this function:
View 6 Replies View RelatedAttached is a sample in which Column"B" contains Total IDs and Column"D" contains worked labour IDs. Now I want the IDs of labour who have not worked in Column"F".