L_PASCAL

=L_PASCAL(vector, wrap_count, [pad_with])

Download the Template

In the template file, navigate to the Matrices worksheet to see the L_PASCAL function in action.

Description

Pascal's Matrix is a symmetric positive definite matrix based on Pascal's triangle. It has a few interesting properties that makes it useful for creating quick examples. Being positive definite means that the determinant MDETERM(P) is positive and is always 1 regardless of the size of the matrix. Also, the inverse of P, MINVERSE(P), consists of all integers.

              1
            1   1
          1   2   1
        1   3   3   1
      1   4   6   4   1
    1   5  10  10   5   1
  1   6  15  20  15   6   1

P4 =
[ 1  1  1  1
  1  2  3  4
  1  3  6  10
  1  4  10 20 ]

Procedure to find P_ij without using factorials

Step 1: Start with a row of 1s of length n, to form the elements P_1j for j = 1 to n

Step 2: Repeat the following steps for i = 2 to n

Step 2.1: Initialize csum=0

Step 2.2: Repeat the following steps for j = 1 to n

Step 2.2.1: csum=csum+P_(i-1)j

Step 2.2.2: P_ij=csum

This procedure makes use of the REDUCE and SCAN functions in Excel, and assembling the P matrix is done using the accumulator, VSTACK and HSTACK. This is an interesting example of how REDUCE and SCAN can be used for nested for loops in Excel.

L_PASCAL(n,"L") can be used to create the lower-triangle matrix, L_n. L_PASCAL(n,"U") can create the upper-triangle matrix, U_n which is the same as TRANSPOSE(L_n). Another interesting property is that P_n = MMULT(L_n,U_n).

Need a quick Symmetric Positive Definite matrix? Try L_PASCAL. :-)

Lambda Formula

This code for using L_PASCAL in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

/**
* Returns a Pascal matrix of size nxn. Optionally specify
* type="L" or type="U" for the lower or upper triangle form.
*/
L_PASCAL = LAMBDA(n,[type],
LET(doc,"https://www.vertex42.com/lambda/pascal.html",
IF(n<2,"Error:n<2",
    IF(type="L",
    MAKEARRAY(n,n,LAMBDA(r,c,LET(i,r-1,j,c-1,
        IF(j<=i,FACT(i)/(FACT(j)*FACT(i-j)),0)
    ))),
    IF(type="U",
    MAKEARRAY(n,n,LAMBDA(r,c,LET(i,r-1,j,c-1,
        IF(i<=j,FACT(j)/(FACT(i)*FACT(j-i)),0)
    ))),
    REDUCE(SEQUENCE(1,n,1,0),SEQUENCE(n-1),LAMBDA(acc,row,
        VSTACK(acc,HSTACK(1,
            SCAN(1,SEQUENCE(1,n-1),
                LAMBDA(csum,i,csum+INDEX(acc,row,i+1))
            )
        ))
    ))))
)));

Name: L_PASCAL
Description: Generate a Pascal Matrix of size nxn
Arguments: n,type
Function:

=LET(doc,"https://www.vertex42.com/lambda/pascal.html",
IF(n<2,"Error:n<2",
    IF(type="L",
    MAKEARRAY(n,n,LAMBDA(r,c,LET(i,r-1,j,c-1,
        IF(j<=i,FACT(i)/(FACT(j)*FACT(i-j)),0)
    ))),
    IF(type="U",
    MAKEARRAY(n,n,LAMBDA(r,c,LET(i,r-1,j,c-1,
        IF(i<=j,FACT(j)/(FACT(i)*FACT(j-i)),0)
    ))),
    REDUCE(SEQUENCE(1,n,1,0),SEQUENCE(n-1),LAMBDA(acc,row,
        VSTACK(acc,HSTACK(1,
            SCAN(1,SEQUENCE(1,n-1),
                LAMBDA(csum,i,csum+INDEX(acc,row,i+1))
            )
        ))
    ))))
))

L_PASCAL Examples

Test: Copy and Paste this LET function into a cell
=LET(
    n, 5,
    L_PASCAL(n)
)

Result: {1,1,1,1,1;1,2,3,4,5;1,3,6,10,15;1,4,10,20,35;1,5,15,35,70}

=LET(
    n, 5,
    L_PASCAL(n,"L")
)

Result: {1,0,0,0,0;1,1,0,0,0;1,2,1,0,0;1,3,3,1,0;1,4,6,4,1}


=LET(
    n, 5,
    L_PASCAL(n,"U")
)

Result: {1,1,1,1,1;0,1,2,3,4;0,0,1,3,6;0,0,0,1,4;0,0,0,0,1}

=LET(
    n, 5,
    L, L_PASCAL(n,"L"),
    U, TRANSPOSE(L),
    MMULT(L,U)
)

Result: {1,1,1,1,1;1,2,3,4,5;1,3,6,10,15;1,4,10,20,35;1,5,15,35,70}