Total Amount divided into Different Sequences using 4 slot only in each sequences

[Yvan_new1976]

Please help on how this was done.

There were 5 different Slots with given amounts on the right (F2:G6), and it was distributed in 5 sequences as shown below. In each sequences I can only use 4 slots and each amount must be equal.

I mean is if B2:B21 Slot is fixed, How would I calculate the amount in C2:C21 if it is left blank??
The result amount must be the same in D2:D21.


I already found one thing that:
Sequence 1 = 4,160
Seq 2 = 5,784
Seq 3= 3,763
Seq 4 = 5,270
Seq 5 = 5,981

Total = 24,958 which is exactly 25% of 99,832.

But What if those Sequences amount is empty/blank??
How do I know that is the number to put into each Sequences?? Like how do I know Sequence 1=4160??, etc...


This is driving me crazy.. Please give some advice..

Thanks a lot.

 

[jorismoerings]

Hi and welcome to MrExcel,

Correct me if my assumption is incorrect.

If the given is "5 different Slots with given amounts" combined with "only use 4 slots and each amount must be equal" and "Slot is fixed"
My assumption is the slot 1 amount in sequence 1 should be different from the slot 1 amount in sequence 2 exactly like your example.
Although in your example the distribution of the amount of slot 1 over all sequences does not come back to the total mentioned in G2.

This what i came up with:

slot size.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	1	1		4940			1	18977	1,2,3,4
3		3		4940			3	19688	1,2,3,5
4		5		4940			5	21195	1,2,4,5
5		7	4160	4940			7	19174	1,3,4,5
6	2	1		5041			9	20798	2,3,4,5
7		3		5041				99832
8		5		5041
9		9	5784	5041
10	3	1		4915
11		3		4915
12		7		4915
13		9	3763	4915
14	4	1		5009
15		5		5009		Slot →		1	3	5	7	9	Total Slot	Per Seq.	Per Slot/Seq
16		7		5009		Sequence ↓	1	18977	19688	21195	19174		79034	19759	4940
17		9	5270	5009			2	18977	19688	21195		20798	80658	20165	5041
18	5	3		5053			3	18977	19688		19174	20798	78637	19659	4915
19		5		5053			4	18977		21195	19174	20798	80144	20036	5009
20		7		5053			5		19688	21195	19174	20798	80855	20214	5053
21		9	5981	5053				18977	19688	21195	19174	20798		99832
22				99832								99832	TRUE
Sheet1

Cell Formulas
Range	Formula
H7,N21	H7	=SUM(H2:H6)
M16:M20	M16	=SUM(H16:L16)
N16:O20	O16	=N16/4
H21:L21	H21	=SUM(H16:H20)/4
D2,D6,D10,D14,D18	D2	=SUMPRODUCT((IF(ISNUMBER(MATCH($G$2:$G$6,B2:B5,0)),$G$2:$G$6,99)=$G$2:$G$6)*($H$2:$H$6))/16
D3,D7,D11,D15,D19	D3	=SUMPRODUCT((IF(ISNUMBER(MATCH($G$2:$G$6,B2:B5,0)),$G$2:$G$6,99)=$G$2:$G$6)*($H$2:$H$6))/16
D4,D8,D12,D16,D20	D4	=SUMPRODUCT((IF(ISNUMBER(MATCH($G$2:$G$6,B2:B5,0)),$G$2:$G$6,99)=$G$2:$G$6)*($H$2:$H$6))/16
D5,D9,D13,D17,D21	D5	=SUMPRODUCT((IF(ISNUMBER(MATCH($G$2:$G$6,B2:B5,0)),$G$2:$G$6,99)=$G$2:$G$6)*($H$2:$H$6))/16
D22	D22	=SUM(D2:D21)
L22	L22	=SUM(H21:L21)
M22	M22	=L22=N21

Hope this helps.

 

[Yvan_new1976]

Hello jorismoerings.

Thanks you for trying to Solve my problem. Unfortunately, I have to match the total Slot amount because it was fixed amount and should be the one distributed.

But already found out which one to use, Unexpectedly it could be Solve using a matrix equation using Excel function MMULT and MINVERSE.

Thanks and best regards.

 

[smozgur]

Glad to hear you got the solution.

Do you mind posting about your solution in a bit more detail, like the solution formula? Then it is perfectly fine to mark your post as the solution to help future readers.

 

[jorismoerings]

Hi,

glad you’ve got the solution. Would be nice however I.ve you had mentioned you cross posted this question on several other forums ( e.g. Total Amount divided into Different Sequences using 4 slot only in each sequences )

@smozgur the cross link has the described solution.