UOM dividing IF-THEN / other solution

%3CLINGO-SUB%20id%3D%22lingo-sub-3543398%22%20slang%3D%22en-US%22%3EUOM%20dividing%20IF-THEN%20%2F%20other%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3543398%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20been%20designing%20simple%20delivery%20note%20generator%2C%20i've%20got%20products%20that%20are%20picked%20up%20from%20dropdown%20box%20as%20shown%20below%2C%20operator%20will%20only%20type%20first%20qty%20in%20trays%20and%20on%20side%20converter%20will%20show%20pallets%20qty%20which%20was%20simply%20dividing%20cell%20value%20by%2060%20(as%20that's%20how%20many%20trays%20majority%20of%20product%20will%20fit%20on%20pallet).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20to%20my%20question%20-%20but%20what%20about%20if%20i.e.%20you%20select%20Caramel%204pk%20and%20this%20means%20that%2090%20will%20fit%20per%20pallet.%20Or%20other%20potential%20variants.%20What%20formula%20to%20put%20that%20upon%20selection%20from%20dropdown%20box%20it%20knows%20it's%20caramel%20and%20divides%20by%2090%2C%20and%20for%20the%20rest%20by%2060.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JZJANIK_1-1655990110091.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22JZJANIK_1-1655990110091.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22JZJANIK_1-1655990110091.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22JZJANIK_1-1655990110091.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22JZJANIK_1-1655990110091.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F383019i9ACCFDEB29B30825%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JZJANIK_1-1655990110091.png%22%20alt%3D%22JZJANIK_1-1655990110091.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Bi'll%20allow%20myself%20to%20tag%20you%20sir%20since%20you've%20been%20super-helpful%20last%20time%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20and%20have%20a%20nice%20day%20all%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3543398%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3547676%22%20slang%3D%22en-US%22%3ERe%3A%20UOM%20dividing%20IF-THEN%20%2F%20other%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3547676%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1400724%22%20target%3D%22_blank%22%3E%40JZJANIK%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20caramel%20is%20the%20only%20exception%2C%20you%20could%20use%20the%20following%20formula%20in%20E13%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(OR(B13%3D%22%22%2CC13%3D%22%22)%2C%22%22%2CC13%2FIF(B13%3D%22CARAMEL%204PK%22%2C90%2C60))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20can%20be%20filled%20down.%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20more%20exceptions%2C%20it%20would%20be%20better%20to%20create%20a%20lookup%20list%20with%20product%20in%20the%20first%20column%20and%20trays%20per%20pallet%20in%20the%20second%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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. 

 

JZJANIK_1-1655990110091.png

@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 

1 Reply

@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.