Filtering with SUMPRODUCT formula

[Russet]

I have the below formula for calculating the root mean square error of the difference of two columns of data:

=SQRT(SUMPRODUCT((C2:C180-B2:B180)^2)/COUNT(B2:B180))

I would like to calculate this for filtered rows as well. I know SUBTOTAL needs to be used, and think the solution involves OFFSET, but haven't been able to come up with a formula that works. Any help would be much appreciated.

[Domenic]

Try...

=SQRT(SUMPRODUCT(SUBTOTAL(2,OFFSET(B2:B180,ROW(B2:B180)-ROW(B2),0,1)),(C2:C180-B2:B180)^2)/SUBTOTAL(2,B2:B180))

Hope this helps!

[Russet]

Worked like a charm. Thank you Domenic!

[Domenic]

Worked like a charm. Thank you Domenic!

You're very welcome!

Cheers!

[Russet]

post deleted