This question already has an answer here:

I have about 80 predictor variables (with some multicollinearity, I assume) and a non-normal count data response variable (n=570) which is arranged into groups (n=34). I need to reduce the number of predictors for the use of GLM and/or GLMM. Ideally, the number for regression model in this case would be about 4 or 5 predictor variables.

Spearman's correlation, linear or curvilinear trend line in excel would be an easy way (for me as a student) for looking the most obvious predictors, but I think I need to do that for each group separately, and that obviously will result in a large number of coefficients. Is it appropriate to use the highest correlation coefficient average to "judge" the best predictors for later use? So I would be comparing coefficient averages (got from a single grouped variable) of different variables.

I'm sure there are plenty of better ways to choose the best predictors and feel free to suggest methods. Just to mention every one of those 80 predictors could be important.


marked as duplicate by gung Nov 21 '18 at 15:13

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.


This is not going to be straightforward in Excel. Environments like R, Python, and others have routines to make this part of your workflow, so I'll suggest you consider migrating. That said, there are several things you can (and should) do. In no particular order: 1) You can use PCA to extract linear combinations of variables. Those that are highly correlated are likely to group together, but you lose some interpretability on what those features represent. If your main goal is prediction accuracy, that's probably ok. 2) You can identify variables with very high correlations and remove all but one of them. In the R package 'caret', the function 'findCorrelation' helps identify these and select the best of the set. It is slightly more complicated than 'choose the one with the highest correlation'. 3) Once multicollinearity has been dealt with, you can use regularization to accomplish feature selection and get your feature set down to a manageable size. Popular approaches include ridge regression, lasso, and (my favorite) the elastic net.


Not the answer you're looking for? Browse other questions tagged or ask your own question.