Sum with Array Formula

  • Thread starter Thread starter Gary''s Student
  • Start date Start date
G

Gary''s Student

I am trying to get an array formula to sum up a set of terms.

=OFFSET(cll,0,1)+OFFSET(cll,0,2)+OFFSET(cll,0,3) where cll is a Named Range

=SUM(OFFSET(cll,0,{1,2,3})) does not work
 
.. why is N() necessary??

Perhaps this is best explained by quoting from a past posting by Harlan:

".. OFFSET only returns Range references. OFFSET called with array 1st, 2nd
or 3rd arguments returns something what seems to be an array of range
references. Excel can't deal with such beasts when used as arithmetic
operands or arguments to most functions. Fortunately, N() is one of the
exceptions, and it effectively converts arrays of range references to
arrays of numbers (note: it converts entries that aren't numeric into
numeric zero). The T() function does the same for strings ... "

An interesting obs about using the earlier struct
is that it allows the omission of an intervening col in the sum, eg:
=SUM(N(OFFSET(cll,0,{1,3})))
will omit the 2nd col's values in the sum,

This is probably not possible with using the width param (daddy's
suggestion)
 
Thank you.
--
Gary''s Student - gsnu200787


Max said:
Perhaps this is best explained by quoting from a past posting by Harlan:

".. OFFSET only returns Range references. OFFSET called with array 1st, 2nd
or 3rd arguments returns something what seems to be an array of range
references. Excel can't deal with such beasts when used as arithmetic
operands or arguments to most functions. Fortunately, N() is one of the
exceptions, and it effectively converts arrays of range references to
arrays of numbers (note: it converts entries that aren't numeric into
numeric zero). The T() function does the same for strings ... "

An interesting obs about using the earlier struct
is that it allows the omission of an intervening col in the sum, eg:
=SUM(N(OFFSET(cll,0,{1,3})))
will omit the 2nd col's values in the sum,

This is probably not possible with using the width param (daddy's
suggestion)
Click to expand...
 
Back
Top