Combination with repetition

[nburaq]

Hi Gents,
I need to build a formula for my combination calculation. The formula is,

So there are basically two variables which are n and x. Combina formula in excel let me to sum up values of combination from 3 to 5 automatically but I dont know how to do it for power part. I can do it for x=3 and x=4 and x=5 by one by but I thought maybe there is another easy solution ( I know possible with vba do until loop) I have attached the table as well. Thanks for your help!

Reliability_Analysis_Template.xlsx
	A	B
43	Reliability of Redundant System
44	n=total number of components	5
45	x=number of components that need to function for system to work	3
46	Rc=Reliability of each component	0.8
47	Rs= Reliability of the system	0.2048
Reliability KPIs

Cell Formulas
Range	Formula
B47	B47	=COMBIN(B44,B45)*POWER(B46,3)*POWER((1-B46),(B44-B45))

 

[Eric W]

If you have SEQUENCE:

Book1 (version 1).xlsb
	A	B	C	D
43	Reliability of Redundant System
44	n=total number of components	5
45	x=number of components that need to function for system to work	3
46	Rc=Reliability of each component	0.8		Sum
47	Rs= Reliability of the system	0.2048		0.94208
Sheet26

Cell Formulas
Range	Formula
B47	B47	=COMBIN(B44,B45)*POWER(B46,3)*POWER((1-B46),(B44-B45))
D47	D47	=SUM(COMBIN(B44,SEQUENCE(B44-B45+1,,B45))*B46^SEQUENCE(B44-B45+1,,B45)*(1-B46)^SEQUENCE(B44-B45+1,,B44-B45,-1))

Excel has a function named SERIESSUM that sums up power series, but only with fixed coefficients. Your formula is basically a subsection of the binomial series. I think it's possible that Excel has a built-in formula to do that, probably in the Statistical functions. I'll see if I can find it.

 

[Eric W]

Found it:

Book1 (version 1).xlsb
	A	B	C	D
43	Reliability of Redundant System
44	n=total number of components	5
45	x=number of components that need to function for system to work	3
46	Rc=Reliability of each component	0.8		Sum
47	Rs= Reliability of the system	0.2048		0.94208
48
49		0.2048		0.94208
Sheet26

Cell Formulas
Range	Formula
B47	B47	=COMBIN(B44,B45)*POWER(B46,3)*POWER((1-B46),(B44-B45))
D47	D47	=SUM(COMBIN(B44,SEQUENCE(B44-B45+1,,B45))*B46^SEQUENCE(B44-B45+1,,B45)*(1-B46)^SEQUENCE(B44-B45+1,,B44-B45,-1))
B49	B49	=BINOM.DIST(3,5,B46,FALSE)
D49	D49	=BINOM.DIST(B44,B44,B46,TRUE)-BINOM.DIST(B44-B45,B44,B46,TRUE)

 

[Eric W]

Even better for the D49 formula:

=1-BINOM.DIST(B44-B45,B44,B46,TRUE)

 

[nburaq]

Found it:

Book1 (version 1).xlsb
	A	B	C	D
43	Reliability of Redundant System
44	n=total number of components	5
45	x=number of components that need to function for system to work	3
46	Rc=Reliability of each component	0.8		Sum
47	Rs= Reliability of the system	0.2048		0.94208
48
49		0.2048		0.94208
Sheet26

Cell Formulas
Range	Formula
B47	B47	=COMBIN(B44,B45)*POWER(B46,3)*POWER((1-B46),(B44-B45))
D47	D47	=SUM(COMBIN(B44,SEQUENCE(B44-B45+1,,B45))*B46^SEQUENCE(B44-B45+1,,B45)*(1-B46)^SEQUENCE(B44-B45+1,,B44-B45,-1))
B49	B49	=BINOM.DIST(3,5,B46,FALSE)
D49	D49	=BINOM.DIST(B44,B44,B46,TRUE)-BINOM.DIST(B44-B45,B44,B46,TRUE)

Hi,
This is great! Thanks for the solutions however I got #NAME? error for the formula D47 what could be the reason?

 

[Eric W]

The SEQUENCE function is available in Excel 365, but not Excel 2019. If you're using Excel 2019, the #NAME? error is just saying it doesn't recognize SEQUENCE. It's a bit irrelevant in this case anyway, the formula in post #4 is much cleaner anyway.

 

[nburaq]

The SEQUENCE function is available in Excel 365, but not Excel 2019. If you're using Excel 2019, the #NAME? error is just saying it doesn't recognize SEQUENCE. It's a bit irrelevant in this case anyway, the formula in post #4 is much cleaner anyway.

Thanks a lot yes solution in post 4 works perfectly!