SOLVED

IF Formula

Copper Contributor

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 quantity for that product to divide by 200. 
How do i do that formula using IF? 

thank you

 

7 Replies
Let's say you have Product Code ABC in cell A1 and 500 in cell B1 as quantity.

The IF Formula will be thus :backhand_index_pointing_down:
=IF(A1="ABC",B1/100,B1/200)

@Madi_LF1968 

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

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

 

@Abiola1 

 

Thank you for your reply. I tried, but it didn't work. Perhaps i did it wrongly.

 

Pls find below table. Could you assist to put in the formula for me in column "Total carton".

 

Item noProduct CodeDelivery QtyTotal Carton
1LSSM-5891600 
2LSSM-5892200 
3LSSM-58011800 
4LSSM-5888400 
    
Item 1,2 & 3 is 200 pcs of paperbags per packet
Item 4 is 100 pcs of paperbags per packet

 

Thank you.

 

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. 

 

best response confirmed by Madi_LF1968 (Copper Contributor)
Solution

@Madi_LF1968 

With this sample

image.png

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.

@Sergei Baklan 

 

Thank you very much Sergei. Your formula worked. 

 

Thanks again. 

@Madi_LF1968 , you are welcome

1 best response

Accepted Solutions
best response confirmed by Madi_LF1968 (Copper Contributor)
Solution

@Madi_LF1968 

With this sample

image.png

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.

View solution in original post