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

FORMULA FOR ENTIRE COLUMN

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


Joined: 17 Sep 2006
Posts: 3

PostPosted: Sun Sep 17, 2006 3:50 am    Post subject: FORMULA FOR ENTIRE COLUMN Reply with quote

I am not familiar with spreadsheets. I wanted to enter some salary particulars like data in a spreadsheet. Here coloumn 4 is total of figures in column 2 and 3. Likewise Column 7 is sum of 5 and 6, 8 is sum of 2 and 5. How to fill these columns automatically by adding?
Back to top
View user's profile Send private message
DiGro
Super User
Super User


Joined: 02 Jun 2004
Posts: 1415
Location: Hoorn NH, The Netherlands

PostPosted: Sun Sep 17, 2006 4:07 am    Post subject: Reply with quote

Well, that should be rather simple.

First though, some basics. If you are talking of columns please refer to them with their corresponding character, as are rows.

So, the cell in column 1 row 1 is reffered to as A1 (column A, row 1) This common rule makes life a lot easier for the most of us Laughing

Now your summing:
Since your talking about columns 2 and 3 I assume you want the values of columns B and C added together and show up in column D (I assume you want only the values on the same row ??) then it will be (let's take row 1) in D1 you type
Code:
=SUM(B1:C1)
or
Code:
=B1+C1


To sum a number of cells in only column B would be:
Code:
=SUM(B1:B34)

_________________
DiGro

Windows 7 Home Premium and AOO 4.0.1 NL (Dutch)
Back to top
View user's profile Send private message
kadal27
Newbie
Newbie


Joined: 17 Sep 2006
Posts: 3

PostPosted: Sun Sep 17, 2006 7:28 am    Post subject: FORMULA FOR ENTIRE COLUMN Reply with quote

Yes, you are correct. I need to sum the values in the same row.

I am having some 500 rows. Shall I key in =sum(b2:c2), =sum(b3:c3) etc. for each row or is there any other shortcut/formula for doing this?
Similarly can I sum 2 different cells in the same row but not adjacent?
This also for all the 500 rows!
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Sun Sep 17, 2006 7:49 am    Post subject: Reply with quote

Shortcut:
Once you have the correct formula in say D1 move the cursor to its lower right corner until its changes to a cross hair then click and drag down. The formula will automatically adjust itself.

Quote:
Similarly can I sum 2 different cells in the same row but not adjacent?
=A1+C1 and the same shortcut for 500 rows.

You defintely are new to spreadsheets Wink With the spreadsheet open press F1 and try the links to basic Calc help documents on the right.
Back to top
View user's profile Send private message
legatic
Newbie
Newbie


Joined: 16 Jul 2008
Posts: 1

PostPosted: Wed Jul 16, 2008 7:32 pm    Post subject: Reply with quote

I know how to copy the formula to multiple cells, but I'm having another problem

I have a sheet when I enter just a few numbers, but want them calculated in many ways, so I have lots of formula columns. this means any time I enter new numbers I have to copy 7 or 8 columns to get the formulas onto the new rows

if I copy many rows at once, it displays "#VALUE!" in the rows that I haven't entered data for yet, which looks bad. Is there any way to have it not do the formula if the cells it's supposed to calculate are blank, that way it will only try to do the formula when I put data on that row?

thanks
Back to top
View user's profile Send private message
uros
Super User
Super User


Joined: 22 May 2003
Posts: 601
Location: Slovenia

PostPosted: Wed Jul 16, 2008 10:45 pm    Post subject: Reply with quote

Hi legatic!
Use If function.
Let's say your data are in A:C and formulas in D:F. Formula in column D: =A2/B2.
If there is no data in B2, value of B2 equals 0 and your formula returns an error.
=IF(B2=0;"";A2/B2) will solve your problem... =IF(condition, if true, if false)
See help for more details.
Uros
Back to top
View user's profile Send private message
Herbivore
General User
General User


Joined: 29 Apr 2008
Posts: 7

PostPosted: Mon Sep 08, 2008 2:15 am    Post subject: [SOLVED] To operate on ALL values of one or more columns Reply with quote

Thanks for any assistance on this. Have done fair amount of searching but could not find an answer.

What if one wants to operate on ALL the current and subsequent values in a column? Normally one specifies a range (A1:A201). I would like to specify A*, as if "*" meant all values in column A.

Situation: Spreadsheet is serving as a day book. Each time there is a transaction, it is entered on a row under columns for TRANSACTION (A), DATE (B), FUNDS IN (C), FUNDS OUT (D), and BALANCE (E). The formula in cell E3, for example, is =E2+C3-D3. Cells in columns C, D, and E are formatted for currency.

Goal is to have a cell at the top (above where this data accumulates) which gives the current balance. This could be achieved if a formula could be written which subtracted the totals of column C from the totals of column D.

Even better would be to operate on all cells of a column below a specified cell.

The sheet is constantly receiving new transactions so a limited range of cells in a column is either going to be rendered invalid when the bottom of the range is passed or require choosing a distant endpoint such as C2500. If such a distant cell is referenced, the size of the sheet becomes huge and calculations or file operations slow way down.

As each sheet is for a particular account it would be possible with such a balance cell to post the current balance of each account to another sheet.

Any suggestions or even information that this is impossible, would be much appreciated.


Last edited by Herbivore on Tue Sep 09, 2008 2:21 am; edited 1 time in total
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Mon Sep 08, 2008 4:48 am    Post subject: Re: To operate on ALL values of one or more columns Reply with quote

Herbivore wrote:
Thanks for any assistance on this. Have done fair amount of searching but could not find an answer.

What if one wants to operate on ALL the current and subsequent values in a column? Normally one specifies a range (A1:A201). I would like to specify A*, as if "*" meant all values in column A.

Situation: Spreadsheet is serving as a day book. Each time there is a transaction, it is entered on a row under columns for TRANSACTION (A), DATE (B), FUNDS IN (C), FUNDS OUT (D), and BALANCE (E). The formula in cell E3, for example, is =E2+C3-D3. Cells in columns C, D, and E are formatted for currency.

Goal is to have a cell at the top (above where this data accumulates) which gives the current balance. This could be achieved if a formula could be written which subtracted the totals of column C from the totals of column D.

Even better would be to operate on all cells of a column below a specified cell.

The sheet is constantly receiving new transactions so a limited range of cells in a column is either going to be rendered invalid when the bottom of the range is passed or require choosing a distant endpoint such as C2500. If such a distant cell is referenced, the size of the sheet becomes huge and calculations or file operations slow way down.

As each sheet is for a particular account it would be possible with such a balance cell to post the current balance of each account to another sheet.

Any suggestions or even information that this is impossible, would be much appreciated.


I have not tried this but I'd suggest setting up a set of ranges for Column E there one range includes the entire colulmn and smaller ranges include the subsets of the data.

Then you can simply so a =sum(range.name) for each segment.

If you're accumulating this much data I would suggest that you look into a data base. It should be safer to use and can handle the large number of entries better.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2


Last edited by jrkrideau on Mon Sep 08, 2008 11:31 am; edited 1 time in total
Back to top
View user's profile Send private message
kadal27
Newbie
Newbie


Joined: 17 Sep 2006
Posts: 3

PostPosted: Mon Sep 08, 2008 5:59 am    Post subject: Thank you Reply with quote

Thank you
Back to top
View user's profile Send private message
Herbivore
General User
General User


Joined: 29 Apr 2008
Posts: 7

PostPosted: Tue Sep 09, 2008 2:19 am    Post subject: Re: Operations on ALL values of one or more columns Reply with quote

Thanks for suggestions to my question on how to operate on ALL values of one or more columns. I learned that the entire column can be identified by using, for example, A1:A65536. A cell with operations involving columns can then be referenced in other sheets. So in my example above, the balance cell formula for both columns is SUM(C1:C65536)-SUM(D1:65536).
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 Sep 09, 2008 5:52 am    Post subject: Reply with quote

See also: http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=9527
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Wed Sep 10, 2008 4:54 am    Post subject: Re: Operations on ALL values of one or more columns Reply with quote

Herbivore wrote:
Thanks for suggestions to my question on how to operate on ALL values of one or more columns. I learned that the entire column can be identified by using, for example, A1:A65536. A cell with operations involving columns can then be referenced in other sheets. So in my example above, the balance cell formula for both columns is SUM(C1:C65536)-SUM(D1:65536).


Yes, and to avoid errors you can also define those two columns as say Range1 isC1:C65536 and range2 isD1:65536 and refer to them anywhere in the spreadsheet. Using defined ranges makes it much easier to check what you are doing and to avoid typing errors. See Data> Define Ranges > etc.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
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