SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2597209%22%20slang%3D%22en-US%22%3ETotal%20Amount%20divided%20into%20Different%20Sequences%20using%204%20slot%20only%20in%20each%20sequences%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2597209%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EPlease%20help%20on%20how%20this%20was%20done.%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EThere%20were%205%20different%20Slots%20with%20given%20amounts%20on%20the%20right%20(F2%3AG6)%2C%20and%20it%20was%20distributed%20in%205%20sequences%20as%20shown%20below.%20In%20each%20sequences%20I%20can%20only%20use%204%20slots%20and%20each%20amount%20must%20be%20equal.%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EI%20mean%20is%20if%20B2%3AB21%20Slot%20is%20fixed%2C%20How%20would%20I%20calculate%20the%20amount%20in%20C2%3AC21%20if%20it%20is%20left%20blank%3F%3F%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EThe%20result%20amount%20must%20be%20the%20same%20in%20D2%3AD21.%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EI%20already%20found%20one%20thing%20that%3A%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3ESequence%201%20%3D%204%2C160%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3ESeq%202%20%3D%205%2C784%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3ESeq%203%3D%203%2C763%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3ESeq%204%20%3D%205%2C270%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3ESeq%205%20%3D%205%2C981%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3ETotal%20%3D%2024%2C958%20which%20is%20exactly%2025%25%20of%2099%2C832.%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EBut%20What%20if%20those%20Sequences%20amount%20is%20empty%2Fblank%3F%3F%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EHow%20do%20I%20know%20that%20is%20the%20number%20to%20put%20into%20each%20Sequences%3F%3F%20Like%20how%20do%20I%20know%20Sequence%201%3D4160%3F%3F%2C%20etc...%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EThis%20is%26nbsp%3B%20driving%20me%20crazy..%20Please%20give%20some%20advice..%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EThanks%20a%20lot.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Solving%20Problem.JPG%22%20style%3D%22width%3A%20895px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F299733iF80CDF774D62F0A9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Solving%20Problem.JPG%22%20alt%3D%22Solving%20Problem.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2597209%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2597447%22%20slang%3D%22en-US%22%3ERe%3A%20Total%20Amount%20divided%20into%20Different%20Sequences%20using%204%20slot%20only%20in%20each%20sequences%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2597447%22%20slang%3D%22en-US%22%3EWell%20First%2C%20Thank%20you%20very%20much.%20You%20are%20a%20lifesaver%2C%20the%20problem%20was%20Solved.%3CBR%20%2F%3EI%20need%20to%20study%20this%20Matrix%20equation%20though%20coz%20I%20don't%20have%20a%20clue%20how%20it%20actually%20works%20lol..%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20again%2C%20I%20feel%20a%20lot%20better%20now%20lols...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2597257%22%20slang%3D%22en-US%22%3ERe%3A%20Total%20Amount%20divided%20into%20Different%20Sequences%20using%204%20slot%20only%20in%20each%20sequences%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2597257%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1117618%22%20target%3D%22_blank%22%3E%40Yvan_New1976%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20a%20mathematical%20matrix%20problem.%20Let's%20denote%20the%20amounts%20for%20sequence%201%20to%205%20by%20x1%20to%20x5.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0636.png%22%20style%3D%22width%3A%20197px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F299745iE0D6444542AD429D%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0636.png%22%20alt%3D%22S0636.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EWe%20have%20to%20solve%20the%20following%20linear%20equations%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0637.png%22%20style%3D%22width%3A%20129px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F299746i0DFE18C02552711F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0637.png%22%20alt%3D%22S0637.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EWe%20can%20write%20this%20as%20a%20matrix%20equation%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0638.png%22%20style%3D%22width%3A%20166px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F299747i009E4CFA856710DB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0638.png%22%20alt%3D%22S0638.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThe%20solution%20is%20found%20by%20multiplying%20the%20inverse%20of%20the%20left-hand%20matrix%20with%20the%20right-hand%20one%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0639.png%22%20style%3D%22width%3A%20280px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F299748i4E1BA4E9556B322D%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0639.png%22%20alt%3D%22S0639.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2607627%22%20slang%3D%22en-US%22%3ERe%3A%20Total%20Amount%20divided%20into%20Different%20Sequences%20using%204%20slot%20only%20in%20each%20sequences%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2607627%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%20Hans%2C%20I%20hope%20you%20are%20doing%20fine..%3C%2FP%3E%3CP%3EI%20have%20tried%20to%20use%20a%209x6%20matrix%20equation%2C%20but%20it%20is%20not%20working.%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20tell%20me%20if%20this%20is%20not%20possible%3F%3F%20and%20Should%20it%20be%20a%209x9%20matrix%20equation%20to%20removed%20the%20error%3F%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%229x6%20Matrix%20Equation.JPG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F300316iFE517B80CC30F015%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%229x6%20Matrix%20Equation.JPG%22%20alt%3D%229x6%20Matrix%20Equation.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20Hans.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2608726%22%20slang%3D%22en-US%22%3ERe%3A%20Total%20Amount%20divided%20into%20Different%20Sequences%20using%204%20slot%20only%20in%20each%20sequences%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2608726%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1117618%22%20target%3D%22_blank%22%3E%40Yvan_New1976%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20don't%20have%209%20variables%20x1...x9%2C%20but%20only%206%3A%20the%20amounts%20for%20each%20of%20the%206%20sequences.%20But%20with%209%20slots%2C%20you%20now%20have%209%20equations%20for%20those%206%20variables.%20There%20is%20no%20feasible%20solution.%3C%2FP%3E%0A%3CP%3EI%20tried%20to%20find%20a%20solution%20using%20the%20Solver%20add-in%2C%20but%20it%20couldn't%20find%20one.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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.

 

Solving Problem.JPG

5 Replies
best response confirmed by Yvan_New1976 (Occasional Contributor)
Solution

@Yvan_New1976 

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

S0636.png

We have to solve the following linear equations:

S0637.png

We can write this as a matrix equation:

S0638.png

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

S0639.png

 

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...

@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??

9x6 Matrix Equation.JPG

 

Thanks Hans.

@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.

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

Thanks.