Selecting data from lists based on multiple variables to calculate statistical formul

I am fairly new to excel and many thanks to all the people who have helped me so far. I wonder if somebody would be kind enough to take a look at this problem. If I can figure out the concepts behind this, I should be able to apply this to many other problems!

***********************************************
1.)

I want to calculate a correlation coefficient (r) between two data variables, x and y in two columns.

r = Sum(x - mean x)(y - mean y) / SQRT( sum(x - mean x)^2 * sum(y - mean y)^2)

I can laboriously calculate this having extra columns for (x - mean x) and (y - mean y)^2 etc. However is there a quicker and neater way of doing this with a formula in one cell?

***********************************************
2.) The problem however is more complex:

Assume variables x and y are in columns A and B.

I only want this formulae applied to patients which register as "M" (male) in column C and a particular drug eg. "Etanercept" in column D.

If data is missing in column A or B and is left blank, I want it ignored from calculations.

Is this possible to do? Is so would you be most kind to post a solution to problem 1 and 2 so I can unpick the formulae are learn so I don't have to bother people again.

Many thanks

Andrew

Hi Andrew,

As a suggestion, try this:

=IF(A2*B2=0,"",IF(C2="M",IF(D2="Etanercept",(A2-AVERAGE(A:A))+(B2-AVERAGE(B:B))/SQRT(POWER(A2-AVERAGE(A:A),2)*POWER(B2-AVERAGE(B:B),2)),""),""))

I have placed the X and Y results in columns A and B respectively.

Hope this helps,

Tim

Cheers Tim for your help but I'm not quite sure your solution is what I'm after.

I need to do the calculation of R in one cell for all the patients who meet the specified parameters.

I have included a small example workbook. My laborious working out is in half hidden in grey. My method is obviously not suitable with thousands of patients who are not neatly arranged in order of males and females and in order of drug v. placebo as they are in this sample!

*********************************************

I've done a sample calculation for:

1.) Females on the drug etanercept (ignoring the female who is missing data from the calculations) - highlighted in yellow.

2.) Males on the placebo drug (ignoring the male who is missing data from the calculations) - highlighted in blue.

I've put the necessary formulae in bold text on the worksheet.


I'm stuck and would be very grateful for any help.