MS Office Forum / Excel / Worksheet Functions / May 2008
Sum with Array Formula
|
|
Thread rating:  |
Gary''s Student - 20 May 2008 00:42 GMT 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
 Signature Gary''s Student - gsnu200787
Max - 20 May 2008 00:53 GMT This seems ok, normal Enter: =SUM(N(OFFSET(cll,0,{1,2,3})))
 Signature Max Singapore http://savefile.com/projects/236895 xdemechanik ---
> 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 - 20 May 2008 01:30 GMT Thanks - works just great - why is N() necessary??
 Signature Gary''s Student - gsnu2007xx
> This seems ok, normal Enter: > =SUM(N(OFFSET(cll,0,{1,2,3}))) [quoted text clipped - 3 lines] > > > > =SUM(OFFSET(cll,0,{1,2,3})) does not work Max - 20 May 2008 06:36 GMT > .. 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)
 Signature Max Singapore http://savefile.com/projects/236895 xdemechanik ---
> Thanks - works just great - why is N() necessary?? Gary''s Student - 20 May 2008 11:20 GMT Thank you.
 Signature Gary''s Student - gsnu200787
> > .. why is N() necessary?? > [quoted text clipped - 16 lines] > suggestion) > > Thanks - works just great - why is N() necessary?? Max - 20 May 2008 11:40 GMT Welcome, GS
 Signature Max Singapore http://savefile.com/projects/236895 xdemechanik ---
> Thank you. daddylonglegs - 20 May 2008 00:54 GMT What's cll, a single column?
Try
=SUM(OFFSET(cll,0,1,,3))
> 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 - 20 May 2008 01:31 GMT Thank you - very neat.
 Signature Gary''s Student - gsnu2007xx
> What's cll, a single column? > [quoted text clipped - 7 lines] > > > > =SUM(OFFSET(cll,0,{1,2,3})) does not work
|
|
|