Jan 10 2020 06:02 PM
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
Jan 10 2020 11:26 PM - edited Jan 10 2020 11:29 PM
Jan 11 2020 01:42 AM
One more variant is =Qty/((Code="ABC")+1)/100 or with cell references
=B1/((A1="ABC")+1)/100
Jan 11 2020 05:58 AM
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 no | Product Code | Delivery Qty | Total Carton |
1 | LSSM-5891 | 600 | |
2 | LSSM-5892 | 200 | |
3 | LSSM-5801 | 1800 | |
4 | LSSM-5888 | 400 | |
Item 1,2 & 3 is 200 pcs of paperbags per packet | |||
Item 4 is 100 pcs of paperbags per packet |
Thank you.
Jan 11 2020 06:00 AM
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.
Jan 11 2020 06:13 AM
SolutionWith 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.
Jan 11 2020 06:36 AM
Jan 11 2020 06:13 AM
SolutionWith 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.