Array Formula with Combin

[Smock Yaba]

In cells A1:C1 I have the numbers 8, 3, and 5. In cell D1 I would like to automatically sum the number of combinations like so:

=COMBIN($A1,3)+COMBIN($A1,4)+COMBIN($A1,5)

If C1 was 6 instead of 5, then I would want:

=COMBIN($A1,3)+COMBIN($A1,4)+COMBIN($A1,5)+COMBIN($A1,6)

If C1 was instead 1, then I would want:

=COMBIN($A1,3)+COMBIN($A1,2)+COMBIN($A1,1)

So in other words, I'd like B1 to be the target number of samples that C1 heads towards in the D1 formula.

 

[Nebu]

May Be you can use the following VBA

Sub test()
Dim l&

i& = ActiveCell.Row
j& = Range("B" & i)
Range("D" & i).ClearContents

If Range("B" & i) <= Range("C" & i) Then

Do While j& <= Range("C" & i)
k& = WorksheetFunction.Combin(Range("A" & i), j)
l = k
k = 0
Range("D" & i) = l + Range("D" & i)
j = j + 1
Loop
Else
Do While j& >= Range("C" & i)
k& = WorksheetFunction.Combin(Range("A" & i), j)
l = k
k = 0
Range("D" & i) = l + Range("D" & i)
j = j - 1
Loop

End If

End Sub

 

[Lori]

You could try:

=SUMPRODUCT(COMBIN(A1,ROW(INDIRECT(B1&":"&C1))))

A non-volatile alternative is:

=BINOM.DIST.RANGE(A1,0.5,MIN(B1:C1),MAX(B1:C1))*2^A1

 

[Smock Yaba]

You could try:

=SUMPRODUCT(COMBIN(A1,ROW(INDIRECT(B1&":"&C1))))

A non-volatile alternative is:

=BINOM.DIST.RANGE(A1,0.5,MIN(B1:C1),MAX(B1:C1))*2^A1

Thanks Lori, exactly what I was looking for. And thanks Nebu for the VBA.