Forum Discussion

Madi_LF1968's avatar
Madi_LF1968
Copper Contributor
Jan 11, 2020
Solved

IF Formula

I'm trying to use IF formula on this scenario If an item is product code : ABC, then use the quantity for product ABC to divide by 100 and if the product is not product code : ABC, then use the quan...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jan 11, 2020

    Madi_LF1968 

    With this sample

    first formula could be

    =D3/((C3<>"LSSM-5888")+1)/100

     

    But it's much better to have helper table (on the right) with paperbags per item against code. When the formula (second table below) will be

    =D10/VLOOKUP(C10,H3:$I$6,2,FALSE)

    Please check in attached file.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 11, 2020

Madi_LF1968 

One more variant is =Qty/((Code="ABC")+1)/100 or with cell references

=B1/((A1="ABC")+1)/100

 

Madi_LF1968's avatar
Madi_LF1968
Copper Contributor
Jan 11, 2020

Thanks Sergei. 

 

I am not that familiar with IF Formula. 

 

Can you put it in full formula as i tried the formula you gave but it didn't work out. 

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 11, 2020

    Madi_LF1968 

    With this sample

    first formula could be

    =D3/((C3<>"LSSM-5888")+1)/100

     

    But it's much better to have helper table (on the right) with paperbags per item against code. When the formula (second table below) will be

    =D10/VLOOKUP(C10,H3:$I$6,2,FALSE)

    Please check in attached file.

    • Madi_LF1968's avatar
      Madi_LF1968
      Copper Contributor
      Jan 11, 2020

      SergeiBaklan 

       

      Thank you very much Sergei. Your formula worked. 

       

      Thanks again. 

Resources