Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calc RSQ and exclude zeros
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calc RSQ and exclude zeros
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calc RSQ and exclude zeros
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calc RSQ and exclude zeros
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|