functions on whole columns

[ellie]

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.

[Bernard Liengme]

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" <[email protected]> wrote in message
news:[email protected]...
> 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]

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

[pinmaster]

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

[Gord Dibben]

ellie

For problem 2/

=INDIRECT("B"&ROW())


Gord Dibben Excel MVP

On Tue, 27 Dec 2005 18:30:12 +0000 (UTC), "ellie" <[email protected]> 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.
>
>
>

[ellie]

Thanks everyone for your helpful replies :-)

ellie

"Bernard Liengme" <[email protected]> wrote in message
news:O4CY#[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
> >
> >
>
>