I 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
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:
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?
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
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?
I 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:
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.
You take an input, click a button. This will them create a small HTML table with a text input box and another button.
Sub VisitWebsite() Dim ie As Object Dim tcode As String Dim sURL As String Set ie = CreateObject("INTERNETEXPLORER.APPLICATION") sURL = http://sample.jsp ie.navigate sURL ie.Visible = True tcode = "apples"
While ie.busy DoEvents Wend Application.Wait Now() + TimeValue("00:00:03")
For Each ieForm In ie.document.forms ieForm(1).Value = tcode ieForm.submit Set ieForm = Nothing Exit For Next The code ieForm(1).Value = tcode works. The code ieForm.submit also hits submit on the button.
The problem is I cannnot figure out how to access the 2nd button which is generated when you click the 1st button. I have tried every variation of ieForm(1).Value. There appears to be only 3 forms (0, 1, 2). When I try 3 or greater, I get an error.
When I use ieForm(2).Value = "something", the Retrieve button text actually changes name to "something".
I've attached a screen shot of the webpage. I can insert text into the top Track Code box, and click Retrieve.
I can't change the text in the Routing Position field, and I cannot click the "Set" button. Any help would be greatly appreciated. I am still a beginner with VBA and it took me a while just to get it to work with the 1st input box.
I am trying to create a document that automatically sends an email whenever a field reaches a certain value. I am pulling the data from different worksheets onto a summary worksheet and using the data on the summary sheet as the point at which my code checks to see whether the value is below the minimum required. The summary sheet updates automatically whenever I change the data in one of the other sheets but the email will not send automatically when the summary sheet is updated. When I run the macro individually the email sends, however I need some way of triggering it so that it sends automatically upon the data in the field changing. Can anyone help me with this?
Here is an example of some of the code I am using:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("D3"), Target) Is Nothing Then If IsNumeric(Target.Value) And Target.Value < 1 Then ANDES1 End If End If If Not Application.Intersect(Range("E3"), Target) Is Nothing Then If IsNumeric(Target.Value) And Target.Value < 1 Then ANDES2 End If End If End Sub
Creating a spreadsheet to inventory cups each day. When a macro was invoked do the following:
-unprotect the worksheet
-cut/paste the completed sheet below the current
-bring in the values form the previous day's inventory
-set protection on new spreadsheet to lock the cells
-blank out old values
-reprotect the worksheet
Pretty straightforward and seems to work fine for a few iterations, then crashes with "Run-time error '-2147417848 (80010108)': Automation error. The object invoked has disconnected from its clients.".
As FYI, this code was mostly generated using a record macro session, so no variables involved.
Here is a screenshot of the spreadsheet as it sits now:
Here is the code: Sub NewDay() ' ' NewDay Macro
' Create new day and copy cup count from previous day[code]....
We are trying to automate the creation of PDF files for each sheet in an Excel file,a nd keep getting error messages. Can someone help? It produces the post script file, but it has no data nd then doesn't create a PDf file at all. We have Excel 2003, and have the below code setup.
I have a file that I update daily. It involves opening the file, going to a drop down menu and selecting 1 of the listed items and pressing a button to run the macro. Once the macro is complete, I then go back to the dropdown menu and select the next line item and press the same button to run the macro on this. I repeat this task 7times.
All the macro does is load a txt file to a third party system.
I am looking to see if it is possible to reduce the time it takes to do this task.
I am trying to automate a website which I use for daily office related work. I am navigating the website, selecting some options and clicking on download button using vba code. When I click on download it opens a new internet page. Now I have to click one more hyperlink on that newly opened internet page. How to do that?
I created a UserForm (userform1) and added a label to it which essentially just says: "Pick a Colour"...
The other UF controls are added on the fly using the below code...however when I try to add the event to each button I get the Automation Error but I don't really understand how / why... the events are actually added to the VB project ok...
Private Sub UserForm_Initialize() Dim NewButton As MSForms.CommandButton Dim ws_i, line As Integer Dim clr_str As String For Each ws In ActiveWorkbook.Worksheets
I added a dummy routine for test purposes which is to be invoked on click of UF button control (I have tried placing this code in a standard module, within UF module etc to no avail):
Sub HIDE_WS(clr_id As Long) MsgBox clr_id End Sub
So to reiterate -- when I invoke the initialisation of the UF I get the following error:
Automation Error The object invoked has disonnected from its clients
All events are added for each control.
If I remove the adding of the events I know the UF populates with the correct controls etc...
pivot chart automation. I have a tab that contains thousands of rows and it grows larger on a weekly basis. On this tab is 169 peices of hardware I manage. Each week 1 row of data is added to all 169 peices of hardware. The data from the main tab contains metrics I need to chart. However, I am in need of a way of selecting a specific peice of hardware and having the pivot charts update automatically. There will be 26 Charts and going through each chart and selecting the proper hardware is going to take far too long.
Is there any way i can select just 1 peices of hardware from the data tab and have it populate all of these charts?
I've done quite a bit of research in the past week or so on the topic of solver automation using loops; while there are a few good posts scattered around, i haven't quite found the solution to my problem.
i'm trying to run the solver on a few like-sized datasets contained in one worksheet. i've set up a loop to properly reference the 4 datasets. the solver constraints are in one column on the same worksheet.
the macro runs with no errors, but it doesn't "solve." the parameter cells retain their original values.
i should mention that my macro ran perfectly before i consolidated the 4 separate solver calls into one loop. I got tired of having to type 4 different constraint changes in the code as i was optimizing the solver.
on a similar topic, does anyone know how to call a variable in the FormulaText declaration? If i wanted to step through a defined array of constraints, say [1,2,3], how do i get FormulaText:= variable to work?
Dim iteration As Long Dim i As Integer i = 8 'start in row 8 at first dataset
how to automate AddIns sequence when opening Excel? I have 5 different AddIn (xla) and I have to launch them in a specific order (after the menu Essbase is installed - with xll) cause I'm adding rows in that menu. Excel is launching my xla's in alphabetical order but before Essbase is installed.