If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I reference every "n" cell in a column in Excel?
I wish to enter a formula at the top of a column "Y" that references a column
"X". This formula should be designed to pick off every "n" row of column "X" and populate every row of column "Y". (n can be defined in the formula, or in a cell referenced in the formula) I then want to drag the formula down column "Y" and have it populate every cell the column "Y", referencing every "n" cell down the column "X". So, for example, I wish to populate column B with the values of every even row cell in column A. Or, the same as above, only poplulate column B with the values of every odd row cell in column A. Or, if n=3... n=3 A B 1 q =A1 2 w =A4 3 e =A7 4 r =A10 5 t =A13 6 y 7 u 8 i |
#2
|
|||
|
|||
on cell B1 type
=offset(a1,row()*2-2,0) and copy it down "Alma" wrote in message ... I wish to enter a formula at the top of a column "Y" that references a column "X". This formula should be designed to pick off every "n" row of column "X" and populate every row of column "Y". (n can be defined in the formula, or in a cell referenced in the formula) I then want to drag the formula down column "Y" and have it populate every cell the column "Y", referencing every "n" cell down the column "X". So, for example, I wish to populate column B with the values of every even row cell in column A. Or, the same as above, only poplulate column B with the values of every odd row cell in column A. Or, if n=3... n=3 A B 1 q =A1 2 w =A4 3 e =A7 4 r =A10 5 t =A13 6 y 7 u 8 i |
#3
|
|||
|
|||
In B1, put 3.
in b2, put... =INDIRECT("A"&(($B$1*ROW())-($B$1+1))) where... A is the column with the original values B is the column with the every other 'n' row B1 is where you put the number ('n') of rows to be skipped The '1' at the end is the number of rows in the header. For example, if you start your formulas at B2, there is only one row above it. If you start your formulas at B5, there are 4 rows above it and therefore the '1' should be replaced with a '4'. HTH, Gary Brown "Alma" wrote: I wish to enter a formula at the top of a column "Y" that references a column "X". This formula should be designed to pick off every "n" row of column "X" and populate every row of column "Y". (n can be defined in the formula, or in a cell referenced in the formula) I then want to drag the formula down column "Y" and have it populate every cell the column "Y", referencing every "n" cell down the column "X". So, for example, I wish to populate column B with the values of every even row cell in column A. Or, the same as above, only poplulate column B with the values of every odd row cell in column A. Or, if n=3... n=3 A B 1 q =A1 2 w =A4 3 e =A7 4 r =A10 5 t =A13 6 y 7 u 8 i |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Reference Cell in Named Range | mark1 | General Discussion | 1 | September 7th, 2004 01:15 AM |
IF E3 & E10 = TRUE set this cell to "Yes", else set to "No" | Timothy L | Worksheet Functions | 5 | August 27th, 2004 02:28 AM |
variables in cell references in formulas | gedalia | Worksheet Functions | 2 | March 11th, 2004 07:35 AM |
Absolute cell references across worksheets | Frank Kabel | Worksheet Functions | 3 | March 4th, 2004 07:06 PM |
Cell reference data not displaying in chart | Jon Peltier | Charts and Charting | 0 | November 12th, 2003 06:30 PM |