Usage Allocation Formula

[Bkisley]

Hello everyone!


Tab 1 called "Component Matrix"
I have a list of all my components vertically in Column C --> C5:C11
I have a list of all my programs in column F4:J4
See below for what I mean - This table represents a usage matrix.
Program 2 has 1 of each of the following components - 1, 3,4,5 and 7
[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD="align: center"]Component Name[/TD]
[TD="align: center"]Program 1[/TD]
[TD="align: center"]Program 2[/TD]
[TD="align: center"]Program 3[/TD]
[TD="align: center"]Program 4[/TD]
[TD="align: center"]Program 5[/TD]
[/TR]
[TR]
[TD="align: center"]Component 1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Component 2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Component 3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Component 4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Component 5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Component 6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Component 7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Tab 2 called "Allocation"
Here is a sample equation
Program 2 component 4 = 50,000* (100,000*(50,000+100000)
Take the tooling and multiply it against that program's volume divided by the total volume of that component

[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]Lifetime Volume -->[/TD]
[TD="align: center"]50,000[/TD]
[TD="align: center"]100,000[/TD]
[TD="align: center"]150,000[/TD]
[TD="align: center"]200,000[/TD]
[TD="align: center"]250,000[/TD]
[/TR]
[TR]
[TD="align: center"]Component Name[/TD]
[TD="align: center"]Component Tooling[/TD]
[TD="align: center"]Program 1[/TD]
[TD="align: center"]Program 2[/TD]
[TD="align: center"]Program 3[/TD]
[TD="align: center"]Program 4[/TD]
[TD="align: center"]Program 5[/TD]
[/TR]
[TR]
[TD="align: center"]Component 1[/TD]
[TD="align: center"]10,000[/TD]
[TD="align: center"][/TD]
[TD="align: center"]10,000[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Component 2[/TD]
[TD="align: center"]20,000[/TD]
[TD="align: center"]2,222[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6,667[/TD]
[TD="align: center"][/TD]
[TD="align: center"]11,111[/TD]
[/TR]
[TR]
[TD="align: center"]Component 3[/TD]
[TD="align: center"]30,000[/TD]
[TD="align: center"]3,000[/TD]
[TD="align: center"]6,000[/TD]
[TD="align: center"]9,000[/TD]
[TD="align: center"]12,000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Component 4[/TD]
[TD="align: center"]40,000[/TD]
[TD="align: center"]13,333[/TD]
[TD="align: center"]16,667[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Component 5[/TD]
[TD="align: center"]50,000[/TD]
[TD="align: center"][/TD]
[TD="align: center"]11,111[/TD]
[TD="align: center"]16,667[/TD]
[TD="align: center"]22,222[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Component 6[/TD]
[TD="align: center"]60,000[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]22,500[/TD]
[TD="align: center"][/TD]
[TD="align: center"]37,500[/TD]
[/TR]
[TR]
[TD="align: center"]Component 7[/TD]
[TD="align: center"]70,000[/TD]
[TD="align: center"][/TD]
[TD="align: center"]70,000[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



I need an equation that first does the second table and then sums up all the program 1 costs. Same for program 2,3,.....

Is this possible!?