Formula to meet multiple criteria and perform calculation

  • Hello
    I have a data given below. I want to do the following-
    Define formula after Equipment-3 column such that, search which 'Product' use Equipment-1, then find highest 'weightage' among the product uses 'Equipment-1' and then pick the 'Dose' of the highest 'weightage' value product and muliply with 'batch' of the product in that row and divide by 'Dose in that row. Like in below case forrmula shall search that Product A & D uses R-50 and Product D has maximum Weighatge (64), Hence in row of product 'A' in cell next to 'Equipment-3', automatically formula shall multiply 'Dose' of D i.e. 250 with Batch of 'A' i.e. 150 and divide bby Dose of 'A' i.e. 5...This is to bbe done of each column "equipment-1, 2 and 3...so on. Please help


    [RDV][TABLE="class: cms_table, width: 500, align: left"]

    [tr]


    [td]

    Product

    [/td]


    [td]

    Batch

    [/td]


    [td]

    Dose

    [/td]


    [td]

    weightage

    [/td]


    [td]

    Equipment-1

    [/td]


    [td]

    Equipment-2

    [/td]


    [td]

    Equipment-3

    [/td]


    [/tr]


    [tr]


    [td]

    A

    [/td]


    [td]

    150

    [/td]


    [td]

    5

    [/td]


    [td]

    55

    [/td]


    [td]

    R-50

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    B

    [/td]


    [td]

    60

    [/td]


    [td]

    100

    [/td]


    [td]

    61

    [/td]


    [td]

    [/td]


    [td]

    R250

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    c

    [/td]


    [td]

    500

    [/td]


    [td]

    1

    [/td]


    [td]

    54

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    R500

    [/td]


    [/tr]


    [tr]


    [td]

    D

    [/td]


    [td]

    200

    [/td]


    [td]

    250

    [/td]


    [td]

    61

    [/td]


    [td]

    R-50

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    E

    [/td]


    [td]

    10

    [/td]


    [td]

    250

    [/td]


    [td]

    54

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    R500

    [/td]


    [/tr]


    [/TABLE]

    [/RDV]