Results 1 to 6 of 6

Help with logic formulas with array

This is a discussion on Help with logic formulas with array within the Excel Questions forums, part of the Question Forums category; Ok this is somewhat tough, I consider myself to be handy with Excel and can't figure it out. I have ...

  1. #1
    New Member
    Join Date
    Nov 2008
    Posts
    2

    Default 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. #2
    Board Regular
    Join Date
    Jul 2006
    Posts
    93

    Default Re: Help with logic formulas with array

    Quote Originally Posted by davidfredc View Post
    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. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,332

    Default 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
    =

    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.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    New Member
    Join Date
    Nov 2008
    Posts
    2

    Default Re: Help with logic formulas with array

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

  5. #5
    jyk
    jyk is offline
    Board Regular
    Join Date
    Oct 2008
    Posts
    54

    Default 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. #6
    Board Regular
    Join Date
    Jul 2006
    Posts
    93

    Default 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/

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com