# nCr but cumulative

This is a discussion on nCr but cumulative within the Excel Questions forums, part of the Question Forums category; I have the numbers 0 to 16 in row 1, beginning at cell B1. I have the numbers 0 to ...

1. ## nCr but cumulative

I have the numbers 0 to 16 in row 1, beginning at cell B1.
I have the numbers 0 to 16 in column A, beginning at cell A2.

I then run this formula in cell B2, and copy over the table:
=COMBIN(B\$1,\$A2)

What I am looking to do is have the columns show the cumulative totals based on the two numbers known. The COMBIN formula can also be written:

=FACT(B\$1)/(FACT(\$A2)*FACT(B\$1-\$A2))

1
16
120
560
1820
4368
8008
11440
12870
11440
8008
4368
1820
560
120
16
1

and I want it to read:

1
17
137
697
2517
6885
14893
26333
39203
50643
58651
63019
64839
65399
65519
65535
65536

I want to use a single formula rather than adding to a nearby cell. Can anybody help, it would be much appreciated. Thank you for your time.

2. ## Re: nCr but cumulative

This sounds terribly like homework, but what the heck...

Try this:

=COMBIN(B\$1,\$A2)+B1*(ROW(B1)>ROW(B\$1))

3. ## Re: nCr but cumulative

Originally Posted by Juan Pablo González
=COMBIN(B\$1,\$A2)+B1*(ROW(B1)>ROW(B\$1))
Thanks for your help, but unfortunately it involves adding a nearby cell.

Originally Posted by xlkeuk
I want to use a single formula rather than adding to a nearby cell. Can anybody help, it would be much appreciated. Thank you for your time.

4. ## Re: nCr but cumulative

Oh, I thought you meant without having to use any extra cells..

5. ## Re: nCr but cumulative

Hi,

You can use a UDF to calculate cumulative combinations. For example:

Code:
```Function CumulativeCombin(n As Double, r As Double, _
Optional Cumulative As Boolean = True) As Double
Dim x As Double

r = Application.Min(n, r)

If Cumulative Then
For x = 0 To r
CumulativeCombin = CumulativeCombin + Application.Combin(n, x)
Next x
Else
CumulativeCombin = Application.Combin(n, r)
End If

End Function```

Also, you can use an array formula, entered with Ctrl-Shift-Enter rather than just Enter

{=SUM(COMBIN(\$A18,ROW(INDIRECT("1:"&C\$1))))+1}

6. ## Re: nCr but cumulative

Wow those look complicated !

Try this too:

=SUMPRODUCT(COMBIN(B\$1,\$A\$2:\$A2))

7. ## Re: nCr but cumulative

Hi JPG,

Agreed. Your solution is better. Nice one!

8. ## Re: nCr but cumulative

Originally Posted by Jay Petrulis
Hi JPG,

Agreed. Your solution is better. Nice one!
My problem is that it involves adding other cells though.

I am looking for a solution where two numbers are used to make the result, and to be able to do it as a formula. i.e. some variation on:

=FACT(B\$1)/(FACT(\$A2)*FACT(B\$1-\$A2))

Thanks for your assistance thus far. I can see how the extra function works, but that too (in effect) adds cells.

9. ## Re: nCr but cumulative

Ue Jay's then... you know... this looks more and more like homework to me... that requirement of "can't use other cells..."

10. ## Re: nCr but cumulative

Originally Posted by Juan Pablo González
Ue Jay's then... you know... this looks more and more like homework to me... that requirement of "can't use other cells..."
It isn't homework, just something I am trying to find out how to do. I am not set / doing homework, just trying to find a workable formula.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•