Excel function to return a nested SEQUENCE within COMBIN

Question

My current function is:

=SUM(COMBIN(5,SEQUENCE($A$1,,$A$1,-1)) * COMBIN(4,SEQUENCE($A$1,,$A$1,-1)))

where $A$1 = any potential integer. So for example, if it were 3 it would return 120 i.e.

COMBIN(5...)	COMBIN(4...)	SUM
10	4	40
10	6	60
5	4	20
		120

I need to change the formula so the final number is based on a nested sequence of the input number from $A$1 so using the same example where $A$1 = 3 the result is 220 in effect summing the results of the formula with a decrementing $A$1 by 1 until it reaches 1 i.e.

COMBIN(5...)	COMBIN(4...)	SUM
10	4	40
10	6	60
5	4	20
10	6	60
5	4	20
5	4	20
		220

or effectively =SUM(COMBIN(5,SEQUENCE($A$1,,$A$1,-1)) * COMBIN(4,SEQUENCE($A$1,,$A$1,-1)), COMBIN(5,SEQUENCE($A$1-1,,$A$1-1,-1)) * COMBIN(4,SEQUENCE($A$1-1,,$A$1-1,-1)), COMBIN(5,SEQUENCE($A$1-2,,$A$1-2,-1)) * COMBIN(4,SEQUENCE($A$1-2,,$A$1-2,-1)))

but because I don't know what $A$1 is, I can't write it out this way as i won't know when $A$1-x will reach 1 and therefore stop summing.

Answer 1

You could do it with a triangular matrix - there's no reason to count down from the initial value; you could simply count up to it, resulting in a matrix that looks like this:

1 -1 -1
1  2 -1
1  2  3

where the -1 in positions aren't used

This can be demonstrated in Excel 365 in two steps:

=IF(SEQUENCE(1,A1)>SEQUENCE(A1,1),-1,SEQUENCE(1,A1))

in order to obtain the matrix in (say) C1 and

=SUM(IFERROR(COMBIN(4,C1#)*COMBIN(5,C1#),0))

to work through the combinations