UOM dividing IF-THEN / other solution

[JZJANIK]

Hi all

I've been designing simple delivery note generator, i've got products that are picked up from dropdown box as shown below, operator will only type first qty in trays and on side converter will show pallets qty which was simply dividing cell value by 60 (as that's how many trays majority of product will fit on pallet). 

Now to my question - but what about if i.e. you select Caramel 4pk and this means that 90 will fit per pallet. Or other potential variants. What formula to put that upon selection from dropdown box it knows it's caramel and divides by 90, and for the rest by 60. 

@Hans Vogelaar i'll allow myself to tag you sir since you've been super-helpful last time :) 

Thanks and have a nice day all 

[#2 · Accepted solution]

@JZJANIK 

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.

[#3]

hi,

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

[#4]

@JZJANIK 

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))

[#5]

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.

Thanks

[#6]

@JZJANIK 

You 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))