how do i specify alternating cell range (de-interleave) in excel?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

given two sets of numbers which have been interleaved/alternated in (e.g) a
column, how can i specify a range that returns every other cell?
thanks.
 
insert a helper column with the range 1,2,1,2,1,2,1,2,1,2,
all the way down
sort the two columns by the second column

greetings from NZ
Bill K
 
For even numbered rows, try this:

=INDEX(A:A,2*ROWS($1:1))

And copy down as needed.

For odd numbered rows, try this:

=INDEX(A:A,2*ROWS($1:1)-1)

And copy down as needed.
 
ok, let me see if i can figure out how to get the chart wizard to swallow
that w/o inserting zeroes for the skipped cells.
thanks!
hwn
 
Assuming the column is A

In B1 enter =OFFSET($A$1,2*ROW() -1,0)

Copy down Column B to return every second cell in Column A


Gord Dibben MS Excel MVP
 
cheers gang.
all the solutions seem to revolve around adding helper columns (either
the alternating even/odd indicator or to hold the extracted values).

i was hoping for something i could feed as series definitions to the
chart wizard w/o disturbing the original spreadsheet.
a range with parameters <begin> <end> <row_step> <col_step>
would be ideal.
anybody see the excel suggestion box anywhere? ;-)

thanks again.
hwn
 
Back
Top