Forum Discussion
Solved
Matrix lookup and summing of components formula help
Good day. I am trying to do some calculations on a matrix of products to assemble (column A) and the components they are comprised of (row 1). Some of these components are used on multiple products. What I would like to do is have 2 lists, 1 of the products to assemble during the day and the other a list of all components needed to accomplish the tasks.
I have the matrix completed and filled out with the number of each component required for assembly. On another sheet I created a list of the products (column A) and Qty to build (column B) as well as a list of components (column G) and Qty needed for builds (column H).
I thought that it might be possible to use an IF statement to make sure column B was > 0, then do a lookup based off column A of both sheets and have it then match the component name in column g with row 1 of the matrix sheet. Then it would need to sum all the components for all the products and possibly multiply them.
I was working on just getting the lookup portion working and am probably in over my head and could use help. I have attached screenshots of a sample of what I'm working on, as I'm sure I didn't explain it very well.
Thank you to anyone who might have better methods or can help in any way.
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.
Oops sorry Peter
Beyond the new potent limit darn it n i l p o n t e t darn it and i l p o t e n t there it is
Beyond the limit no summation is necessary because the matrix of resolves to zero at that point
Something all the lower Powers summing all the lower Powers excuse me gives you all the multi-stage requirements for any order vector
What a marvelous tool mathcad
Sorry I don't know how to do this in 365
- Masscat allows import darn it
Mathcad allows import and export of data files but the really fast way to do it for those data files which consist of matrices and especially if the limit is 255 that is all data values are between 0 and 2:55 is to import export a bitmap enough said much faster
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
First and foremost, thank you. It appears that the MMULT with the TRANSPOSE should fit perfectly for what is needed.
Secondly, thank you for showing me that you can define ranges of data to make it easier to call up when working with formulas, I never even realized that was possible.
I attempted to recreate your sample within my example linked from my OneDrive, but am just getting the #VALUE! error, so not sure exactly what I'm doing differently.
I'll check into it a little more tomorrow and see if I can figure out what's going on to get it working on my workbook.
kurtlee MMULT is a very picky function in that it does not fail gracefully. If the number of product rows/columns in the two tables do not match the function will crash and burn rather than reporting errors in the final elements to be returned.
Another catch is that it does not accept blank cells as zeros; the 0 must be entered explicitly. Otherwise you would need a formula step to test each array, element by element, and return 0 if the test fails (the element value otherwise).
= MMULT(TRANSPOSE(compsPerProduct), IF(ISNUMBER(productQuantity), productQuantity, 0))or a 365 version
= LET( cleanedVector, IF(ISNUMBER(productQuantity), productQuantity, 0), cleanedArray, IF(ISNUMBER(compsPerProduct), compsPerProduct, 0), MMULT(TRANSPOSE(compsPerProduct), cleanedVector) )
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Thank you for the reply. I tried to attach an example when I originally posted but couldn't figure out how and still can't. Below is a link to OneDrive and the example I took the screenshots from.
