Bob Phillips wrote...
Whilst we know that COLUMN and ROW always return arrays, that is often a
useful situation that can be exploited in a formula. That array can be used
to force OFFSET to pass an array to another function. As you say, OFFSET
returns an undocumented result that functions like an array of range
references, not a true array, but other functions can use that array of
range references, N is one such , SUBTOTAL is another. SUM may work in this
case (although how you worked out that the OP needed to SUM them is beyond
me <G>), but it won't always be appropriate. For instance
Functions that *expect* range references rather than arbitrary type
arguments can use arrays of range references. SUMIF and COUNTIF are the
prime examples. N can also, *but* N is a legacy function meant to
simulate the behavior of Lotus 123's @N function, so when given a range
reference or an array of range references, it returns *only* the value
of the 1st cell in each range reference rather than the values of all
cells in the range references. Therefore, N(OFFSET(...)) is only useful
when each of the range references in the apparent array returned by
offset is single cell. That's not the case with the OP's OFFSET calls,
which return a *single* range reference that happens to span 43 rows,
so wrapping it in N would return only the value of the 1st cell in that
single 43 row range.
Why SUM handles this is that it's the simplest (and shortest) way to
convert a single item array into a scalar. N(COLUMN(A1)) still returns
{1}, but SUM(COLUMN(A1)) returns 1 [as does the arguably clearer
INDEX(COLUMN(A1),1,1)].
SUM(COLUMN(C1)-COLUMN(A1:B1))
Fair point when the COLUMN(x)-COLUMN(y) expression returns a multilpe
item array. *BUT* you'd still get a hash using
N(OFFSET(r,1,COLUMN(x)-COLUMN(y),43,))
There'd be NO POINT WHATSOEVER to the 4th and 5th args to OFFSET being
anything other than 1 for both. That is, the following is an immutable
identity.
N(OFFSET(r,1,a,43,)) == N(OFFSET(r,1,a,1,1))
where a is COLUMN(x)-COLUMN(y). I guessed that the OP's
COLUMN(x)-COLUMN(y) expression returned a single value and all that was
needed was converting it into a scalar. Looks like I guessed right.
So whilst SUM may work for the OP here, I wouldn't want him to think that is
always the way.
Agreed, but the OP's incorrect formula boils down to
=SUMPRODUCT((OFFSET(r,1,,43,)=a)*OFFSET(r,1,COLUMN(x)-COLUMN(y),43,))
When COLUMN(x)-COLUMN(y) returns a multiple item array this would only
make sense (to me at least) if the range reference r refers to a single
column range. In that case, the second OFFSET call is ultimately meant
to return a nontrivial 2D array, in which case
=SUMPRODUCT((OFFSET(r,1,,43,)=a)
*OFFSET(r,1,SUM(COLUMN(x)-COLUMN(INDEX(y,1,1))),43,COLUMNS(y)))
which would still not need an N function call. N would only be needed
if you rewrote the formula as
=SUMPRODUCT((OFFSET(r,1,,43,)=a)
*N(OFFSET(r,ROW(1:43),COLUMN(x)-COLUMN(y),1,1)))
Note that making the 2nd arg to the 2nd OFFSET call an array would be
*MANDATORY*.
