Creating conditional formulas where zeros need to be excluded?

[spennis]

Hi,

Is there anyway of creating conditional formulas where zeros need to be excluded? I want to use CORREL (and similar functions) along with multiple criteria, such as:
=CORREL(IF(--("range A"="criterion A")--("range B"="criterion B");"range C");"range D")

i.e. I want to correlate two ranges, and exclude all values that do not meet my criterion. However, as CORREL depend on whether cells are blank or 0, I can't use arrays as they return 0 when the criterion are not met, can I?

 

[DRSteele]

Welcome to the forum.

I might be wrong here, but I think I have some insight for function CORREL. Excel Help says "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." Accordingly, your IF statement above needs a FALSE argument: put this in the false argument "" That will return blanks if the criterion are not met, and CORREL will dutifully ignore them.