Formula calculate combination of each number by there 5 positions

[motilulla]

Hello,

I want to know how much combinations could be each of number by there 5 positions.
I find the formula which can gives the combination of each number only in the first position formula E6=COMBIN(50-D6,4) Copied down to E51.

My query is how much combination could be for each number within 5 positions. For example like number 14 has their combinations by position as shown. That I found some ware in goggle not sure is it correct or not?

*	A	B	C	D	E	F	G	H	I	J
1
2
3
4			Number	Position1	Position2	Position3	Position4	Position5
5			1	211.876
6			2	194.580
7			3	178.365
8			4	163.185
9			5	148.995
10			6	135.751
11			7	123.410
12			8	111.930
13			9	101.270
14			10	91.390
15			11	82.251
16			12	73.815
17			13	66.045
18			14	58.905	92.820	49.140	10.296	715
19			15	52.360
20			16	46.376
21			17	40.920
22			18	35.960
23			19	31.465
24			20	27.405
25			21	23.751
26			22	20.475
27			23	17.550
28			24	14.950
29			25	12.650
30			26	10.626
31			27	8.855
32			28	7.315
33			29	5.985
34			30	4.845
35			31	3.876
36			32	3.060
37			33	2.380
38			34	1.820
39			35	1.365
40			36	1.001
41			37	715
42			38	495
43			39	330
44			40	210
45			41	126
46			42	70
47			43	35
48			44	15
49			45	5
50			46	1
51
52

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[Marcelo Branco]

Maybe this..

11082021 Testes.xlsx
	C	D	E	F	G	H
1
2
3		Position
4	Number	1	2	3	4	5
5	1	211876	0	0	0	0
6	2	194580	17296	0	0	0
7	3	178365	32430	1081	0	0
8	4	163185	45540	3105	46	0
9	5	148995	56760	5940	180	1
10	6	135751	66220	9460	440	5
11	7	123410	74046	13545	860	15
12	8	111930	80360	18081	1470	35
13	9	101270	85280	22960	2296	70
14	10	91390	88920	28080	3360	126
15	11	82251	91390	33345	4680	210
16	12	73815	92796	38665	6270	330
17	13	66045	93240	43956	8140	495
18	14	58905	92820	49140	10296	715
19	15	52360	91630	54145	12740	1001
20	16	46376	89760	58905	15470	1365
21	17	40920	87296	63360	18480	1820
22	18	35960	84320	67456	21760	2380
23	19	31465	80910	71145	25296	3060
24	20	27405	77140	74385	29070	3876
25	21	23751	73080	77140	33060	4845
26	22	20475	68796	79380	37240	5985
27	23	17550	64350	81081	41580	7315
28	24	14950	59800	82225	46046	8855
29	25	12650	55200	82800	50600	10626
30	26	10626	50600	82800	55200	12650
31	27	8855	46046	82225	59800	14950
32	28	7315	41580	81081	64350	17550
33	29	5985	37240	79380	68796	20475
34	30	4845	33060	77140	73080	23751
35	31	3876	29070	74385	77140	27405
36	32	3060	25296	71145	80910	31465
37	33	2380	21760	67456	84320	35960
38	34	1820	18480	63360	87296	40920
39	35	1365	15470	58905	89760	46376
40	36	1001	12740	54145	91630	52360
41	37	715	10296	49140	92820	58905
42	38	495	8140	43956	93240	66045
43	39	330	6270	38665	92796	73815
44	40	210	4680	33345	91390	82251
45	41	126	3360	28080	88920	91390
46	42	70	2296	22960	85280	101270
47	43	35	1470	18081	80360	111930
48	44	15	860	13545	74046	123410
49	45	5	440	9460	66220	135751
50	46	1	180	5940	56760	148995
Plan2

Cell Formulas
Range	Formula
D5:H50	D5	=IF(D$4>$C5,0,COMBIN($C5-1,D$4-1)*COMBIN(50-$C5,5-D$4))

Hope this helps

M..

 

[motilulla]

Maybe this..

11082021 Testes.xlsx
	C	D	E	F	G	H
1
2
3		Position
4	Number	1	2	3	4	5
5	1	211876	0	0	0	0
6	2	194580	17296	0	0	0
7	3	178365	32430	1081	0	0
8	4	163185	45540	3105	46	0
9	5	148995	56760	5940	180	1
10	6	135751	66220	9460	440	5
11	7	123410	74046	13545	860	15
12	8	111930	80360	18081	1470	35
13	9	101270	85280	22960	2296	70
14	10	91390	88920	28080	3360	126
15	11	82251	91390	33345	4680	210
16	12	73815	92796	38665	6270	330
17	13	66045	93240	43956	8140	495
18	14	58905	92820	49140	10296	715
19	15	52360	91630	54145	12740	1001
20	16	46376	89760	58905	15470	1365
21	17	40920	87296	63360	18480	1820
22	18	35960	84320	67456	21760	2380
23	19	31465	80910	71145	25296	3060
24	20	27405	77140	74385	29070	3876
25	21	23751	73080	77140	33060	4845
26	22	20475	68796	79380	37240	5985
27	23	17550	64350	81081	41580	7315
28	24	14950	59800	82225	46046	8855
29	25	12650	55200	82800	50600	10626
30	26	10626	50600	82800	55200	12650
31	27	8855	46046	82225	59800	14950
32	28	7315	41580	81081	64350	17550
33	29	5985	37240	79380	68796	20475
34	30	4845	33060	77140	73080	23751
35	31	3876	29070	74385	77140	27405
36	32	3060	25296	71145	80910	31465
37	33	2380	21760	67456	84320	35960
38	34	1820	18480	63360	87296	40920
39	35	1365	15470	58905	89760	46376
40	36	1001	12740	54145	91630	52360
41	37	715	10296	49140	92820	58905
42	38	495	8140	43956	93240	66045
43	39	330	6270	38665	92796	73815
44	40	210	4680	33345	91390	82251
45	41	126	3360	28080	88920	91390
46	42	70	2296	22960	85280	101270
47	43	35	1470	18081	80360	111930
48	44	15	860	13545	74046	123410
49	45	5	440	9460	66220	135751
50	46	1	180	5940	56760	148995
Plan2

Cell Formulas
Range	Formula
D5:H50	D5	=IF(D$4>$C5,0,COMBIN($C5-1,D$4-1)*COMBIN(50-$C5,5-D$4))

Hope this helps

M..

Marcelo Branco, I tried to find solution and made hundred of different formula but did not get any answer so I thought it must be solved by a huge formula or possible with macro so I ask here.

I am amazed to see how you have solved with such tiny formula a huge query it is so wonderful and when I summed each row putting formula in cell I5 =SUM(D5:H5) and copied down got sum for every row 211.876 got more surprised

I appreciate your assist and time you spent to solve my query by giving a nice and precise formula. ?

Good Luck!

Kind Regards,
Moti

 

[Marcelo Branco]

You are welcome. Glad to help.

M,