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

How to do this calculation ?

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


Joined: 14 Sep 2006
Posts: 48
Location: Hong Kong

PostPosted: Mon Oct 09, 2006 8:26 am    Post subject: How to do this calculation ? Reply with quote

I would like to do the following.

In sheet 1, A1 is the sum of all entries in sheet 2, colum 1, (say, A1 to A10), however, I will add one more items periodically, say, A11 one month later, A12 six months later. How can I make it add to the last item in sheet 2 column 1 automatically ?

Thank you !
_________________
I hate MsOffice becasue it's dear. I like OpenOffice becasue it is FREE.
Back to top
View user's profile Send private message
Schadeboy
General User
General User


Joined: 09 Oct 2006
Posts: 5
Location: Sierra Vista, AZ

PostPosted: Mon Oct 09, 2006 9:57 am    Post subject: Reply with quote

In Excel, you can do a SUM function on an entire column. The formula looks like this:

=SUM(Sheet2!A:A)

Just substitute the word Sheet for the actual name of the worksheet. I'm not sure if the syntax is precisely correct in Calc, though, so you might want to play around with it.
_________________
-Schadeboy

"Perhaps, if I am very lucky, the feeble efforts of my lifetime will someday be noticed, and maybe, in some small way, they will be acknowledged as the greatest works of genius ever created by Man." -- Jack Handy
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Oct 09, 2006 2:35 pm    Post subject: Reply with quote

Schadeboy wrote:
In Excel, you can do a SUM function on an entire column. The formula looks like this:

=SUM(Sheet2!A:A)

Just substitute the word Sheet for the actual name of the worksheet. I'm not sure if the syntax is precisely correct in Calc, though, so you might want to play around with it.


Nope, the entire column has to be addressed as Sheet2.A1:A65536
Calc's sheet-spearator is "."(dot). "!" is the intersect-operator.
The rules of relative/absolute addressing apply to sheets as well, so you may want $Sheet2.A1:A65536 or even $Sheet2.$A$1:$A$65536
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
wlaw
General User
General User


Joined: 14 Sep 2006
Posts: 48
Location: Hong Kong

PostPosted: Mon Oct 09, 2006 4:02 pm    Post subject: Reply with quote

Thank you for your information, I've tried but it didn't work ! Why ? Can you try it in your Cal to see if it works or not ?

Thank you !
_________________
I hate MsOffice becasue it's dear. I like OpenOffice becasue it is FREE.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Oct 09, 2006 6:27 pm    Post subject: Reply with quote

wlaw:

Yes, it does work for me . . . as described by Villeroy.

To be specific, . . . if you use the formula:
Code:
=SUM($Sheet2.$A$1:$A$65536)

So, wherever you place the formula . . . it will SUM all the values in Sheet2, from the range, A1 through and including A65536.

I hope this helps, please be sure to let me / us know.

Sliderule
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Oct 10, 2006 7:55 am    Post subject: Reply with quote

To be even more specific:
In OOo1.x we have only 32000 rows and should use $Sheet2.$A$1:$A$32000

Quote:
Thank you for your information, I've tried but it didn't work ! Why ? Can you try it in your Cal to see if it works or not ?

This statement is very unspecific.
Does the program crash?
Do you get an error-value? Which one?
Do you get an unexpected result?

Let the program do the input:
Start your formula =SUM(
Select Sheet2.A1 and hit Ctrl+Shift+down until you reach the bottom.
The formula bar shows =SUM(Sheet2.A1:A65536
Now you may hit Shift+F4 in order to convert this relative reference to =SUM($Sheet2.$A$1:$A$65536
Hit enter (a single missing brace at the end is closed automtically
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Schadeboy
General User
General User


Joined: 09 Oct 2006
Posts: 5
Location: Sierra Vista, AZ

PostPosted: Tue Oct 10, 2006 8:46 am    Post subject: Reply with quote

Yeah, I didn't expect the syntax to be exact. But the concept is the same, anyway. This assumes that you won't ever have to put anything else in that same column, too.
_________________
-Schadeboy

"Perhaps, if I am very lucky, the feeble efforts of my lifetime will someday be noticed, and maybe, in some small way, they will be acknowledged as the greatest works of genius ever created by Man." -- Jack Handy
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Oct 10, 2006 9:06 am    Post subject: Reply with quote

Schadeboy wrote:
Yeah, I didn't expect the syntax to be exact. But the concept is the same, anyway. This assumes that you won't ever have to put anything else in that same column, too.

Right, you should not do that anyway.
But consider this one:
Having a list with headers in first row and consecutive contents in B2:Bxxx, you can sum up column A by finding the first empty cell in B:
=SUM(OFFSET($A$1;1;0;MATCH("";$B$2:$B$65536;0);1))
=OFFSET($A$1;1;0) gives cell A2
resized by rows: MATCH("";$B$2:$B$65536;0), which is 99 if first empty cell is B100.
See help on OFFSET() and MATCH()
Without header row:
=SUM(OFFSET($A$1;0;0;MATCH("";$B$1:$B$65536;0);1))
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
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