Sum with Array Formula
 

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
--
Gary''s Student - gsnu200787

Sum with Array Formula
 

This seems ok, normal Enter:
=SUM(N(OFFSET(cll,0,{1,2,3})))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gary''s Student" wrote:
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
--
Gary''s Student - gsnu200787

Sum with Array Formula
 

What's cll, a single column?

Try

=SUM(OFFSET(cll,0,1,,3))



"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200787

Sum with Array Formula
 

Thanks - works just great - why is N() necessary??
--
Gary''s Student - gsnu2007xx


"Max" wrote:

This seems ok, normal Enter:
=SUM(N(OFFSET(cll,0,{1,2,3})))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gary''s Student" wrote:
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
--
Gary''s Student - gsnu200787

Sum with Array Formula
 

Thank you - very neat.
--
Gary''s Student - gsnu2007xx


"daddylonglegs" wrote:

What's cll, a single column?

Try

=SUM(OFFSET(cll,0,1,,3))



"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200787

Sum with Array Formula
 

.. 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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gary''s Student" wrote in message
...
Thanks - works just great - why is N() necessary??
--
Gary''s Student - gsnu2007xx

Sum with Array Formula
 

Thank you.
--
Gary''s Student - gsnu200787


"Max" wrote:

.. 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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gary''s Student" wrote in message
...
Thanks - works just great - why is N() necessary??
--
Gary''s Student - gsnu2007xx

Sum with Array Formula
 

Welcome, GS
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gary''s Student" wrote in message
...
Thank you.
--
Gary''s Student - gsnu200787