Excluding Zeros from CORREL Function

=correl(if(c3:c9,c3:c9),if(d3:d9,d3:d9))
should work (similar to yours but a bit simpler)
needs entering with ctrl+shift+enter
when you do so, curly brackets should appear around the formula, comme ca:
{=correl(if(c3:c9,c3:c9),if(d3:d9,d3:d9))}

hth

You need at least two matching pairs for correl not to return div0

formula provided does so...

Perhaps OP means if 0 appears in either cell ignore - as opposed to just in D ?

=CORREL(IF(C3:C9*D3:D9,C3:C9),IF(C3:C9*D3:D9,D3:D9))
confirmed with CTRL + SHIFT + ENTER

My understanding, I would love to be corrected (I note I'm now a 'forum guru' and think this is hardly justified, so let's prove why I shouldn't be...)

=correl(if(c3:c9,c3:c9),if(d3:d9,d3:d9))

from help:
If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

Of course, you can test it.
1 | =a1^2
2 | "
3 | "
4 | "
5 | "
(quadratic relationship -> every pairing must deviate from linear, hence removing any pairing changes correl result)

correl on ranges
delete 4, number changes, replace 4, delete =a4^2, correl = same as when 4 was deleted

This makes me think (with aid of help) that a blank in either x or y at any array point excludes the array pairing from correl (after all, what is the 'other' number correlating to???)

That is why if OP had:
1 | blank
blank | 2
3 | blank
blank | 4
5 | 5

would return div0 as only 1 pair of numbers to correlate - which is what i was alluding to in post 4

Based on this logic, this would also work:
=correl(if(d3:d9,c3:c9),if(c3:c9,d3:d9))
or indeed
=correl(if((c3:c9*d3:d9),c3:c9),d3:d9)
(which is my new favourite answer)

Your correction is very welcome

You're correct - for some reason I was basing my reply on OP's post of:

Please Login or Register  to view this content.

rather than your suggestion ... the above obviously has a different outcome but you had already accounted for that in your first response - apologies.

I would say your Guru status is well deserved CC (and overdue) - next stop - Moderator !