Bill of materials quantity

[nigelbloomy]

I'm using Excel 2007 on Windows XP. I have many different Bill of materials that come to me in the format below. I would like a formula in column D that can tell me the total quantity that will be used to make the top level part. For example: For part e, I will use 1 * .6(at level 2) *1 (at level 1). So I will use a total of .6 of part e to make my top level part a.

I tried using sum product to find anything lower than each level. My problem with that is when I multiply the quantity of part e by all part levels lower than 3 I get the .6 from the correct level 2, but I also get the .5 from the the other level 2 (part b).

I have been trying formulas to do this, but I think it may need VBA code to get it right.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Level[/TD]
[TD]Part[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[TD].5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[TD].01[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]d[/TD]
[TD].6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]e[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]f[/TD]
[TD].2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]g[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]h[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for your help.

 

[shg]

Welcome to the board.

      --A-- -B-- ---C---- --D---
  1   Level Part Quantity  Ext  
  2      1  a          1      1 
  3      2  b        0.5    0.5 
  4      3  c       0.01  0.005 
  5      2  d        0.6    0.6 
  6      3  e          1    0.6 
  7      4  f        0.2   0.12 
  8      5  g          3   0.36 
  9      4  h          6    3.6

The formula in D2 and copied down is

=C2*IF(A2=1, 1, LOOKUP(2, 1/(A$1:A2=A2-1), D$1:D1))

 

[Marcelo Branco]

Welcome to the board.

      --A-- -B-- ---C---- --D---
  1   Level Part Quantity  Ext  
  2      1  a          1      1 
  3      2  b        0.5    0.5 
  4      3  c       0.01  0.005 
  5      2  d        0.6    0.6 
  6      3  e          1    0.6 
  7      4  f        0.2   0.12 
  8      5  g          3   0.36 
  9      4  h          6    3.6

The formula in D2 and copied down is

=C2*IF(A2=1, 1, LOOKUP(2, 1/(A$1:A2=A2-1), D$1:D1))

Nice!!!

M.

 

[shg]

Thank you, Marcelo. Or slightly differently,

=C2*IF(A2=1, 1, LOOKUP(9E307, D$1:D1/(A$1:A2=A2-1)))

 

[nigelbloomy]

shg you are incredible. I have spent weeks trying to figure this out using offset, match and sumproduct functions. I don't know how to thank you enough for figuring this out so quickly.

 

[shg]

You're welcome, glad it helped.

 

[rt2017]

Hello, I'm using Excel 2010 and I have a similar problem for a more complex bill of materials.


My BOM use top level assemblies, sub-assemblies and sub-sub-assemblies, and sub-components.


For example:


Item Code Quantity


1 A 2


1.1 B 2


1.2 B2 4


1.2.1 B21 2


1.3 B3 2


2 C1 1


2.1 C4 5


I need to create a fourth column with the total quantity for the main assembly. So, for example, we will need 16 items from 1.2.1 (Multiplying 2 by 4 and by 2).


Is there a similar equation that we can use to calculate automaticaly?


Thank you very much!