Forum Discussion

Rajesh_Sinha's avatar
Rajesh_Sinha
Iron Contributor
Nov 19, 2020

Excel12345678 

 

Check the attached Workbook,, on first sheet I've modified the excising formula along with condition/values to be tested,, also read my opinion.

 

In case you need any further assistance just post your reply.

Excel12345678's avatar
Excel12345678
Copper Contributor
Nov 22, 2020

Rajesh_Sinha 

Thanks....but I have Query

1. how formula find division of Delhi-VCD or Delhi-PHD from master sheet..?

2. formula should based on master sheet Criteria …if P then two condition above 100% - (Additional 120% in fixed amount) and above 90% (as it is fixed amount)

3. but Division is V then one condition only above 100%

 

  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Iron Contributor
    Nov 23, 2020

    Excel12345678 

     

    You may use this in cell H2 in Master Sheet:

     

    =IF(Master!B2="P",Calculation!$N$4,IF(Master!B2="V",Calculation!$N$8,IF(Master!B2="V1",Calculation!$N$10,"NA")))

Or use this:

=IF(B2="P",(IF(OR(Calculation!F3>=100,Calculation!H3>=100,Calculation!F4>=100,Calculation!H4>=100),((Calculation!B3*Calculation!K3%)+(Calculation!J3*Calculation!I3)))),IF(B2="V",IF(OR(Calculation!F3>=100,Calculation!H3>=100,Calculation!F4>=100,Calculation!H4>=100),(((Calculation!B3*Calculation!K3%)+(Calculation!J3*120%)*Calculation!I3))),IF(K16="V1",IF(OR(Calculation!F3<=90,Calculation!H3<=90,Calculation!F4<=90,Calculation!H4<=90),((Calculation!B3*Calculation!K3%)+((Calculation!J3*Calculation!I3)))),"NA")))

     

    Note: 

    • According to Division you may add more checks and modify the formula. 
    • Adjust cell references in the formula as needed.

Resources