PASCAL

=PASCAL(vector, wrap_count, [pad_with])

Download the Template

In the template file, navigate to the Matrices worksheet to see the 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.

PASCAL(n,"L") can be used to create the lower-triangle matrix, L_n. 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 PASCAL. :-)

Lambda Formula

This code for using 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.
*/
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: 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))
            )
        ))
    ))))
))

PASCAL Examples

Test: Copy and Paste this LET function into a cell
=LET(
    n, 5,
    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,
    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,
    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, 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}