Forum Discussion
UOM dividing IF-THEN / other solution
If caramel is the only exception, you could use the following formula in E13:
=IF(OR(B13="",C13=""),"",C13/IF(B13="CARAMEL 4PK",90,60))
This can be filled down.
If you have more exceptions, it would be better to create a lookup list with product in the first column and trays per pallet in the second column.
thanks, work like a charm as always 🙂 but you're right - to be more proof for any changes in the future - creating a lookup list would be probably better longer term solution.
what would be the formula in E13 then?
kind regards
Let's say you create a list like this on another sheet Sheet2:
It contains only three products here, but it can be longer of course. The formula in E13 on the data sheet could then be
=IF(OR(B13="",C13=""),"",C13/XLOOKUP(A13,Sheet2!A:A,Sheet2!B:B,60))
if you have Microsoft 365 or Office 2021:
If you have an older version:
=IF(OR(B13="",C13=""),"",C13/IFERROR(VLOOKUP(A13,Sheet2!A:B,2,FALSE),60))
- Hi,
I get it, implemented it and it works 🙂 thanks a lot! Excel seems to be a lot of fun if you know what you're doing... hope I'll get there one day eventually ha!
One more (final I hope 🙂 ) possibility - what if one of the products listed on lookup list would be exception of other type (subject of not converting to pallets) so it returns blank in E column in order not to break summing up whole column below.
ThanksYou could check if column F contains something other than "PAL", e.g. "OTHER" or a blank.
The formula could then be
=IF(OR(B13="",C13="",F13<>"PAL"),"",C13/IFERROR(VLOOKUP(A13,Sheet2!A:B,2,FALSE),60))