• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Solver to find optimal Independent variables

M

maverick_084

New Member
Hi,

I am trying to identify the top 4 independent variables (IV) out of 23 for my model to get the max possible Rsquared. Somehow, solver is messing things up by giving non integer values for G8:J8 in the "regression" sheet.

Kindly help!
 

Attachments

Hi:

I am not sure what are your independent variables and which is your depended variable. I normally run a correlation matrix to decide which all IV I have to consider in my model. You can achieve this by running step wise regression as well. There is a free excel add-in called PHstat which will allow you to run multiple regression and get your predicted values.

All this work well if your data is normally distributed and there is no auto correlation.

Thanks
 
Nebu said:
Hi:

I am not sure what are your independent variables and which is your depended variable. I normally run a correlation matrix to decide which all IV I have to consider in my model. You can achieve this by running step wise regression as well. There is a free excel add-in called PHstat which will allow you to run multiple regression and get your predicted values.

All this work well if your data is normally distributed and there is no auto correlation.

Thanks
Click to expand...
Hi Nebu,

Thanks for responding. My dependent variable is trade receivables in Col K on the "regression" sheet. I need 4 IVs to get an optimal Rsq.
 
Hi:

Your best bet is step wise regression.

In step wise regression you start with all IV, and try eliminating the those IVs which are not statistically significant.

I would suggest you to see the changes that happens to the adjusted r-Square , rather than the r- Square. If you add more and more useless IVs to a model, adjusted r-squared will decrease, but if you add more useful IVs, adjusted r-squared will increase.This way you can determine which all IVs are significant to your model.

Here is a link to the basic excel add-in which can help you
http://wps.prenhall.com/bp_groebner_busstats_8/145/37311/9551672.cw/-/9551709/index.html

Thanks
 
Nebu said:
Hi:

Your best bet is step wise regression.

In step wise regression you start with all IV, and try eliminating the those IVs which are not statistically significant.

I would suggest you to see the changes that happens to the adjusted r-Square , rather than the r- Square. If you add more and more useless IVs to a model, adjusted r-squared will decrease, but if you add more useful IVs, adjusted r-squared will increase.This way you can determine which all IVs are significant to your model.

Here is a link to the basic excel add-in which can help you
http://wps.prenhall.com/bp_groebner_busstats_8/145/37311/9551672.cw/-/9551709/index.html

Thanks
Click to expand...
Thanks again! I am trying to build a dynamic regression model for people who are not super savvy with the regression metrics. Another reason I want it to dynamic is that if there are any strategic initiatives that were taken which changed the nature and utility of IVs, the model should take that into account and reflect that.

So , the ultimate idea was to put in a macro button to optimize time lags and select the right IVs to maximize Rsquare depending on the dependent variable selected from a drop down list.
 
Nebu said:
Hi:

if you are looking for a ready made VB code
follow this link
http://www.freevbcode.com/ShowCode.asp?ID=9070

Thanks
Click to expand...
Thanks for sharing the info. What I am really looking for is a way to ensure that solver shouldn't give non integer values even when the integer constraint has been selected. In the sheet that I have attached if you try to maximize H4 by changing G8:J8, Solver makes one or more cells non integer and stops optimization process. Can you kindly look at it?
 
Hi:

I am not sure , I understood you correctly. Your data validation is not working properly as it is linked to a different file and even I am not sure on what you are trying to achieve here. I ran the regression and I find that only two IVs are significant to your model, if probability of accepting the null hypothesis is more(p value) why do you even consider those IVs .

Thanks
 
thanks for your initiative. But can you please look at why solver is giving non integer values if in the "regression" sheet you try to max H4 by changing G8:J8 and putting in the integer constraint for G8:J8? Kindly try to overlook the nitty gritties and just help me with the solver conundrum :)
 
Hi:

The reason is simple some of the values in your transform sheet is in text, if you copy the valued in the transform sheet and paste special as values you can see a green shade on the top left hand corner of each cell in the transform sheet. If you convert them into numbers the formula in your regression sheet will not give u a Value error. You could have got this solved before itself , but your description of the problem where way off :)

Thanks
 
You must log in or register to reply here.
Back
Top