How to define an array in multiple parts (eg. for the function correl)

I would like to make a correlation between two arrays that are defined in parts like this:

=CORREL((B2:B4;C2:C4;D2:D4);(F2:F4;G2:G4;H2:H4))

But the brackets don't help, and neither do the {}-brackets or making it an array function. But surely there has to be a way to do this?

As you presented them the two arrays could be simplified as B2:D4 and F2:H4.

The formula below works for me

Please Login or Register  to view this content.

Does that work or are your arrays more complex?

ChemistB

Originally Posted by ChemistB

As you presented them the two arrays could be simplified as B2:D4 and F2:H4.

The formula below works for me

Please Login or Register  to view this content.

Does that work or are your arrays more complex?

ChemistB

No doesn't work and yes more complex. The point was to have a larger array defined in multiple parts. That is, all the cells defined before the non-bracketed semicolon in the code I posted to all the cells defined after the (non-bracketed) semicolon! That is the cells in columns B, C and D to the cells in F, G and H.

=CORREL((B2:B4;C2:C4;D2:D4);(F2:F4;G2:G4;H2:H4))

And yes, I know this particular example actually can be solved by simply as rectangles like B2:D4, but this was only for demonstrating purposes. I actually want to have thing like this:

=CORREL((B2:B6;B9:B15);(C2:C6;C9:C15))

Is excel truly that stupid that a formula like this can't be fed to it? I've tried entering it as an array formula but it doesn't help. I believe my problem is in fact exactly the same as in this thread:

http://www.excelforum.com/showthread.php?t=636112

Originally Posted by Smirgelius

I believe my problem is in fact exactly the same as in this thread:

http://www.excelforum.com/showthread.php?t=636112

Well. Maybe it wasn't "exactly" the same, as the other problem had an alternate solution to it. So, if anyone has a solution to this, I'd be grateful. Or otherwise if there is proof that what I'm trying to do is purely impossible, that'd be at least informative as well.