How to use matrix operations to create a "lag value" matrix

Let's suppose I've got an array that has values 100,200,300,400.

What could I do w/ matrix formulas to implicitly construct a 4x4
matrix that looks like:

1,2,3,4
0,1,2,3
0,0,1,2
0,0,0,1

The above is just a wedge that has each row "lagged" from the
previous.

I can do it explicitly by creating the lag matrix w/ lookup functions
right on the sheet, but curious if I might be able to do it right in
the formulas.  That way I'd avoid the burden of creating lots of
utility calculation areas in the workbook.

(Aside - use case: These matrices are instrumental to modeling
cumulative impacts from customers acquired across time.  I use these
matrices and multiply them times a customer lifecycle array modeling
each month's performance for a given customer.  There are also other
ways to go, but I still end up creating a lot of utility ranges w/ a
bunch of sumproduct operations.  Pick my poison, perhaps.)

Thx!
0
6/24/2009 4:42:49 PM
excel 39879 articles. 2 followers. Follow

1 Replies
495 Views

Similar Articles

[PageSpeed] 13

Edit:  Sorry, I meant to say "an array that has values
1,2,3,4" (simpler, no need to divide by 100!).

TrippKnightly wrote:
> Let's suppose I've got an array that has values 100,200,300,400.
>
> What could I do w/ matrix formulas to implicitly construct a 4x4
> matrix that looks like:
>
> 1,2,3,4
> 0,1,2,3
> 0,0,1,2
> 0,0,0,1
>
> The above is just a wedge that has each row "lagged" from the
> previous.
>
> I can do it explicitly by creating the lag matrix w/ lookup functions
> right on the sheet, but curious if I might be able to do it right in
> the formulas.  That way I'd avoid the burden of creating lots of
> utility calculation areas in the workbook.
>
> (Aside - use case: These matrices are instrumental to modeling
> cumulative impacts from customers acquired across time.  I use these
> matrices and multiply them times a customer lifecycle array modeling
> each month's performance for a given customer.  There are also other
> ways to go, but I still end up creating a lot of utility ranges w/ a
> bunch of sumproduct operations.  Pick my poison, perhaps.)
>
> Thx!
0
6/24/2009 7:47:56 PM
Reply:

Similar Artilces:

How to use matrix operations to create a "lag value" matrix
Let's suppose I've got an array that has values 100,200,300,400. What could I do w/ matrix formulas to implicitly construct a 4x4 matrix that looks like: 1,2,3,4 0,1,2,3 0,0,1,2 0,0,0,1 The above is just a wedge that has each row "lagged" from the previous. I can do it explicitly by creating the lag matrix w/ lookup functions right on the sheet, but curious if I might be able to do it right in the formulas. That way I'd avoid the burden of creating lots of utility calculation areas in the workbook. (Aside - use case: These matrices are instrumental to modeling cumul...