Ignore #N/A in the Correl function

[jeffreybrown]

I have a scatter diagram which I want to allow the user to remove data points from the chartted data.

My Y's are in column B and the X's in column C with a Worksheet_BeforeDoubleClick event on column D. DoubleClick in column D and the values in columns C/D change into #N/A so the values will not plot on the chart.

The formula below will ignore zero's, but can't seem to get it to work with the #N/As. Actually there will never be a zero in the data, only an #N/A that needs to be excluded.

CSE
=CORREL((IF(B2:B13<>0,B2:B13,"")),(IF(C2:C13<>0,C2:C13,"")))

 

[AlphaFrog]

Try this...

CSE
=CORREL((IF(ISERROR(B2:B13),"",B2:B13)),(IF(ISERROR(C2:C13),"",C2:C13)))

 

[T. Valko]

I have a scatter diagram which I want to allow the user to remove data points from the chartted data.

My Y's are in column B and the X's in column C with a Worksheet_BeforeDoubleClick event on column D. DoubleClick in column D and the values in columns C/D change into #N/A so the values will not plot on the chart.

The formula below will ignore zero's, but can't seem to get it to work with the #N/As. Actually there will never be a zero in the data, only an #N/A that needs to be excluded.

CSE
=CORREL((IF(B2:B13<>0,B2:B13,"")),(IF(C2:C13<>0,C2:C13,"")))

I'm not a "stats" person so I've never had to use the CORREL function...

Seems like ISNUMBER(range) should work to exclude anything that is non-numeric.

 

[jeffreybrown]

Perfect AlphaFrog...Thanks

 

[AlphaFrog]

You're welcome.

Alternatively, you could eliminate the errors from within the B2:C13 formulas.

 

[jeffreybrown]

@Biff
Thanks for the input...I was able to get this to work based on your input...

CSE
=CORREL((IF(ISNUMBER(B2:B13),B2:B13)),(IF(ISNUMBER(C2:C13),C2:C13)))

@AlphaFrog
Your right, I just tested instead of the #N/A to mask the data point on the chart...blanking the data point out works as well.

 

[T. Valko]

@Biff
Thanks for the input...I was able to get this to work based on your input...

CSE
=CORREL((IF(ISNUMBER(B2:B13),B2:B13)),(IF(ISNUMBER(C2:C13),C2:C13)))

If the errors are ALWAYS only the #N/A error then this will also work and is the shortest of all the suggestions:

=CORREL((IF(ISNA(B2:B13),"",B2:B13)),(IF(ISNA(C2:C13),"",C2:C13)))

However, only the ISNUMBER version will exclude empty cells (if any might exist).