Hi everyone
I am trying to write a dynamic RSQ equation where some of the arguments are called in by the INDIRECT function. I have no problem using INDIRECT with a one array function (like sum), for example,
(1/H7)*SUM(INDIRECT("E"&H3&":E"&H4))
but I can't get this to work with 2.
So the basic equation is: =RSQ(A:B, X:Y), where A,B,X,Y are cell references.
(i) Term A will always be fixed at the actual cell $E$8
(ii) What I need term B to be is $E$Variable 1 (where variable 1, the row number, will always be in cell A4 -- but changes frequently depending on what I am doing)
(iii) Term X: here the column will change as I go across the spreadsheet (F, G, H, I, etc.), but the row number will always start with 8.
(iv) Y will also have the same column (F, G, H, I, etc.) as X, but the row number to go along with it has to be the number in cell 4.
Here is what I tried: =RSQ(INDIRECT("$E$8"&":$E$"&$A$4&",G$8"&":G"&$A$4))
Thanks for any suggestions.
I am trying to write a dynamic RSQ equation where some of the arguments are called in by the INDIRECT function. I have no problem using INDIRECT with a one array function (like sum), for example,
(1/H7)*SUM(INDIRECT("E"&H3&":E"&H4))
but I can't get this to work with 2.
So the basic equation is: =RSQ(A:B, X:Y), where A,B,X,Y are cell references.
(i) Term A will always be fixed at the actual cell $E$8
(ii) What I need term B to be is $E$Variable 1 (where variable 1, the row number, will always be in cell A4 -- but changes frequently depending on what I am doing)
(iii) Term X: here the column will change as I go across the spreadsheet (F, G, H, I, etc.), but the row number will always start with 8.
(iv) Y will also have the same column (F, G, H, I, etc.) as X, but the row number to go along with it has to be the number in cell 4.
Here is what I tried: =RSQ(INDIRECT("$E$8"&":$E$"&$A$4&",G$8"&":G"&$A$4))
Thanks for any suggestions.