Calc SUM Function

users
#1

So I can select an area of a column "=SUM(B2:B50)" and have it add and
produce a total in say B51. Thats fine but fairly simplistic and I need
more. Completely new to spreadsheets and how to do this stuff.

1. Can I add ALL of Column B, Minus B1 and have the total of the column
display in B1?

2. Column B is a Cost/Each, C is Quantity, D is Shipping & E is Total Cost.
Is there a way to multiply B & C then add D and display in E for each row?
Yes, I understand C would have to be at least 1 or greater and not empty. :slight_smile:

Any detailed explanation, tip, link to a how-to would be so appreciated!
I've only found a few examples and pretty basic.

Thanks

#2

So I can select an area of a column "=SUM(B2:B50)" and have it add and produce a total in say B51. That's fine but fairly simplistic and I need more. Completely new to spreadsheets and how to do this stuff.

You'll want to read a book, go on a course, get local help, join a club, or whatever. You cannot really learn by individual e-mail message.

1. Can I add ALL of Column B, Minus B1 and have the total of the column display in B1?

You don't mean - do you? - that you want the existing value in B1 subtracted from the sum of the rest and the result put back into B1? That would be a circular (or "recursive") requirement and the result would be undefined. If you just want the sum of the cells from B2 downward placed in B1, just put
=SUM(B2:B999)
in B1. Choose a finishing row that will be beyond any values you will insert. If necessary, find the last possible row number and use that.

2. Column B is a Cost/Each, C is Quantity, D is Shipping & E is Total Cost. Is there a way to multiply B & C then add D and display in E for each row?

In E2, enter:
=B2*C2+D2
and then fill this down column E as necessary. For an understanding of "fill", see my first sentence above.

Yes, I understand C would have to be at least 1 or greater and not empty. :slight_smile:

Not particularly. Empty cells are evaluated as zero in calculations. But you will have the problem that your customer who orders none of something will still be charged for shipping his non-existent package!

I trust this helps.

Brian Barker

#3

So I can select an area of a column "=SUM(B2:B50)" and have it add and
produce a total in say B51. Thats fine but fairly simplistic and I need
more. Completely new to spreadsheets and how to do this stuff.

1. Can I add ALL of Column B, Minus B1 and have the total of the column
display in B1?

      You should use the first row to label your columns and and put your total for Cost Each in B2
      Use this formula in your first paragraph:
a) Click B2 to select it.
b) Enter this into B2: =SUM(B3:1048576) [1048576 is the last row in a Calc sheet]

2. Column B is a Cost/Each, C is Quantity, D is Shipping & E is Total Cost.
Is there a way to multiply B & C then add D and display in E for each row?
Yes, I understand C would have to be at least 1 or greater and not empty. :slight_smile:

      This is basic algebra plus one additional step:
a) Click E3 to select it.
b) Enter this into E3: =B3*C3+D3 using the <Enter> key [This gives the total cost for row 3]
c) Drag the "handle" down the column to copy this formula into each row of the E column.

      When you enter the formula into cell E3, a black box appears around E3 with a small black at the bottom right corner which we call a "handle". Click on it and drag it down the column. One note: it is going to take some time for Calc to copy the formula into the 1048573 rows.
      Someone else may know of a way to copy the formula into all the rows more quickly.
      This could also be done with a database. The data would be entered into a table. Then in a query you could have four columns: Cost/Each, Quantity, Shipping and Total Cost. The last column would apply the formula you need to each row of your table.

Dan

#4

More easily, perhaps, but not more quickly:

Either:
o Click on the source cell. Press Ctrl+C or go to Edit | Copy. Click the column header to select the entire column. Ctrl+click the column heading to deselect it if necessary. Press Ctrl+V or go to Edit | Paste.

Or:
o Click the column header to select the entire column. Ctrl+click the column heading to deselect it if necessary. Go to Edit | Fill > | Down.

(I wouldn't recommend filling entire columns with formulae: a million is a lot of rows! But the above techniques are applicable to shorter extents.)

Brian Barker

#5

Many thanks for the help. I was able to setup the spreadsheet as I wanted. Thanks again.