[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
brasse
Newbie

Joined: 06 Jan 2005
Posts: 1

 Posted: Thu Jan 06, 2005 10:03 am    Post subject: Sum of dynamic range Hello! I am a bit of a spreadsheet newbie, but here goes. If I want to calculate the sum of a dynamic number of cells, how would I do that? In cell AX I want to calculate the sum of cells in the range A2 to A(X - 2). Does this make any sense? Can it be done? :.:: brasse
dfrench
Moderator

Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

 Posted: Thu Jan 06, 2005 12:55 pm    Post subject: You can calculate the range; express it as a string ( text) and use the indirect function =SUM(INDIRECT("A2:A"&X-2)) where X is a named cell (or value) greater than 3
jimlenn
General User

Joined: 01 Apr 2003
Posts: 39
Location: The cold, windswept, and great, upper Great Plains, USA.

Posted: Fri Jan 07, 2005 8:48 am    Post subject:

 dfrench wrote: You can calculate the range; express it as a string ( text) and use the indirect function =SUM(INDIRECT("A2:A"&X-2)) where X is a named cell (or value) greater than 3

Can't you also define a dynamic range using the OFFSET function and then simply sum the range?

In the range definition use something like the following:

OFFSET(\$B\$6;0;0;COUNT(\$B\$6:\$B\$1000);1)

Then simply use SUM(Name)

This would sum range of any length starting in cell B6 and extending to B1000.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum