Calculating R-squared for selective data

**L.LEE** · 12-23-2016, 10:21 PM

Hi

I have a range of numbers that sometimes have zero values in between. Is there a formula or automatic method to calculate R-squared only for the non-zero values? I attach my file. Thanks.

**MrShorty** · 12-23-2016, 11:27 PM

As with many other Excel functions, the built in RSQ() function (https://support.office.com/en-us/art...d-a8364f2be08f ) ignores text values. The most common approach I see, then, is to add helper columns or an array function that replaces the zero values with a text string. Since I prefer helper columns a lot of the time, I would:

1) Add helper columns with something like =IF(value=0,"text",value)
2) Enter the RSQ() function. =RSQ(helper_y,helper_x)