OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Question about Variables and Specifying Cell Address

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Majestyk
Newbie
Newbie


Joined: 20 Oct 2008
Posts: 3

PostPosted: Mon Oct 20, 2008 4:41 pm    Post subject: Question about Variables and Specifying Cell Address Reply with quote

I need to allow a user to specify two numbers, so we'll call them var1 and var2. I need to do a manipulation on each number, which results in another number. Once that is done, I need to do a SUM of a series of rows bounded by those two numbers.

In other words, I need to do something like:

=SUM(B(var1):B(var2))

Where the cell reference is literally B (which is the column where a long series of numbers is located) and one or the other number I generated from the user-solicited variable. The literal output would be something like:

=SUM(B20:B30)

But I need to specify a number in place of the 20 or 30 that is generated from user inputs. What is the best way to achieve this?
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Mon Oct 20, 2008 8:52 pm    Post subject: Reply with quote

The OFFSET() function allows you to specify a range to be used in another function such as SUM().

Something like:

=SUM(OFFSET(....))

No time to work out the details - work calls Crying or Very sad
Back to top
View user's profile Send private message
bobban
OOo Enthusiast
OOo Enthusiast


Joined: 02 Jan 2008
Posts: 172
Location: Australia

PostPosted: Mon Oct 20, 2008 9:30 pm    Post subject: Reply with quote

This should get you started:

Assume the user input var1 is in cell F4 and var2 is in cell G4. Your data is in column C and you need to make a Sum from this. This will do the trick:

=SUM(INDIRECT(ADDRESS(F4;3)):INDIRECT((ADDRESS(G4;3))))

What that is doing is making an ADDRESS (as text), by getting the row value for the address from F4, and fixing the column value for the address as '3'(or column C). So say the user input 8 into the var1 cell, the result would be $C$8, and this is your lower bound. similarly, if var2 was input as 17, then the second ADDRESS would be $C$17. INDIRECT is simply returns the reference specified by a text string, which is what was made by ADDRESS. The SUM I am sure you know. Not sure if this is the best but it works well for me so far. Razz

Edited: Originally I said 'you data in column' B, and then used '3'(or column C) as the second argument in the ADDRESS function. This should have 'your data in column C', so I just changed that.
_________________
nil sine labore


Last edited by bobban on Mon Oct 20, 2008 10:49 pm; edited 1 time in total
Back to top
View user's profile Send private message
Majestyk
Newbie
Newbie


Joined: 20 Oct 2008
Posts: 3

PostPosted: Mon Oct 20, 2008 10:16 pm    Post subject: Reply with quote

Excellent. I will work with that and see what I get. Thanks for the help!
Back to top
View user's profile Send private message
Majestyk
Newbie
Newbie


Joined: 20 Oct 2008
Posts: 3

PostPosted: Wed Oct 22, 2008 10:15 pm    Post subject: Reply with quote

That worked perfectly. Thanks a lot.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group