Formula to meet multiple criteria and perform calculation

[rockybell_77]

In below example, I want to define a formula in each cell of column ‘L’ (first in ‘P2’). I want to first search the products in (column A), which are having ‘Equipments’ populated in ‘column I’. For all products having equipments populated in column I, I want to find single maximum ‘weightage’ value in column G. The product which is having maximum ‘weightage’, I want to Multiply the ‘Dose’ value from ‘column C’; with B2 (Kg of first product) and divide by C2 (Dose of first product).

In case more then one product found having maximum weightage (Column G value) , then formula shall consider the product having ‘Solubility’ (Column D) having higher value among the identified multiple products having highest weightage value.

This formula will be defined for each cell in column L and corresponding product values will change (Column B ‘Kg’ for multiplication and Column C ‘Dose’ for division). Also similar search for other columns like M,N and so on s

Column-A Product B Kg C Dose D Soluility E Toxicity F potency G weighatge H Wt I Equipment-1 <tbody> </tbody> J Equipment-2 <tbody> </tbody> K Equipment-3 <tbody> </tbody> alpha 163.8 1 2 5 1 41 630 RMG 250 L Beta 125 100 2 4 1 36 500 RMG 600L Gama 250 10 2 4 1 41 500 RMG 600L Delta 265 25 2 4 1 61 530 RMG 600L Theta 265 250 2 4 1 36 530 RMG 600L Sita 132.5 50 2 4 1 42 1060 RMG 600L Zita 506 100 2 4 1 36 1150 RMG 600L Pie 840 5 3 4 1 61 525 RMG 50L SI 840 20 2 4 1 36 1050 RMG 1200L Chi 840 1 3 4 1 61 525 RMG 50L Ki 456 10 2 4 1 36 380 RMG 1200L Li 37.5 200 4 3 4 43 RMG 50L MY 18.75 2 5 3 3 39 75 NY 18.75 5 1 3 3 39 150 RMG 50L <colgroup><col><col span="2"><col span="2"><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[cyrilbrd]

Hi,
In your sample, Pie, Chi, Li and Ny are eligible.
Then Pie and Chi have the same max weightage of 61 AND both have a solubility of 3...
Question is how do you handle these exemptions?

 

[rockybell_77]

Hi,
In your sample, Pie, Chi, Li and Ny are eligible.
Then Pie and Chi have the same max weightage of 61 AND both have a solubility of 3...
Question is how do you handle these exemptions?

In situation like same weightage and solubility, the formula shall check, if the product name (among the 2 in this case or more with same weightage and solubility) is same then shall select the first occurrence of value in the list, if product name is different as in this case else shall select lower Value in Column 'C' i.e. Dose

Thanks for ur reply..and sorry for delay in providing the clarity. Expecting quick response.