reference

  • Thread starter Thread starter qiong
  • Start date Start date
Q

qiong

Hi

My database is something like that:
I have monthly stock returns for many firms in columns from Jan 73 to
Jun 03. The "Month" column is in column A.
Some of the firms' returns start after Jan 73. So before that, the
cell values have the values "N/A".

For each column representing each firm, I would like to create a
formula in a cell that 'goes' to the first numerical value (after the
whole series of N/As) and then returns the corresponding month where
this first value occurs to that cell.

Is this possible?

Thanks in advance
Keng
 
qiong said:
Hi

My database is something like that:
I have monthly stock returns for many firms in columns from Jan 73 to
Jun 03. The "Month" column is in column A.
Some of the firms' returns start after Jan 73. So before that, the
cell values have the values "N/A".

For each column representing each firm, I would like to create a
formula in a cell that 'goes' to the first numerical value (after the
whole series of N/As) and then returns the corresponding month where
this first value occurs to that cell.

Is this possible?
Click to expand...

Yes. For example, the formula for column B would be
=INDEX($A1:$A500,MIN(IF(ISNUMBER(B1:B500),ROW(B1:B500))))
This is an array formula and so must be entered using Ctrl+Shift+Enter
rather than just Enter.
You can adjust the length of the ranges to suit your data if 500 rows is not
appropriate. (I have allowed for plenty of blank rows).
When this formula is working, simply copy it across for your other columns.
 
Back
Top