Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
Hidden Columns in Shared Workbooks | Excel Discussion (Misc queries) | |||
Hiding columns and custom views problem | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Counting the Contents of Two Columns | Excel Discussion (Misc queries) |