FORMULA FOR ENTIRE COLUMN

kadal27 · Newbie Joined: 17 Sep 2006 Posts: 3

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

kadal27 · Newbie Joined: 17 Sep 2006 Posts: 3

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

legatic · Newbie Joined: 16 Jul 2008 Posts: 1

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

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.

jrkrideau · Last edited by jrkrideau on Mon Sep 08, 2008 11:31 am; edited 1 time in total

kadal27 · Newbie Joined: 17 Sep 2006 Posts: 3

Thank you

Herbivore · General User Joined: 29 Apr 2008 Posts: 7

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 · 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 http://forum.openoffice.org

jrkrideau