Calculating R-squared while ignoring zeros and #N/A

[L.LEE]

Hi

I have a range of data from which I want to calculate r-squared and slope. However, some of the data are zeros or #N/A. How do I calculate r-squared and slope of the non-zero numbers only?

thanks.

[MrShorty]

The help files for these functions (rsq() https://support.office.com/en-us/art...d-a8364f2be08f ) indicate that these functions will ignore boolean or text values. The usual approach, then, is to replace your N/A and 0 values with a text or boolean value.

1) If the current set of values are being generated by some function, then next the current function inside of an IF() function that will replace 0 and N/A errors with something ignored: =IFNA(IF(currentformula=0,"text",currentformula),"text").
2) Using a helper column, enter something like =IFERROR(IF(value=0,"text",value),"text"), then use the helper column in your SLOPE() and RSQ() functions.
3) Use an array function, where you use something like 2 inside of the SLOPE() and RSQ() function =SLOPE(IFERROR(...)) (confirm with ctrl-shift-enter).

One consideration: there is duplicated effort in both 1 and 3 that may slow calculation down if this is a large data set. 2 might be preferable if this slows the spreadsheet down too much.

[ranman256]

if cell =#NA, and is part of the formula,then make another cell that translates NA to 0.

if B2 = #NA, then set C2 :=IFNA(B2,0)
run slope on c (on col c for all cells)