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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 21"]
<colgroup><col><col span="2"><col span="2"><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Column-A

Product[/TD]
[TD]B


Kg[/TD]
[TD]C


Dose[/TD]
[TD]D


Soluility[/TD]
[TD]E


Toxicity[/TD]
[TD]F


potency[/TD]
[TD]G


weighatge[/TD]
[TD]H


Wt[/TD]
[TD]

I

[TABLE="class: grid, width: 21"]
<tbody>[TR]
[TD]Equipment-1

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]J


[TABLE="class: grid, width: 21"]
<tbody>[TR]
[TD]Equipment-2

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="class: grid, width: 21"]
<tbody>[TR]
[TD]K


Equipment-3

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]alpha[/TD]
[TD]163.8[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]41[/TD]
[TD]630[/TD]
[TD] [/TD]
[TD]RMG 250 L[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Beta[/TD]
[TD]125[/TD]
[TD]100[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]36[/TD]
[TD]500[/TD]
[TD] [/TD]
[TD]RMG 600L[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Gama[/TD]
[TD]250[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]41[/TD]
[TD]500[/TD]
[TD] [/TD]
[TD]RMG 600L[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD]265[/TD]
[TD]25[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]61[/TD]
[TD]530[/TD]
[TD] [/TD]
[TD]RMG 600L[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Theta[/TD]
[TD]265[/TD]
[TD]250[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]36[/TD]
[TD]530[/TD]
[TD] [/TD]
[TD]RMG 600L[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sita[/TD]
[TD]132.5[/TD]
[TD]50[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]42[/TD]
[TD]1060[/TD]
[TD] [/TD]
[TD]RMG 600L[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Zita[/TD]
[TD]506[/TD]
[TD]100[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]36[/TD]
[TD]1150[/TD]
[TD] [/TD]
[TD]RMG 600L[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Pie[/TD]
[TD]840[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]61[/TD]
[TD]525[/TD]
[TD]RMG 50L[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SI[/TD]
[TD]840[/TD]
[TD]20[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]36[/TD]
[TD]1050[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]RMG 1200L[/TD]
[/TR]
[TR]
[TD]Chi[/TD]
[TD]840[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]61[/TD]
[TD]525[/TD]
[TD]RMG 50L[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Ki[/TD]
[TD]456[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]36[/TD]
[TD]380[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]RMG 1200L[/TD]
[/TR]
[TR]
[TD]Li[/TD]
[TD]37.5[/TD]
[TD]200[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]43[/TD]
[TD] [/TD]
[TD]RMG 50L[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]MY[/TD]
[TD]18.75[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]39[/TD]
[TD]75[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NY[/TD]
[TD]18.75[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]39[/TD]
[TD]150[/TD]
[TD]RMG 50L[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.