[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
DrIdiot
Newbie

Joined: 07 Dec 2005
Posts: 1

 Posted: Wed Dec 07, 2005 7:54 pm    Post subject: applying formula to many cells Let's say I have a spreadsheet, and I have a column at the end of a table that should sum all values within a certain range (say, from column C to G in that row). How can I do this without setting the formulas to each row: SUM(C1:G1) SUM(C2:G2) ... I tried the help and tutorials but I couldn't do it and got really confused. Thanks, -Harrison
xlprof
Newbie

Joined: 06 Dec 2005
Posts: 4
Location: Philippines

 Posted: Wed Dec 07, 2005 8:38 pm    Post subject: The answer is select cells F1:F2 (thats where the summation is), then click the sum function in the formula bar. That's it.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

Posted: Thu Dec 08, 2005 3:45 am    Post subject:

 Quote: How can I do this without setting the formulas to each row: SUM(C1:G1) SUM(C2:G2) ...

----------------------------------------------------------------------------------
Having data in C1:G10000 and col H blank, select H1:H10000
Notice the position of the current input-cell.
If you selected H:H10000 with current cell @ H10000
=SUM(C10000:G10000)
with current cell @ H1:
=SUM(C1:G1)
----------------------------------------------------------------------------------
Having data in C1:G10000 and col I blank, select I1:I10000
Notice the position of the current input-cell.
If you selected I:I10000 with current cell @ I10000
=SUM(\$C\$10000:\$G\$10000)
with current cell @ I1:
=SUM(\$C\$1:\$G\$1)
---------------------------------------------------------------------------------
This is what the \$ is about.
From the view point of Sheet2.B2
Sheet1.A2 is the left neighbour in previous sheet.
\$Sheet1.A2 is the left neighbour in sheet1.
\$A2 is the cell in column A of the same row
\$A\$2 is exactly A2 in this sheet
B1 is the top neighbour
and so on...
Read \$D\$5 like "exactly Column D, exactly Row 5"
Read D\$5 like "X columns left/right, exactly Row 5"
Read \$D5 like "exactly Column D, Y rows above/below
Read D5 like "the cell X Columns left/right, Y rows above/below"
A relative address like A1:B5 does not realy say cells @ A1:B5
The same ref copied one row down says A2:B6
An absolute address like \$A\$1:\$B\$5 means exactly those cells.
When moving cell(s) refering to others, nothing happens to the references.
Moving by clipboard or simple drag&drop means: Show exactly this elswhere.
When copying cell(s) refering to others, the relations without \$ will be adjusted.
Copying by clipboard or Ctrl+drag&drop means: Apply this references to the target range.
If you want to be controller's darling while keeping your job: Learn everything about referencing in sheets and debugging them.
David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Thu Dec 08, 2005 6:11 am    Post subject: Re: applying formula to many cells

 DrIdiot wrote: How can I do this without setting the formulas to each row: SUM(C1:G1) SUM(C2:G2) ...

If I read you right, you just want to save typing in every row...

Type in the first. Then you'll see a little square at the bottom right of that cell. Grab that with the mouse, and drag down the column. Your formula will be copied down. There is another way using "Fill", but I don't want to spoil all your fun. It took some of us decades to learn what we do, and that's only a portion. ....and why not? We don't do it for a living; well, not all of us.

As was pointed out though by Villeroy, do take a look at relative and absolute cell reference to be sure of what you are doing. This method saves typing, but does not lead to understanding.

David.
carl
Super User

Joined: 21 Apr 2003
Posts: 920
Location: Germany

Posted: Fri Dec 09, 2005 12:37 am    Post subject:

 Quote: select cells F1:F2 (thats where the summation is), then click the sum function in the formula bar.

I love it! I learn something new everyday!
_________________
carl
Using OpenOffice.org 2 on XP sp2
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum