Help with logic formulas with array

Thanks:  0
Likes:  0

# Thread: Help with logic formulas with array

1. ## Help with logic formulas with array

Ok this is somewhat tough, I consider myself to be handy with Excel and can't figure it out.

I have one sheet with 3 columns: Product name, raw material name, weight of raw material.

So in the first column you have the product listed several times for every raw material, then the weight of that raw material. I want to create another sheet with all the products as the header for the columns, and the raw materials on the side...I've done this but now I need a formula to populate this matrix with the weight values that correspond to the product and raw material.

I've tried a bunch of different 'if' / 'and' statements using array formulas. Pls help.

2. ## Re: Help with logic formulas with array

Originally Posted by davidfredc
Ok this is somewhat tough, I consider myself to be handy with Excel and can't figure it out.

I have one sheet with 3 columns: Product name, raw material name, weight of raw material.

So in the first column you have the product listed several times for every raw material, then the weight of that raw material. I want to create another sheet with all the products as the header for the columns, and the raw materials on the side...I've done this but now I need a formula to populate this matrix with the weight values that correspond to the product and raw material.

I've tried a bunch of different 'if' / 'and' statements using array formulas. Pls help.
Newbie here so...

Would a pivot table help?

3. ## Re: Help with logic formulas with array

Welcome to the board...

Here is a very basic example, both tables on the same sheet, but you should get the idea..

 Microsoft Excel - Personal.xls ___Running: 11.0 : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 G2H2I2G3H3I3 =

A
B
C
D
E
F
G
H
I
1
ProductRaw MaterialWeight   Product1Product2Product3
2
Product1A5  A51525
3
Product1B10  B102030
4
Product2A15
5
Product2B20
6
Product3A25
7
Product3B30
 Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

4. ## Re: Help with logic formulas with array

Pivot table works, I guess that was a really dumb question then.

5. ## Re: Help with logic formulas with array

I would also recommend trying a pivot table, and seeing if that gives you what you want.

Using a pivot table, you can create (for example) a matrix with the products listed along the top and the materials listed along the side. The cells of the matrix would then show the weights for each product-material pair. You can also set up the table to include whatever summary information you want (for example, the total weight of all the materials for one product, and/or the total weight of a single material for all products).

6. ## Re: Help with logic formulas with array

Wow! I got one right! Not bad for my first suggestion. Here's a link I used to try and learn about setting up a pivot table. Sounds like you got it down all ready though.

http://www.metacafe.com/watch/784047...in_excel_2003/

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•