Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / May 2008

Tip: Looking for answers? Try searching our database.

Sum with Array Formula

Thread view: 
Enable EMail Alerts  Start New Thread
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.