VBA formula for correlation coefficient between two variables where one of the variables is the sumproduct of two other variables

Question

I need to calculate the correlation coefficient between two variables where one of them is the sumproduct of two other variables. In the following example I need to calculate CORREL which is the correlation coefficient between SUMPR and X. SUMPR is the sumproduct of values A and B with each of the values Y1 and Y2.

Is there a way I can find CORREL without having to calculate SUMPR first? In other words, can I pass SUMPR in the correlation coefficient formula as a variable? I need to do that for very large dynamic tables and calculating SUMPR first takes a lot of space and time. Thank you

Answer 1

Try this array formula as oer screenshot-

=CORREL($A$2*D2:D4+$B$2*E2:E4,H2:H4)

Press CTRL+SHIFT+ENTER to evaluate the formula as it is an array formula.

If you have Office365 then it will work as normal without array entry.