M
Mark K
I think I'm on the right track, but I can't quite figure out how to
get this to work.
I have 3000 rows and 800 columns of data. Working with slope variance
etc. The problem is that the data for each row (firm price holding
period returns) starts at a different date. The prior cells are #N/A.
So what I want to achieve is a formula that will check to find the
first cell with a number (zero returns included) and start the calcs
from that cell both for the known_y's,known_x's. So the knows_y's is
one thing, then parsing that to the known_x's is another.
For a simplified example
Market 0.08 0.03 -0.03 -0.04 0.03 0.00 -0.15
A #N/A #N/A #N/A 0.45 -0.01 -0.03 -0.02
B #N/A -0.02 -0.33 -0.03 0.02 -0.13 0.22
So far I've got:
=SLOPE(OFFSET(INDEX(B2:H2,MATCH(1,ISNUMBER(B2:H2)*(B2:H2<>0),0)),
0,0,1,7),OFFSET($E$1,0,0,1,7))
It doesn't work, but I think I'm getting close.
Obviously the offset bit "0,0,1,7)
" won't work as "7" will vary based on the outcome ISNUMBER
Also, how to parse it to the known_x's
Can anybody help please.
Many thanks for any help you can provide
Cheers, Mark
get this to work.
I have 3000 rows and 800 columns of data. Working with slope variance
etc. The problem is that the data for each row (firm price holding
period returns) starts at a different date. The prior cells are #N/A.
So what I want to achieve is a formula that will check to find the
first cell with a number (zero returns included) and start the calcs
from that cell both for the known_y's,known_x's. So the knows_y's is
one thing, then parsing that to the known_x's is another.
For a simplified example
Market 0.08 0.03 -0.03 -0.04 0.03 0.00 -0.15
A #N/A #N/A #N/A 0.45 -0.01 -0.03 -0.02
B #N/A -0.02 -0.33 -0.03 0.02 -0.13 0.22
So far I've got:
=SLOPE(OFFSET(INDEX(B2:H2,MATCH(1,ISNUMBER(B2:H2)*(B2:H2<>0),0)),
0,0,1,7),OFFSET($E$1,0,0,1,7))
It doesn't work, but I think I'm getting close.
Obviously the offset bit "0,0,1,7)
" won't work as "7" will vary based on the outcome ISNUMBER
Also, how to parse it to the known_x's
Can anybody help please.
Many thanks for any help you can provide
Cheers, Mark