View previous topic :: View next topic 
Author 
Message 
kadal27 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? 

Back to top 


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
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 or
To sum a number of cells in only column B would be: _________________ DiGro
Windows 7 Home Premium and AOO 4.0.1 NL (Dutch) 

Back to top 


kadal27 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! 

Back to top 


JohnV Administrator
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. 

Back to top 


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 

Back to top 


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 

Back to top 


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+C3D3. 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 


jrkrideau Super User
Joined: 08 Aug 2005 Posts: 6732 Location: Kingston ON Canada

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+C3D3. 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 


kadal27 Newbie
Joined: 17 Sep 2006 Posts: 3

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


Thank you 

Back to top 


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

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany


Back to top 


jrkrideau Super User
Joined: 08 Aug 2005 Posts: 6732 Location: Kingston ON Canada

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

Back to top 


