| View previous topic :: View next topic |
| 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 |
|
| Back to top |
|
 |
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. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Thu Dec 08, 2005 3:45 am Post subject: |
|
|
Another answer is: Learn about absolute, relative and mixed references.
| 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)
Press Alt+Enter instead of enter.
----------------------------------------------------------------------------------
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)
Press Alt+Enter instead of enter.
---------------------------------------------------------------------------------
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. |
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| 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. |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
|
