Subtracting in rows

Discuss the spreadsheet application

Subtracting in rows

Postby 2dumb4this » Thu Mar 13, 2008 11:56 pm

Ok I am just toooo dumb to figure out how to do what should be an easy task.

I have 5 rows

row1 = person name
row2 = amount earned
row3 = cost of gas
row4 = cost of food
row5 = real earnings

now I cant figure out how to MAKE openofice calc actually total and subtract rows 2-4 and place a total on row 5.
I have tried formatting cells etc but oviously as my name states I am just to dumb to figure out. So any help would be appreciated.

row2 - (row3+row4) = row 5
2dumb4this
 
Posts: 5
Joined: Thu Mar 13, 2008 11:49 pm

Re: Subtracting in rows

Postby keme » Fri Mar 14, 2008 12:05 am

To tell Calc that you want a calculation, you start the cell entry with an equals sign. So if your data are in column B, in cell B5 you would enter =B2-(B3+B4).
OOo 3.3.0 and Apache OOo 3.4.1/4.0.0, on Ms Windows 7 and Mac OS-X.
User avatar
keme
Volunteer
 
Posts: 1601
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Subtracting in rows

Postby 2dumb4this » Fri Mar 14, 2008 12:30 am

keme wrote:To tell Calc that you want a calculation, you start the cell entry with an equals sign. So if your data are in column B, in cell B5 you would enter =B2-(B3+B4).



HUH? These are rows.......

A B C D E
MIKE $20 $10 $5 I want this row to = B-(C+D)
john $15 $8 $1 again want total
and on and on and on

Also where am i supposed to enter the formula you posted?
2dumb4this
 
Posts: 5
Joined: Thu Mar 13, 2008 11:49 pm

Re: Subtracting in rows

Postby kingfisher » Fri Mar 14, 2008 12:51 am

You enter the formula in a new cell, presumably where you expect to find a total.

You only need enter it once. Once entered, it can be copied and pasted.
OpenOffice 3.3 on PCLinuxOS. There are 3 kinds of people: those who can count and those who can't.
User avatar
kingfisher
Volunteer
 
Posts: 1861
Joined: Tue Nov 20, 2007 10:53 am

Re: Subtracting in rows

Postby 2dumb4this » Fri Mar 14, 2008 1:03 am

I still don't understand if I type in the formula that was posted it doesnt work. It would apear that it adds colum B1 and B2 etc which are also peoples names.


I want it to add sideways not up and down!
2dumb4this
 
Posts: 5
Joined: Thu Mar 13, 2008 11:49 pm

Re: Subtracting in rows

Postby 2dumb4this » Fri Mar 14, 2008 1:12 am

Ok even though nobody had any advice I fixed it so it should look like this

=B3-(C3+D3)

However is ther a way so i dont have to keep typing the numbers for each row. So row 100 would be =B100-(C100+D100). I have about 500 columns and don't want to type the formula for each one.

So does anyone know the answer to this? Also please read my post and don't just post some unworkable solution simply to look like you know something!
2dumb4this
 
Posts: 5
Joined: Thu Mar 13, 2008 11:49 pm

Re: Subtracting in rows

Postby James » Fri Mar 14, 2008 1:13 am

2dumb4this wrote:I have 5 rows

row1 = person name
row2 = amount earned
row3 = cost of gas
row4 = cost of food
row5 = real earnings

now I cant figure out how to MAKE openofice calc actually total and subtract rows 2-4 and place a total on row 5.


2dumb4this wrote:HUH? These are rows.......

Code: Select all   Expand viewCollapse view
A            B             C            D             E
MIKE        $20          $10           $5           I want this row to = B-(C+D)
john        $15           $8           $1            again want total

and on and on and on



I think you are confusing rows and columns :?

Rows are 1,2,3 going down the side of the sheet. A,B,C,D are columns going across the top of the sheet.

Forgive me if I'm wrong. If I'm right, then I think what you want is contained in the example attached.

HTH,
Attachments
2dumb4this.ods
(9.23 KiB) Downloaded 102 times
James
www.8daysaweek.co.uk - A User-Focused OOo site.
Windows Easy Installation CDs & OOo on USB Keys, OOo for Mac OS X + Ubuntu CDs

Please read: Survival Guide for the forum
OOo 3.1.1 on Ubuntu 9.x + Windows XPP, Mac OS X
User avatar
James
 
Posts: 264
Joined: Sun Oct 07, 2007 7:23 am
Location: UK

Re: Subtracting in rows

Postby James » Fri Mar 14, 2008 1:16 am

2dumb4this wrote:However is ther a way so i dont have to keep typing the numbers for each row.

You can just copy and paste the formula into as many cells in that column as you want.
James
www.8daysaweek.co.uk - A User-Focused OOo site.
Windows Easy Installation CDs & OOo on USB Keys, OOo for Mac OS X + Ubuntu CDs

Please read: Survival Guide for the forum
OOo 3.1.1 on Ubuntu 9.x + Windows XPP, Mac OS X
User avatar
James
 
Posts: 264
Joined: Sun Oct 07, 2007 7:23 am
Location: UK

Re: Subtracting in rows

Postby keme » Fri Mar 14, 2008 1:17 am

2dumb4this wrote:
keme wrote:To tell Calc that you want a calculation, you start the cell entry with an equals sign. So if your data are in column B, in cell B5 you would enter =B2-(B3+B4).



HUH? These are rows.......

Code: Select all   Expand viewCollapse view
A                  B                   C                        D                  E
MIKE            $20               $10                      $5               I want this row to = B-(C+D)
john            $15                $8                        $1               again want total

and on and on and on

Also where am i supposed to enter the formula you posted?

OK, there's a clash in terminology here. Sorry!

In everyday language, a "row" could mean any set of objects arranged along a straight line.
In spreadsheet terms, we have a grid, that divides the sheet up into cells. Cells along a horizontal line, normally labeled with a number at left, constitute a row. Cells along a vertical line, often labeled by a letter (or letter combination) at the top, constitute a column. That's why I misunderstood your description of rows. I was bound by the terminology, whereas your setup is of course the logical (and most common) one.
If you start in row 2 (using row 1 for headings), then you enter this formula in cell E2: =B2-(C2+D2)
Copy that formula to the next cell in column E, and you'll see that the row numbers of the cell references update accordingly.
OOo 3.3.0 and Apache OOo 3.4.1/4.0.0, on Ms Windows 7 and Mac OS-X.
User avatar
keme
Volunteer
 
Posts: 1601
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Subtracting in rows

Postby 2dumb4this » Fri Mar 14, 2008 1:30 am

Ok I must really be dumb or my openoffice doesnt work, but when I copy/paste i get same formula and same results. Why would it and how would it know I wanted a different formula or updated one if I didnt tell it too? Obviously it wouldnt be programmed or designed to auto update the code for that cell or peole who wanted same results in every filed would be screwed.

So =B3-(C3+D3) shows up in every single cell, no updating. Can you do it with out copy/paste?

I guess with all the typing I have done here I could just type in each formula individually but seems kind of retarded to not have sytem inplace to do it automaticly.
2dumb4this
 
Posts: 5
Joined: Thu Mar 13, 2008 11:49 pm

Re: Subtracting in rows

Postby keme » Fri Mar 14, 2008 1:43 am

Don't keep insisting you're dumb. You may start believing it some day.

If you select the cell, copy, then paste, the formula should update to reflect its new location. That's what you need.
If you "enter" the cell and select the formula (as text, so to speak), it will be pasted verbatim, like you say. This is not what you need.

So just single click the cell with the first formula you entered, copy, select the ~500 target cells and paste. That should do it.
OOo 3.3.0 and Apache OOo 3.4.1/4.0.0, on Ms Windows 7 and Mac OS-X.
User avatar
keme
Volunteer
 
Posts: 1601
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Subtracting in rows

Postby James » Fri Mar 14, 2008 9:31 am

If you copy the cell (just click once on the cell) then copy, go to another cell, click once, and paste - then the formula will auto update. If you copy the text from within the cell, and paste that elsewhere then, as you say, the formula will remain the same.

Some other ways to do this are:
  • highlighting the cell with the formula, then all cells below it where you want formula to and select Edit > Fill > Down
  • Click on the cell containing the formula. "Grab" the tiny square at the bottom right of the highlighted cell by holding down the mouse button. Drag down for as many cells as you want the formula in
HTH,
James
www.8daysaweek.co.uk - A User-Focused OOo site.
Windows Easy Installation CDs & OOo on USB Keys, OOo for Mac OS X + Ubuntu CDs

Please read: Survival Guide for the forum
OOo 3.1.1 on Ubuntu 9.x + Windows XPP, Mac OS X
User avatar
James
 
Posts: 264
Joined: Sun Oct 07, 2007 7:23 am
Location: UK


Return to Calc

Who is online

Users browsing this forum: No registered users and 16 guests