I am very new to statistics, and have searched around the net and stack exchange for an answer, and have tried to guess how to deal with the problem. Therefore this post. I hope someone can help...

I am carrying out an analysis of the environmental impact of about 100 parametrically generated external wall constructions. For each variant, I have results for primary energy, greenhouse gases, acidification, etc.; 10 different parameters in all.

Architects are not very good at numbers, so we are looking at creating a guidance method that reduces the amount of data that architects need to look at early in the design process. I want to see whether there is enough correlation between the parameters, so that instead of looking at 10 parameters to see which construction is best, architects only need to look at 2 or 3 parameters.

Here is the graph for primary energy in relation to greenhouse gases: Primary energy in relation to greenhouse gases

How do I deduce whether there is a correlation in Excel? What is the exact work flow?

I have done the following:

1: test for skewness on both datasets (SKEW); If the result is greater than 1.0, then a constant of 1.0 is added to the data and it is converted to Log10. I have not figured out how to treat the dataset if the skew is less than -1.0.

2: Pearson r test (CORREL) is carried out. If r>0.75 then there is correlation.

3: R2 test (RSQ) is carried out. If R2>0.56 then there is correlation.

4: LINEST test is carried out to calculate whether F.DIST.RT is less than 5%, and whether m/sem is less than TINV. This is to calculate the 5% probability.

I have the following questions:

1) Is this correct? Am I missing something?

2) Are the thresholds for r/R2 (0.7/0.56) I have chosen correct to show correlation?

3) Is the SKEW/log10 approach correct, and how do I deal with negative skewness?

4) Is there an easier way to do it by dividing the two datasets into each other and looking at the ratio, standard deviation, variance, etc.

Help would be greatly appreciated!

Rob

I don't think this approach is right. I think you want some form of regression. What sort of regression would depend on how "environmental impact" is measured - it might be OLS regression or ordinal logistic regression or possibly something else. You could then easily create a formula that would create a score from the 10 parameters and the formula could be easily put into Excel or almost any program so that all the architect would have to do is enter the variables and get a score.

Doing the regression would be a relatively involved task. I would not use Excel for this - use R or SAS or some other statistical program.

  • It is a bit difficult for me to follow why what I have done is not right. If I do the same analysis on the 10 parameters, then I can choose the ones with the greatest correlation. We do not want to expect architects to use Excel - they would run a mile! We are working in Denmark, where the architects are not that mathematically literate. We just want one or two parameters, which we can say represent7correlate with the others. – RobDK Sep 16 '14 at 11:13
  • You will lose a lot of information unless the parameters are VERY strongly correlated. Your two were not. Everyone uses Excel! Heck, high school students use it. But if architects really don't want to, you could write a program to do it with a simple "enter the variables" format. – Peter Flom Sep 16 '14 at 11:16

I will try to respond to your specific questions, but I encourage you to offer a more complete explanation of what your goals are and what data you have because it is very likely people can give you better solutions and a deeper understanding.

My understanding is that there are certain measured outcomes that you, as architects, are trying to control through your design. You have measured design variables that you believe are causally related to the outcomes of interest. I take it your goal is to do this study once, get a qualitative sense of what is important and then translate that into a couple principles that architects can keep in mind when developing a design. If I'm wrong about this purpose, or if you have more than one purpose, then please expand on this.

Regarding your data: what are "parametrically generated external wall constructions"? and how did you get data on 100 of them? Were they constructed for the purposes of this study or naturally occurring? Did you select them from a larger population? How did you choose this 100?

Now to your questions:

1) Is this correct? Am I missing something?

2) Are the thresholds for r/R2 (0.7/0.56) I have chosen correct to show correlation?

There is no magic threshold for saying a correlation exists. I think you are grasping towards the idea of statistical significance... how do you know the relationship you see didn't simply arise by chance? Based on your data, what is the plausible range within which the true correlation is almost certain to lie? This is what the LINEST function is getting at as well. Really explaining this requires learning some statistics and it's not clear you want to go down this road.

3) Is the SKEW/log10 approach correct, and how do I deal with negative skewness?

I think the best approach depends on more details of the distribution. Is it logically bounded on either the high or low side? Do you observe values close to the logical min/max? For a positively skewed distribution your log(1 + x) is probably a fine transformation. For negatively skewed distribution you could transform it by adding a constant and squaring it... look for a function that gives you a roughly normal looking distribution. But if its not to severely skewed you may not need to do any transformation at all. Before bothering with tests of skewness, just look at the data (histogram) and see if it has extreme tails.

4) Is there an easier way to do it by dividing the two datasets into each other and looking at the ratio, standard deviation, variance, etc.

I'm not sure how to build on this... go back and develop your explanation of your study and I or someone else may be able to say more. Good luck.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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