INDIRECT with two array RSQ formula

runway9r

New Member
Joined
Mar 21, 2011
Messages
10
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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,833
Office Version
  1. 2010
Platform
  1. Windows
How about ...

=RSQ($E$8:INDEX($E:$E, $A$4), F$8:INDEX(F:F, $A$4))
 

runway9r

New Member
Joined
Mar 21, 2011
Messages
10
How about ...

=RSQ($E$8:INDEX($E:$E, $A$4), F$8:INDEX(F:F, $A$4))

shg: I truly hope I can help you out some day. But after my first few days participating in this forum, I am pretty sure it won't be with excel. Obviously, thanks - I get stuck thinking I am going at the problem the right way and don't always see the bigger set of options. Works like a charm.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,833
Office Version
  1. 2010
Platform
  1. Windows
Code:
... I am pretty sure it won't be with excel.
Thank you. :ROFLMAO:

It actually could be written using INDIRECT, but INDIRECT is intrinsically volatile, so I avoid it unless there's no other way.
 

runway9r

New Member
Joined
Mar 21, 2011
Messages
10

ADVERTISEMENT

Code:
... I am pretty sure it won't be with excel.
Thank you. :ROFLMAO:

It actually could be written using INDIRECT, but INDIRECT is intrinsically volatile, so I avoid it unless there's no other way.

When you have a chance and feel like it, could you explain what and why it is volitile?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,833
Office Version
  1. 2010
Platform
  1. Windows
Maybe "intrinsically" is too strong a word.

If A1 contains the text B1:B4, then the formula

=SUM(INDIRECT(A1))

... returns the sum of B1:B4. Excel can see that the formula depends on the contents of A1, but not on B1:B4. You could argue that Excel should be able to, but it would be very complex for the general case. For example, the value in A1 could itself be the result of a formula, e.g., ="B1:B" & SUM(C1:C3)

So Excel punts and recalculates the formula any time it calculates anything.

Prefer to avoid the use of OFFSET in favor of INDEX for the same reason.
 
Last edited:

runway9r

New Member
Joined
Mar 21, 2011
Messages
10
Maybe "intrinsically" is too strong a word.

If A1 contains the text B1:B4, then the formula

=SUM(INDIRECT(A1))

... returns the sum of B1:B4. Excel can see that the formula depends on the contents of A1, but not on B1:B4. You could argue that Excel should be able to, but it would be very complex for the general case. For example, the value in A1 could itself be the result of a formula, e.g., ="B1:B" & SUM(C1:C3)

So Excel punts and recalculates the formula any time it calculates anything.

Prefer to avoid the use of OFFSET in favor of INDEX for the same reason.

Thanks, got it. - Looks like this week's learning goal will be to better develop my skills with INDEX !
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,945
Members
430,329
Latest member
asmith75

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top