Need some help wih a Correlation formula

[Dave L]

I have the following formula which calculates the median for D6:D671 if certain conditions are met:

Median(IF(($B$6:INDIRECT("B"&$C$703)<=$F721)*($B$6:INDIRECT("B"&$C$703)>$G721),D$6:D$671))

What I would like to do is calculate the correlation between D$6:D$671 and another range E$6:E$671 if those same conditions are met, but I can't figure out the necessary changes to the formula to use the CORREL function.

Can anyone help me out on this?

Thanks,

Dave

 

[fairwinds]

Hi,

Try:

=CORREL(IF(($B$6:INDEX(B:B,$C$703)<=$F721)*($B$6:INDEX(B:B,$C$703)>$G721),D$6:INDEX(D:D,$C$703)),IF(($B$6:INDEX(B:B,$C$703)<=$F721)*($B$6:INDEX(B:B,$C$703)>$G721),E$6:INDEX(E:E,$C$703)))


Confirmed with Ctrl + shift + enter.

 

[Dave L]

fairwinds,

Thanks for your help. I didn't use your formula exactly because I had made some changes to what I was doing, but your suggestion helped me get it all straight and working properly.

Thanks again,

Dave