Covariance function to ignore #n/a pairs

Hey guys,

I'm attempting to use the =COVARIANCE.S function in excel, but I'm getting a #n/a because there are n/as in A:A and B:B but in different places, so in other words each array has a different number of data points.

Can anyone think of a formula that will modify the covariance function so that an #n/a in A:A will ignore both A:A and B:B for that data point? I.e. excel will ignore any row that has n/a in either A or B, and therefore make the number of data points the same.

It would be considerably easier to prevent the #N/A than to circumnavigate the restrictions on CONVAR.

What formula is generating the error?

Hi

This is the formula that generate the #N/A:
=IF(ISNA(IF(ISNUMBER($A3),VLOOKUP($A3,TRYRR,2,FALSE))),#N/A,VLOOKUP($A3,TRYRR,2,FALSE))

The only other option is to have a blank instead of an #n/a, but that doesn't fix the fact that the two arrays have different data points.

Should have before I posted! The formula produced a number, but I'm not sure it's correct--do you know if it's doing what I want, i.e. ignoring both A and B with a blank is found in either one?

when** a blank is found in either one

Yes, it works