IndigoPlatea
02-14-2017, 02:20 PM
Hi All,
I am currently working at a large technology consulting organisation and I have been tasked to make a multiple regression analysis model on Excel to predict “Wage Rates” (Column E – my dependant variable). Though I am aware how to run the analysis through the “Data Analysis” toolpak, it’s not feasible to run hundreds of regressions especially when variables are likely to be adjusted monthly.
Having said this, is it possible to produce a VBA or Macro to have the analysis done in the file? I can run a multiple regression analysis per Role (Column D), but that would take a lot time to run it per Role (there are a number of roles per Country) and per Country (there are dozens of countries). Also, analysing what the best trend line is per output in order to formulate the most appropriate equations. Furthermore, using the Summary output to produce an equation to predict the data in the rows. I have examples for two roles, Architect and Developer – both with mock data. In the real example, I would have many more variables GDP, technology lifecycle trend etc.
Again, would there be a function through VBA or Macros which would recognise the Country, Role, the independent variables etc. to produce many Multiple Regression Analysis results at once – based on those criteria? Even tips on how to reorganise my data for the anticipated VBA or Macro would be nice.
I should note - I am not a VBA user at all, though I have implemented simple codes.
Any help on how to proceed would be great.
Thanks
I am currently working at a large technology consulting organisation and I have been tasked to make a multiple regression analysis model on Excel to predict “Wage Rates” (Column E – my dependant variable). Though I am aware how to run the analysis through the “Data Analysis” toolpak, it’s not feasible to run hundreds of regressions especially when variables are likely to be adjusted monthly.
Having said this, is it possible to produce a VBA or Macro to have the analysis done in the file? I can run a multiple regression analysis per Role (Column D), but that would take a lot time to run it per Role (there are a number of roles per Country) and per Country (there are dozens of countries). Also, analysing what the best trend line is per output in order to formulate the most appropriate equations. Furthermore, using the Summary output to produce an equation to predict the data in the rows. I have examples for two roles, Architect and Developer – both with mock data. In the real example, I would have many more variables GDP, technology lifecycle trend etc.
Again, would there be a function through VBA or Macros which would recognise the Country, Role, the independent variables etc. to produce many Multiple Regression Analysis results at once – based on those criteria? Even tips on how to reorganise my data for the anticipated VBA or Macro would be nice.
I should note - I am not a VBA user at all, though I have implemented simple codes.
Any help on how to proceed would be great.
Thanks