Total Bill of Materials Quantity Calculation

Question

I am currently working on an excel spreadsheet of engineering bill of materials. Please reference the example below:

1 main assembly Qty - 1 Total Qty - 1;
1.1 sub assembly Qty - 2 Total Qty - Qty of 1.1 * Total Qty of 1 = 2;
1.1.1 parts involved in the sub assembly Qty - 4 Total Qty - 8;
1.1.2 part Qty - 2 Total Qty - Qty of 1.1.2 * Total Qty of 1.1 = 4;
1.1.3 part Qty - 4 Total Qty - Qty of 1.1.3 * Total Qty of 1.1 = 8;

Is there a function or formula to calculate the total quantity of each part for that sub assembly and assembly specific. As there are more than 1000 rows in the spread sheet if there is a way other than manually doing it would save up lot of time and prevent errors. I have attached the image of what I am trying to achieve.

user_10962789 · Answer

Try:

=LET(x, LEN($A2)-LEN(SUBSTITUTE($A2,".","")),
y, LEFT($A2, FIND("!", SUBSTITUTE($A2, ".", "!", x), 1)-1),
IF(x=0, $B2,
$B2*INDEX($A$2:$C$14, MATCH(y, TEXT($A$2:$A$14, "General"),0),3)))

The steps:

We use the LET function to name variables.
We count the number of instances a decimal appears (indicating the parent-child relationship) with LEN($A2)-LEN(SUBSTITUTE($A2,".","")) and call this count "x".
We find the parent of the last child using LEFT($A2, FIND("!", SUBSTITUTE($A2, ".", "!", x), 1)-1). Note that the right part of the equation finds the last instance of a decimal and that the character '!' is chosen at random; it could be anything that wouldn't normally appear in the string.
We finally use an IF statement. If it is the main parent, the total quantity is just the quantity, otherwise it is the quantity multiplied by the parent's total quantity. Note that we wrap the range we are looking up in the INDEX\MATCH as a text string and use "3" as a fixed reference only because we know the position of the "Total QTY" column and don't anticipate it moving.

EDITED IMAGE TO SHOW FUNCTION IN ACTION ON NEW DATA:

Total Bill of Materials Quantity Calculation

1 Answers