Correlation with criteria

[Neil07979]

Hi Guys

I have tried searching the forum to get an answer - but thinking that the results are too complex for what i am trying to do.

I have a set of data with various columns, A: ID, F: Unit, G: Values; the data covers multiple date ranges, across different ID's and diff units.

On another sheet - I have 3 cells, (C4: ID, C5: ID, C6: Unit)

I want to enter the ID's that need correlating, along with the Unit number; which will then give me the result in Cell D6.

I have got this far - but doesn't work, and also doesn't take into consideration the Unit (my head is going to explode!)

<Code>
=CORREL((IF('6Months_Data'!A2:A3742='Analysis Tool'!C4,'6Months_Data'!G2:G3742)),(IF('6Months_Data'!A2:A3742='Analysis Tool'!C5,'6Months_Data'!G2:G3742)))
<Code>

******** Entered as an Array ********

Any help is greatly appreciated.

Thanks

[mewingkitty]

Could you post an example worksheet? I kinda think I know what you're trying to do but I'm not sure.

[Neil07979]

Attached is a smaller version of what I am trying to do.

I haven't got the Unit criteria yet - but would need to build this in also at some point!

[shg]

CORREL ignores logicals in both arrays. When there is a logical value in either, it ignores the value in the corresponding position in the other.

There are no values in your arrays where corresponding positions are both numeric since the Metric IDs are mutually exclusive -- ergo, #DIV/0!

I realize that doesn't solve your problem, and am pondering ...

[Steve R]

I canīt create an array without blanks or logicals to use in Correl() function. I created a "Help Sheet".

See attached workbook.

[shg]

Steve's approach looks good to me.