Our company has 3 x sized boxes - small medium large.
We fit the following quantities of our product in each box:
Small - 95
Medium - 200
Large - 300
What I want to do is tell a user how many boxes each order will use. I’ve achieved this with nested IFs:
IF({quantity} <= {Small Box (from Link To Products)}, "1 x Small Box", IF({quantity} <= {Medium Box (from Link To Products)}, "1 x Medium Box", IF({quantity} <= {Large Box (from Link To Products)}, "1 x Large Box",ROUND({quantity}/{Large Box (from Link To Products)},2)&" x Large Boxes")))
This works well. My only issue is with the last statement when orders exceed 1 x Large Box. With an order larger than the size of 1 x Large Box, it returns a decimal of large boxes e.g. “2.67 x Large Boxes”. My question relates to the deicmal - I now want my formula to say whether that residual decimal would fir into a small box, medium box or round up to another full large box.
I’m used to nested IFs going in one direction, but now not sure how to handle the residual.
I hope I’ve explained myself OK.
Best answer by TheTimeSavingCo
How does this look?
IF( {quantity} <= {Small Box (from Link To Products)}, "1 x Small Box", IF( {quantity} <= {Medium Box (from Link To Products)} + {Small Box (from Link To Products)}, "1 x Medium Box + 1 x Small Box", IF( {quantity} <= {Large Box (from Link To Products)}, "1 x Large Box", IF( MOD({quantity}, {Large Box (from Link To Products)}) = 0, FLOOR({quantity} / {Large Box (from Link To Products)}) & " x Large Boxes", IF( MOD({quantity}, {Large Box (from Link To Products)}) <= {Small Box (from Link To Products)}, FLOOR({quantity} / {Large Box (from Link To Products)}) & " x Large Boxes + 1 x Small Box", IF( MOD({quantity}, {Large Box (from Link To Products)}) <= {Medium Box (from Link To Products)} + {Small Box (from Link To Products)}, FLOOR({quantity} / {Large Box (from Link To Products)}) & " x Large Boxes + 1 x Medium Box + 1 x Small Box", IF( MOD({quantity}, {Large Box (from Link To Products)}) <= {Medium Box (from Link To Products)}, FLOOR({quantity} / {Large Box (from Link To Products)}) & " x Large Boxes + 1 x Medium Box", (FLOOR({quantity} / {Large Box (from Link To Products)}) + 1) & " x Large Boxes" ) ) ) ) ) ) )
IF( {quantity} <= {Small Box (from Link To Products)}, "1 x Small Box", IF( {quantity} <= {Medium Box (from Link To Products)} + {Small Box (from Link To Products)}, "1 x Medium Box + 1 x Small Box", IF( {quantity} <= {Large Box (from Link To Products)}, "1 x Large Box", IF( MOD({quantity}, {Large Box (from Link To Products)}) = 0, FLOOR({quantity} / {Large Box (from Link To Products)}) & " x Large Boxes", IF( MOD({quantity}, {Large Box (from Link To Products)}) <= {Small Box (from Link To Products)}, FLOOR({quantity} / {Large Box (from Link To Products)}) & " x Large Boxes + 1 x Small Box", IF( MOD({quantity}, {Large Box (from Link To Products)}) <= {Medium Box (from Link To Products)} + {Small Box (from Link To Products)}, FLOOR({quantity} / {Large Box (from Link To Products)}) & " x Large Boxes + 1 x Medium Box + 1 x Small Box", IF( MOD({quantity}, {Large Box (from Link To Products)}) <= {Medium Box (from Link To Products)}, FLOOR({quantity} / {Large Box (from Link To Products)}) & " x Large Boxes + 1 x Medium Box", (FLOOR({quantity} / {Large Box (from Link To Products)}) + 1) & " x Large Boxes" ) ) ) ) ) ) )
Wow! Talk about going above and beyond. I was hoping to just handle those things beyond 1 large box, but you’ve given me futher options. Thanks very much.
