Forum Discussion
Matrix lookup and summing of components formula help
The mathematical approach for this problem is to use MMULT for the matrix multiplication
= MMULT(TRANSPOSE(compsPerProduct), productQuantity)Sadly the formula is unlikely to appeal to those without a STEM background. There is an alternative using 365 array formulas.
= LET( componentsNeeded, productQuantity * compsPerProduct, neededOverall, BYCOL(componentsNeeded, LAMBDA(x, SUM(x))), TOCOL(neededOverall) )The first line simply multiplies the component table by the number of products required to determine the component count for each product. Next, summing by column gives the overall component counts. The result is a row of counts so the final step converts it to a column. Fortunately the two methods give identical results.
The mathematical approach for this problem is to use MMULT for the matrix multiplication
= MMULT(TRANSPOSE(compsPerProduct), productQuantity)
Sadly the formula is unlikely to appeal to those without a STEM background. There is an alternative using 365 array formulas.
= LET(
componentsNeeded, productQuantity * compsPerProduct,
neededOverall, BYCOL(componentsNeeded, LAMBDA(x, SUM(x))),
TOCOL(neededOverall)
)
The first line simply multiplies the component table by the number of products required to determine the component count for each product. Next, summing by column gives the overall component counts. The result is a row of counts so the final step converts it to a column. Fortunately the two methods give identical results.
A method of matrix exponentiation includes the possibility that one of the assemblies is used in another assembly in addition to the possibility which you stated, which is that various parts are used in different assemblies.
I first read of this in a textbook from approximately 1969 that I got it a thrift store. It is part of the Prentice Hall quantitative methods series. I believe the series is still in print there. The title is finite mathematics with business applications and the primary author is Kennedy incorrect let me spell that k e m e n y there you go.
In the chapter on the parts requirements counting matrix method matrix exponentiation is used to establish an inverse which leads to a solution for peculiar problem one might encounter there when one would wish to produce a set of products and ship a different set of products which is a little higher level than what you're talking about
So if you wouldn't mind answering whether any of the assemblies you are assembling are used again in other assemblies that would clarify whether the exponentiation method is applicable
Even if no assemblies are reused in other assemblies, the exponentiation method accounts for the fundamental structure of all assembly manufacturing which is that there will be an irreducible set of parts from which subsets may be drawn to produce assemblies which in general have part numbers as assemblies
The method does not track part numbers
However, the exponentiation step is extremely useful. I'll describe it a little for you. You mentioned that zeros must be entered explicitly. In the software I use for matrix exponentiation, that happens automatically. For instance to create a matrix 9 by 9 filled with zeros one would just say these statements. They are in math kids 6.0 Plus format excuse me that's meth gag good grief mathcad there you go:
Origin equals one
Q sub 9, 9 = 0
And there it is a matrix with 81 elements all zeros
Now let's say that the first five rows of the matrix are the irreducible parts. Just for fun we'll call them ABCD and E
The first Assembly would be on row 6
What do you need one of a and 3 of B to make one of f
See how that works
On the 6th row of The matrix you'd put a one in the first column and a three in the second column and nothing else leaving all the other zeros
So you wanted to produce five of part f
You would set up a column vector call it v with a five in the 6th position indicating that no orders are being placed for the irreducible parts and one order is placed consisting of quantity 5 of item number 6 which is part f and assembly of A and B
Some left multiplication of the vector times The matrix produces the instant result V2 which is a vector containing 5 in the first position 10 in the second position if I got that right and then a zero in the six position
This is called first stage or single stage multiplication
Let's say part f is reused in an assembly g which in turn consists of one f two a and 3C
Well what you do is you square the matrix and multiply by the order vector left multiply again
That's the two stage not the summed stage requirements
All you have to do
Follow through all the stages
The matrix will be lower left diagonal and therefore nail potent no potent darn it and i l p o t e n t **bleep** it darn it n i l p o t e n t there you go
Since it is nail potent there is an upper level to the exponentiation
Summing as you go, you form the first power and add it to the accumulate, the second power add that in then the third etc up to the nth power which is the limit beyond which no potency darn it applies and beyond which no summation is necessary