functions on whole columns
 

I'm new to Excel, please forgive me if these are silly questions...

1/

I have three columns - the cells in A and B contain numbers
and I want the cells in C to be those in B minus those in A
i.e.

C1 = B1 - A1
C2 = B2 - A2
and so on.

To achieve this, I don't want to have to go down the whole column
typing in box C1 " =B1-A1 " and typing in C2 " =B2-A2 "
and so on.

Is there a way I can apply one function to the whole of column C
to save time typing?

2/

Different problem...

ROW () gives the row number. So if I type in box C6, ROW() it gives the
number 6.
But if I want C6 to contain the contents of B6, why doesn't =B(ROW()) work
i.e. the ROW() is 6 so it's equal to "=B6". What am I doing wrong?

Many thanks for any advice.

functions on whole columns
 

Two ways:
1) Click on column header to select the entire column; type =B1-A1 and then
use CTRL+ENTER to complete the formulas
2) In C1 enter =A1-B1 and double click C1's fill-handle (the little solid
square in the cell's lower right corner - when cell is selected). This works
only when there are entries in the neighbouring column.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"ellie" wrote in message
...
I'm new to Excel, please forgive me if these are silly questions...

1/

I have three columns - the cells in A and B contain numbers
and I want the cells in C to be those in B minus those in A
i.e.

C1 = B1 - A1
C2 = B2 - A2
and so on.

To achieve this, I don't want to have to go down the whole column
typing in box C1 " =B1-A1 " and typing in C2 " =B2-A2 "
and so on.

Is there a way I can apply one function to the whole of column C
to save time typing?

2/

Different problem...

ROW () gives the row number. So if I type in box C6, ROW() it gives the
number 6.
But if I want C6 to contain the contents of B6, why doesn't =B(ROW()) work
i.e. the ROW() is 6 so it's equal to "=B6". What am I doing wrong?

Many thanks for any advice.

functions on whole columns
 

You can use the autofill button on the bottom right corner to drag down the
formula as far as you need.

Debra Dalgleish has some tips at:
http://contextures.com/xlDataEntry01.html#Mouse

or....

You can select the range you want first:
Say you select C1:C99
Then type the formula for C1:
=b1-a1
But instead of hitting enter, hit ctrl-enter. Excel will adjust the formula for
each cell in the selection.

============
You can use =indirect() to point at other cells:

in A6, this will return the value in B6:
=indirect("b"&row())


ellie wrote:

I'm new to Excel, please forgive me if these are silly questions...

1/

I have three columns - the cells in A and B contain numbers
and I want the cells in C to be those in B minus those in A
i.e.

C1 = B1 - A1
C2 = B2 - A2
and so on.

To achieve this, I don't want to have to go down the whole column
typing in box C1 " =B1-A1 " and typing in C2 " =B2-A2 "
and so on.

Is there a way I can apply one function to the whole of column C
to save time typing?

2/

Different problem...

ROW () gives the row number. So if I type in box C6, ROW() it gives the
number 6.
But if I want C6 to contain the contents of B6, why doesn't =B(ROW()) work
i.e. the ROW() is 6 so it's equal to "=B6". What am I doing wrong?

Many thanks for any advice.

--

Dave Peterson

functions on whole columns
 

ellie

For problem 2/

=INDIRECT("B"&ROW())


Gord Dibben Excel MVP

On Tue, 27 Dec 2005 18:30:12 +0000 (UTC), "ellie" wrote:

I'm new to Excel, please forgive me if these are silly questions...

1/

I have three columns - the cells in A and B contain numbers
and I want the cells in C to be those in B minus those in A
i.e.

C1 = B1 - A1
C2 = B2 - A2
and so on.

To achieve this, I don't want to have to go down the whole column
typing in box C1 " =B1-A1 " and typing in C2 " =B2-A2 "
and so on.

Is there a way I can apply one function to the whole of column C
to save time typing?

2/

Different problem...

ROW () gives the row number. So if I type in box C6, ROW() it gives the
number 6.
But if I want C6 to contain the contents of B6, why doesn't =B(ROW()) work
i.e. the ROW() is 6 so it's equal to "=B6". What am I doing wrong?

Many thanks for any advice.

functions on whole columns
 

You don't have to type your formula over and over again, just type it in
C1 and then drag the cell down as far as needed.

HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=496225

functions on whole columns
 

Thanks everyone for your helpful replies :-)

ellie

"Bernard Liengme" wrote in message
...
Two ways:
1) Click on column header to select the entire column; type =B1-A1 and
then
use CTRL+ENTER to complete the formulas
2) In C1 enter =A1-B1 and double click C1's fill-handle (the little solid
square in the cell's lower right corner - when cell is selected). This
works
only when there are entries in the neighbouring column.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"ellie" wrote in message
...
I'm new to Excel, please forgive me if these are silly questions...

1/

I have three columns - the cells in A and B contain numbers
and I want the cells in C to be those in B minus those in A
i.e.

C1 = B1 - A1
C2 = B2 - A2
and so on.

To achieve this, I don't want to have to go down the whole column
typing in box C1 " =B1-A1 " and typing in C2 " =B2-A2 "
and so on.

Is there a way I can apply one function to the whole of column C
to save time typing?

2/

Different problem...

ROW () gives the row number. So if I type in box C6, ROW() it gives the
number 6.
But if I want C6 to contain the contents of B6, why doesn't =B(ROW())
work
i.e. the ROW() is 6 so it's equal to "=B6". What am I doing wrong?

Many thanks for any advice.