Excel function to return a nested SEQUENCE within COMBIN

0 votes

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.

Mar 24, 2022 in Database by Edureka
• 13,670 points
686 views

1 answer to this question.

0 votes

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))
enter image description here

to work through the combinations
enter image description here
answered Mar 25, 2022 by gaurav
• 23,260 points

Related Questions In Database

0 votes
1 answer

Excel 2003 - Match Column A with Column B & Column C to correspond to Column B

Write a standard IF formula that compares ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
2,375 views
0 votes
1 answer

Excel formula to remove comma, spaces, period and add a text

The steps to accomplish this are as ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
1,764 views
0 votes
1 answer

Converting a large XML file to Excel

Using the web URL, import an XML ...READ MORE

answered Mar 24, 2022 in Database by gaurav
• 23,260 points
2,315 views
0 votes
1 answer

[Excel][VBA] How to draw a line in a graph?

Sub MakeChart() Dim x(20) ...READ MORE

answered Mar 24, 2022 in Database by gaurav
• 23,260 points
1,526 views
0 votes
1 answer

Insert a value to a cell in excel using formula in another cell

Select the cell where the formula should ...READ MORE

answered Mar 25, 2022 in Database by gaurav
• 23,260 points
4,152 views
0 votes
1 answer

How to convert a column number (e.g. 127) into an Excel column (e.g. AA)

If anyone needs to do this in ...READ MORE

answered Mar 30, 2022 in Database by gaurav
• 23,260 points
690 views
0 votes
1 answer

Excel TEXT formula doesn't convert 'yyyy' to a year

It appears that if you have a ...READ MORE

answered Mar 30, 2022 in Database by gaurav
• 23,260 points
672 views
0 votes
1 answer

What is the Excel formula to countif text partially makes up a cell?

With Formulas, Countif Partial String/Substring Match We can ...READ MORE

answered Mar 30, 2022 in Database by gaurav
• 23,260 points
10,596 views
0 votes
1 answer
0 votes
1 answer

Convert a number to a letter in C# for use in Microsoft Excel [duplicate]

If you are familiar with using formulas ...READ MORE

answered Feb 23, 2022 in Database by gaurav
• 23,260 points
571 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP