Forumla Required

[wchan83]

Here is my problem. I have a excel table with a list of items and their chemical components, commitment quality as shown below.

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">[COLOR=#000000]Example[/COLOR][COLOR=#000000]:[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#000000]=====================================================================[/COLOR][COLOR=#000000]
    Column A              Column B                 Column C             
  Product Name          Materials             Commitment Quantity    
[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000] Metallic Alloy    [/COLOR][COLOR=#800000]87[/COLOR][COLOR=#000000]% Metallic, 13%[/COLOR][COLOR=#000000] Alloy         [/COLOR][COLOR=#800000]30000[/COLOR][COLOR=#000000]             
[/COLOR][COLOR=#800000]2[/COLOR][COLOR=#000000] Silver[/COLOR][COLOR=#000000]-[/COLOR][COLOR=#000000]Gold       [/COLOR][COLOR=#800000]56[/COLOR][COLOR=#000000]% Silver, 44%[/COLOR][COLOR=#000000] Gold            [/COLOR][COLOR=#800000]50000[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#800000]3[/COLOR][COLOR=#000000] Metal Silver      [/COLOR][COLOR=#800000]50[/COLOR][COLOR=#000000]% Metallic, 50%[/COLOR][COLOR=#000000] Silver        [/COLOR][COLOR=#800000]10000[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#000000]=====================================================================[/COLOR][COLOR=#000000]
                               Total Commitment Quantity [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]90000[/COLOR]</code>

I want to know if there is any excel function or formula, such that it can automatically generate a break down report for each chemical component with percentage distribution over the whole market as below. Now I just doing it manually as below, but it would be a pain if the list is long. Is any simple way to do it? All I need is the chemical components percentage over the total commitment quantity. So I can make a chart for that. In our case that is [34.5% Metallic, 4.3% Alloy, 36.7% Silver , 24.4% Gold].

[COLOR=#000000][FONT=Consolas] Example[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]:[/FONT][/COLOR]
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">[COLOR=#000000]======================================================================[/COLOR][COLOR=#000000]
       Column D         Column E          Column F       Column G
       Metallic          Alloy             Silver          Gold
     [/COLOR][COLOR=#800000]1 [/COLOR][COLOR=#800000]30000[/COLOR][COLOR=#000000]*[/COLOR][COLOR=#800000]87[/COLOR][COLOR=#000000]%        30000*13%             [/COLOR][COLOR=#000000]-              -[/COLOR][COLOR=#000000]
     [/COLOR][COLOR=#800000]2 [/COLOR][COLOR=#800000]50000[/COLOR][COLOR=#000000]*[/COLOR][COLOR=#800000]56[/COLOR][COLOR=#000000]%        50000*44%             -              -[/COLOR][COLOR=#000000]
     [/COLOR][COLOR=#800000]3 [/COLOR][COLOR=#800000]10000[/COLOR][COLOR=#000000]*[/COLOR][COLOR=#800000]50[/COLOR][COLOR=#000000]%            -             10000*50%          [/COLOR][COLOR=#000000]-[/COLOR][COLOR=#000000]

     [/COLOR][COLOR=#000000]**[/COLOR][COLOR=#000000]Total[/COLOR][COLOR=#000000]**[/COLOR][COLOR=#000000] 
        31[/COLOR][COLOR=#800000]100             [/COLOR][COLOR=#800000]3900             [/COLOR][COLOR=#800000]33000            [/COLOR][COLOR=#800000]22000[/COLOR][COLOR=#000000]

     [/COLOR][COLOR=#000000]**[/COLOR][COLOR=#000000]Percentage Distribution[/COLOR][COLOR=#000000]**[/COLOR][COLOR=#000000]
      [/COLOR][COLOR=#800000]31100[/COLOR][COLOR=#000000]/[/COLOR][COLOR=#800000]90000        [/COLOR][COLOR=#800000]3900[/COLOR][COLOR=#000000]/[/COLOR][COLOR=#800000]90000      [/COLOR][COLOR=#800000]33000[/COLOR][COLOR=#000000]/[/COLOR][COLOR=#800000]90000     [/COLOR][COLOR=#800000]22000[/COLOR][COLOR=#000000]/[/COLOR][COLOR=#800000]90000[/COLOR][COLOR=#000000]
         [/COLOR][COLOR=#800000]34.5[/COLOR][COLOR=#000000]%             4.3%             [/COLOR][COLOR=#800000]36.7[/COLOR][COLOR=#000000]%           24.4%[/COLOR][COLOR=#000000]
        Metallic           Alloy            Silver          Gold 
[/COLOR]</code>[COLOR=#000000][FONT=Consolas]=========================================================================
[/FONT][/COLOR]

 

[Sandeep Warrier]

Hi & Welcome to the Board,

Does this help?

Excel 2010
	A	B	C	D	E	F	G
1	Product Name	Materials	Commitment Quantity	Metallic	Alloy	Silver	Gold
2	Metallic Alloy	87% Metallic, 13% Alloy	30000	26100	3900	-	-
3	Silver-Gold	56% Silver, 44% Gold	50000	-	-	28000	22000
4	Metal Silver	50% Metallic, 50% Silver	10000	5000	-	5000	-
5		Total	90000	31100	3900	33000	22000
6		Percentage Distribution		34.56%	4.33%	36.67%	24.44%
Sheet1

Cell Formulas
Range	Formula
D2	=IF(ISNUMBER(SEARCH(D$1,$B2)),$C2*((MID($B2,SEARCH(D$1,$B2)-4,2)+0)/100),"-")
D5	=SUM(D2:D4)
D6	=D$5/$C$5