Skip to main content

Hi everybody,

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.

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

 

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.

Terms & ConditionsAccessibility statement