Multiple ranges in RSQ Function formula
up vote
0
down vote
favorite
I have two data sets to compare using the Excel RSQ Function. The first dataset comprises straightforward sequential cells in a row (A1:D1). The second dataset in the row below takes its final cell from a different reference in the spreadsheet(A2:C2,G12). Can't get the formula with 3 references to work. I seem to have tried all combinations and even read up on array formulas thinking this might help but no luck. Can this even be done?
Obviously the following doesn't work, but for clarity, this is kind of what I'm after:
=RSQ(A1:D1,(A2:C2,G12))
Any help much appreciated.
excel excel-formula
asked Nov 19 at 13:41
Silverburch
165218
add a comment |
up vote
0
down vote
favorite
I have two data sets to compare using the Excel RSQ Function. The first dataset comprises straightforward sequential cells in a row (A1:D1). The second dataset in the row below takes its final cell from a different reference in the spreadsheet(A2:C2,G12). Can't get the formula with 3 references to work. I seem to have tried all combinations and even read up on array formulas thinking this might help but no luck. Can this even be done?
Obviously the following doesn't work, but for clarity, this is kind of what I'm after:
=RSQ(A1:D1,(A2:C2,G12))
Any help much appreciated.
excel excel-formula
asked Nov 19 at 13:41
Silverburch
165218
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have two data sets to compare using the Excel RSQ Function. The first dataset comprises straightforward sequential cells in a row (A1:D1). The second dataset in the row below takes its final cell from a different reference in the spreadsheet(A2:C2,G12). Can't get the formula with 3 references to work. I seem to have tried all combinations and even read up on array formulas thinking this might help but no luck. Can this even be done?
Obviously the following doesn't work, but for clarity, this is kind of what I'm after:
=RSQ(A1:D1,(A2:C2,G12))
Any help much appreciated.
excel excel-formula
asked Nov 19 at 13:41
Silverburch
165218
I have two data sets to compare using the Excel RSQ Function. The first dataset comprises straightforward sequential cells in a row (A1:D1). The second dataset in the row below takes its final cell from a different reference in the spreadsheet(A2:C2,G12). Can't get the formula with 3 references to work. I seem to have tried all combinations and even read up on array formulas thinking this might help but no luck. Can this even be done?
Obviously the following doesn't work, but for clarity, this is kind of what I'm after:
=RSQ(A1:D1,(A2:C2,G12))
Any help much appreciated.
excel excel-formula
excel excel-formula
asked Nov 19 at 13:41
Silverburch
165218
asked Nov 19 at 13:41
Silverburch
165218
asked Nov 19 at 13:41
Silverburch
165218
asked Nov 19 at 13:41
Silverburch
165218
asked Nov 19 at 13:41
Silverburch
165218
165218
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
up vote
1
down vote
accepted
You don't mention whether this needs to be dynamic or not, so we can use a static array construction:
=RSQ(A1:D1,IF({1,1,1,0},A2:C2,G12))
Note that, if you're not using an English language-version of Excel, the separator within the array constant (here a comma) may require changing.
Regards
answered Nov 19 at 14:10
XOR LX
7,1141913
add a comment |
up vote
1
down vote
Try to replace (A2:C2,G12) with CHOOSE({1;2;3;4},A2,B2,C2,G12).
For completeness, another method. Replace with :N(INDIRECT({"A2";"B2";"C2";"G12"})).
answered Nov 19 at 14:15
Peter K.
4899
Thanks. Another, alternative working solution
– Silverburch
Nov 19 at 14:47
I added a second method to my answer.
– Peter K.
Nov 19 at 16:00
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
You don't mention whether this needs to be dynamic or not, so we can use a static array construction:
=RSQ(A1:D1,IF({1,1,1,0},A2:C2,G12))
Note that, if you're not using an English language-version of Excel, the separator within the array constant (here a comma) may require changing.
Regards
answered Nov 19 at 14:10
XOR LX
7,1141913
add a comment |
up vote
1
down vote
accepted
You don't mention whether this needs to be dynamic or not, so we can use a static array construction:
=RSQ(A1:D1,IF({1,1,1,0},A2:C2,G12))
Note that, if you're not using an English language-version of Excel, the separator within the array constant (here a comma) may require changing.
Regards
answered Nov 19 at 14:10
XOR LX
7,1141913
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
You don't mention whether this needs to be dynamic or not, so we can use a static array construction:
=RSQ(A1:D1,IF({1,1,1,0},A2:C2,G12))
Note that, if you're not using an English language-version of Excel, the separator within the array constant (here a comma) may require changing.
Regards
answered Nov 19 at 14:10
XOR LX
7,1141913
You don't mention whether this needs to be dynamic or not, so we can use a static array construction:
=RSQ(A1:D1,IF({1,1,1,0},A2:C2,G12))
Note that, if you're not using an English language-version of Excel, the separator within the array constant (here a comma) may require changing.
Regards
answered Nov 19 at 14:10
XOR LX
7,1141913
edited Nov 19 at 14:16
answered Nov 19 at 14:10
XOR LX
7,1141913
answered Nov 19 at 14:10
XOR LX
7,1141913
answered Nov 19 at 14:10
XOR LX
7,1141913
7,1141913
add a comment |
add a comment |
up vote
1
down vote
Try to replace (A2:C2,G12) with CHOOSE({1;2;3;4},A2,B2,C2,G12).
For completeness, another method. Replace with :N(INDIRECT({"A2";"B2";"C2";"G12"})).
answered Nov 19 at 14:15
Peter K.
4899
Thanks. Another, alternative working solution
– Silverburch
Nov 19 at 14:47
I added a second method to my answer.
– Peter K.
Nov 19 at 16:00
add a comment |
up vote
1
down vote
Try to replace (A2:C2,G12) with CHOOSE({1;2;3;4},A2,B2,C2,G12).
For completeness, another method. Replace with :N(INDIRECT({"A2";"B2";"C2";"G12"})).
answered Nov 19 at 14:15
Peter K.
4899
Thanks. Another, alternative working solution
– Silverburch
Nov 19 at 14:47
I added a second method to my answer.
– Peter K.
Nov 19 at 16:00
add a comment |
up vote
1
down vote
up vote
1
down vote
Try to replace (A2:C2,G12) with CHOOSE({1;2;3;4},A2,B2,C2,G12).
For completeness, another method. Replace with :N(INDIRECT({"A2";"B2";"C2";"G12"})).
answered Nov 19 at 14:15
Peter K.
4899
Try to replace (A2:C2,G12) with CHOOSE({1;2;3;4},A2,B2,C2,G12).
For completeness, another method. Replace with :N(INDIRECT({"A2";"B2";"C2";"G12"})).
answered Nov 19 at 14:15
Peter K.
4899
edited Nov 19 at 16:03
answered Nov 19 at 14:15
Peter K.
4899
answered Nov 19 at 14:15
Peter K.
4899
answered Nov 19 at 14:15
Peter K.
4899
4899
Thanks. Another, alternative working solution
– Silverburch
Nov 19 at 14:47
I added a second method to my answer.
– Peter K.
Nov 19 at 16:00
add a comment |
Thanks. Another, alternative working solution
– Silverburch
Nov 19 at 14:47
I added a second method to my answer.
– Peter K.
Nov 19 at 16:00
Thanks. Another, alternative working solution
– Silverburch
Nov 19 at 14:47
Thanks. Another, alternative working solution
– Silverburch
Nov 19 at 14:47
I added a second method to my answer.
– Peter K.
Nov 19 at 16:00
I added a second method to my answer.
– Peter K.
Nov 19 at 16:00
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53375900%2fmultiple-ranges-in-rsq-function-formula%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
