=SLOPE(OFFSET(INDEX .... Help please

[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

 

[Bernd P]

Hello Mark,

Why OFFSET? Take INDEX:
=SLOPE(INDEX(A2:G2,1,LOOKUP(2,1/ISERROR(A2:G2),{1,2,3,4,5,6,7})
+1):G2,INDEX($E$1:$K$1,1,LOOKUP(2,1/ISERROR(A2:G2),{1,2,3,4,5,6,7})+1):
$K$1)
It's not volatile.

Regards,
Bernd

 

[Lori]

Maybe try entering this using {Ctrl+Shift+Enter}:

=SLOPE(IF(ISNUMBER(B2:H2),B2:H2),IF(ISNUMBER(B2:H2),B1:H1))

slope/intercept/forecast ignore non-numeric data (unlike linest/trend).

 

[Mark K]

Hello Mark,

Why OFFSET? Take INDEX:
=SLOPE(INDEX(A2:G2,1,LOOKUP(2,1/ISERROR(A2:G2),{1,2,3,4,5,6,7})
+1):G2,INDEX($E$1:$K$1,1,LOOKUP(2,1/ISERROR(A2:G2),{1,2,3,4,5,6,7})+1):
$K$1)
It's not volatile.

Regards,
Bernd

Many thanks for your help. Much appreciated.

There is one problem I'm having with this formula. If the row has no
errors, when it has all numbers, it gives an
#N/A error.

Any suggestions?

Also, regarding the section {1,2,3,4,5,6,7})+1) The actual data covers
60 cells. So do I need to count 1 to 60?

Cheers
Mark

 

[Mark K]

Maybe try entering this using {Ctrl+Shift+Enter}:

=SLOPE(IF(ISNUMBER(B2:H2),B2:H2),IF(ISNUMBER(B2:H2),B1:H1))

slope/intercept/forecast ignore non-numeric data (unlike linest/trend).

Many thanks for your help. I'll give it a try.

Cheers, Mark

 

[Zumble]

I had this problem too. Well, maybe.
I don't know much about slopes but I often tend to see problems in the light
of my own experience.
Sometimes I'm way off the point but sometime I'm not. So here goes.

Is it like comparing the weights of two brothers, but one is two years older
than the other ?
If it's not then you may check out.



I ended up calculating the **age** (in days, or quarters) of each brother,
not the birthdate.
When you use ages, then comparatif weights make sense because they're all
comparable.

Also, when you have small slopes and big slopes (hills and mountains) you
can't compare them either if the data stays a number.
You have to shift those numbers into percentages of the maximum or some
common reference point.
Otherwise your data is so different it can't be interpreted in a table or on
a graph.



If you can find my posting of about two years ago, under another alias, Andy
Pope, I was talking about Frogs in a Pond
Frogs in the pond - Display percentages not numbers
Same problem.
Different analogy.

FWIW
Zumble.

 

[Mark K]

Having a look at it. So far no luck with this.

One problem is parsing the correct cell reference from known_y's to
known_x's. Also, the market data will not have the same NA's as the
firm data.

Any suggestions?

Cheers
Mark

 

[Mark K]

I finally got it to work. Thanks to all for your suggestions that
helped and prompted further ideas.

I realised after responding to Bernd that working it in 2 stages would
simplify the formula - a long formula would increase the size of the
file which is already <100mb just for 1 year (& I've got 25 to go).

Also combining Lori's ISNUMBER idea. Actually, that promoted me to
look at something I'd done a few yrs ago using ISNUMBER to get further
ideas.

The solution:

Cell H2 =MATCH(1,ISNUMBER(2:2)*(2:2<>0),0)
Cell I2 =SLOPE(INDEX(A2:G2,1,H2):G2,INDEX($A$1:$G$1,1,H2):$G$1)

Times for file saving & calculation are reduced substantially with
this method for the huge amount of data I have.

The beauty is that I can also use cell H2 to parse the starting cell
to variance etc.

I'm sure this will be useful to others doing similar calculation on
market data. It has been a recurring issue for me, but in the past
after a couple of goes I just put up with the reduced number of
observations. In this case I'm going back a few years so observations
were reduced by a significant 30% thus making it worthwhile to pursue.

Thanks all for your help and input.

Regards
Mark

 

[Mark K]

Actually, I've changed Cell H2

it was =MATCH(1,ISNUMBER(2:2)*(2:2<>0),0) This worked fine but did
not count the zeros. It is possible for a firm to have a zero return.

It is now:
=MATCH(0,ISERROR(2:2)*(2:2<>0),0)
(entered as an array by hitting Ctrl/Shift/Enter)

This knocks out the NAs but not the zeros.

Cheers
Mark

 

[Bernd P]

Hello Mark,

Make sure that your data starts with an error:
=VLOOKUP(0,0,0)
or
=1/0
Regards,
Bernd