RSQ Visible Cells

[douge]

Does anyone know how to make Excel calculate RSQ only on visible cells
from a filtered list? RSQ() by itself calculates based on the entire
set of data even after filtering and SUBTOTAL() doesn't include
options for RSQ, PEARSON etc.

Thanks

 

[Aladin Akyurek]

=RSQ(IF(SUBTOTAL(3,OFFSET(B2:B8,ROW(B2:B8)-MIN(ROW(B2:B8)),,1)),B2:B8),IF(SU
BTOTAL(3,OFFSET(B2:B8,ROW(B2:B8)-MIN(ROW(B2:B8)),,1)),C2:C8))

which needs to be confirmed with control+shift+enter instead of just with
enter.

The above is applied to A1:C9, where row 1 has labels and the area is
AutoFiltered using a condition that applies to the A-range.

Note that the SubTotal idiom is due to Laurent Longre.

 

[douge]

Thanks Aladin, works great! One day I may be able to figure out how ;-)