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<=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.
Aladin
Posted by riha on June 26, 2001 11:14 PM
Thanks Aladin.
But I need the averages in the same order as they appear in col.B because I'll use them along with the dates for futher manipulation. Your solution does give me the values without blanks, but they're rearranged in some order.
Posted by Aladin Akyurek on June 27, 2001 7:11 AM
Riha,
Thinking that you might have such needs, I had suggested an additional column where you'd have an identifier with the every average that is bound to change its position. You say that you have dates, couldn't you put corresponding dates in C the system of formulas that I suggested take them along with averages. Wouldn't that be enough for your purposes. Otherwise, I don't see how the order of appearance of averages is relevant and sufficient for further processing. Any comments?
Aladin
