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.
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.