Subtracting in rows

Discuss the spreadsheet application
Post Reply
2dumb4this
Posts: 5
Joined: Thu Mar 13, 2008 11:49 pm

Subtracting in rows

Post by 2dumb4this »

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
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Subtracting in rows

Post by keme »

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).
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
2dumb4this
Posts: 5
Joined: Thu Mar 13, 2008 11:49 pm

Re: Subtracting in rows

Post by 2dumb4this »

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?
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: Subtracting in rows

Post by kingfisher »

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.
Apache OpenOffice 4.1.9 on Linux
2dumb4this
Posts: 5
Joined: Thu Mar 13, 2008 11:49 pm

Re: Subtracting in rows

Post by 2dumb4this »

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

Post by 2dumb4this »

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!
User avatar
James
Volunteer
Posts: 264
Joined: Sun Oct 07, 2007 7:23 am
Location: UK

Re: Subtracting in rows

Post by James »

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

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 413 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
Volunteer
Posts: 264
Joined: Sun Oct 07, 2007 7:23 am
Location: UK

Re: Subtracting in rows

Post by James »

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
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Subtracting in rows

Post by keme »

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

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.
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
2dumb4this
Posts: 5
Joined: Thu Mar 13, 2008 11:49 pm

Re: Subtracting in rows

Post by 2dumb4this »

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.
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Subtracting in rows

Post by keme »

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.
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
User avatar
James
Volunteer
Posts: 264
Joined: Sun Oct 07, 2007 7:23 am
Location: UK

Re: Subtracting in rows

Post by James »

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
Post Reply