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

Author Message
Newbie

Joined: 17 Sep 2006
Posts: 3

 Posted: Sun Sep 17, 2006 3:50 am    Post subject: FORMULA FOR ENTIRE COLUMN 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?
DiGro
Super User

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

Posted: Sun Sep 17, 2006 4:07 am    Post subject:

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

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)
Newbie

Joined: 17 Sep 2006
Posts: 3

 Posted: Sun Sep 17, 2006 7:28 am    Post subject: FORMULA FOR ENTIRE COLUMN 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!
JohnV

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

Posted: Sun Sep 17, 2006 7:49 am    Post subject:

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 With the spreadsheet open press F1 and try the links to basic Calc help documents on the right.
legatic
Newbie

Joined: 16 Jul 2008
Posts: 1

 Posted: Wed Jul 16, 2008 7:32 pm    Post subject: 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
uros
Super User

Joined: 22 May 2003
Posts: 601
Location: Slovenia

 Posted: Wed Jul 16, 2008 10:45 pm    Post subject: 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
Herbivore
General User

Joined: 29 Apr 2008
Posts: 7

 Posted: Mon Sep 08, 2008 2:15 am    Post subject: [SOLVED] To operate on ALL values of one or more columns 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
jrkrideau
Super User

Joined: 08 Aug 2005
Posts: 6732

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

 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
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
Newbie

Joined: 17 Sep 2006
Posts: 3

 Posted: Mon Sep 08, 2008 5:59 am    Post subject: Thank you Thank you
Herbivore
General User

Joined: 29 Apr 2008
Posts: 7

 Posted: Tue Sep 09, 2008 2:19 am    Post subject: Re: Operations on ALL values of one or more columns 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).
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Tue Sep 09, 2008 5:52 am    Post subject: 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
jrkrideau
Super User

Joined: 08 Aug 2005
Posts: 6732

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

 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
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
 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