Calc RSQ and exclude zeros

[Bruce]

I want to do an array formula for the RSQ function so that it excludes zero
values in the calc.. The RSQ has 2 arguments, 1st for y's and 2nd for x's.

My problem is how to do an array formula with 2 arguments.

If I wanted to calc the STDEV ( a 1 argument function ) and exclude zero's I
would enter;

{=STDEV(IF($J11:$AG11<>0,$J11:$AG11))}

For RSQ's normal usage I would enter;

=RSQ(J11:AG11,J7:AG7)

How do I adjust the formula above to exclude zero values?

Bruce

[vezerid]

You want to exclude entries when BOTH x and y are 0? When either is 0?
Given the nature of the RSQ function this might not make much sense...

But anyway, just replace J11:AG11 with IF($J11:$AG11<>0,$J11:$AG11) in
your formula.

HTH
Kostis Vezerides

[Bruce]

Thanks vezerid,

You make a very good point i did not consider.

I wanted to exclude zero values for Y (which i consider anomolies in the
dataset). However this cannot work as each number of variables in x and y
needs to be equal (corresponding).

x in my dataset is an independant variable being time which is measured as
the period being 1 to 24. The data for y is also 24 values but contain zero's.

If y is found to be zero I figure now I will need to also exclude to
corresponding Y value for that data point (which will not be zero).

Can this be done?

Bruce

"vezerid" wrote:

> You want to exclude entries when BOTH x and y are 0? When either is 0?
> Given the nature of the RSQ function this might not make much sense...
>
> But anyway, just replace J11:AG11 with IF($J11:$AG11<>0,$J11:$AG11) in
> your formula.
>
> HTH
> Kostis Vezerides
>
>

[vezerid]

Sure it can be done,
you just need to have the same condition in the two IF()'s, both
looking at y, and then supplying the x's or y's in the second argument.
This will guarantee arrays of similar length and it will exclude y=0:

=RSQ(IF($J11:$AG11<>0,$J11:$AG11), IF($J11:$AG11<>0,$J7:$AG7))

(with x's in row 11 and y's in row 7)

Regards,
Kostis