# Copying select cells

Posted by riha on June 25, 2001 2:45 AM

My column A has closing prices on 1200 days of a particular stock.
Column B has average closing price for every 5 days on Col.A (B5 has average of A1 to A5, B10 has avg of A6 to A10...)

I need the contents of A5,A10,A15... (every 5th row on column B), in a separate column (say E). I do not want the values (zero / blank) in cells A2,A3,A4,A6,A7....
In short I need only the averages on col.E without the blank cells inbetween.

I tried copying the contents and setting autofilter on. But it copies only the absolute values that do not change when I change the values in column A.

Can someone help?

The

Posted by Aladin Akyurek on June 25, 2001 5:36 AM

Riha,

I'll assume that you have the names of stocks in column C. If not, I'd suggest doing that.

In D1 enter: =IF(B1>0,RANK(B1,\$B\$1:\$B\$2000)+COUNTIF(B\$1:B1,B\$1),"")

Copy down this up to row 2000.

In E1 enter: =MAX(D1:D2000)

Name the cell E1 MaxNumRecs via the Name Box.

In E2 enter: =IF(ROW()-1&LT;=MaxNumRecs,INDEX(B\$1:B\$2000,MATCH(ROW()-1,\$D\$1:\$D\$2000,0)),"")

Copy this to F2 and then down as far as needed.

You'll get a table of average closing prices and stock names in columns E and F without blank rows.

A Side Note. In a reply to a problem like yours, I suggested to array-enter the formula in D1. I think that's not necessary.

I'm posting from school without any check. I hope everything is as intended.

Posted by riha on June 26, 2001 11:14 PM