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.

[#2 · Accepted solution]

@Yvan_New1976 

It's a mathematical matrix problem. Let's denote the amounts for sequence 1 to 5 by x1 to x5.

We have to solve the following linear equations:

We can write this as a matrix equation:

The solution is found by multiplying the inverse of the left-hand matrix with the right-hand one:

[#3]

Well First, Thank you very much. You are a lifesaver, the problem was Solved.
I need to study this Matrix equation though coz I don't have a clue how it actually works lol..

Thanks again, I feel a lot better now lols...

[#4]

@Hans Vogelaar 

Hello Hans, I hope you are doing fine..

I have tried to use a 9x6 matrix equation, but it is not working. 

Can you tell me if this is not possible?? and Should it be a 9x9 matrix equation to removed the error??

Thanks Hans.

[#5]

@Yvan_New1976 

You don't have 9 variables x1...x9, but only 6: the amounts for each of the 6 sequences. But with 9 slots, you now have 9 equations for those 6 variables. There is no feasible solution.

I tried to find a solution using the Solver add-in, but it couldn't find one.

[#6]

Yeah I've seen that one too..
That is okay.. You have helped me a lot already.

Thanks.