I am trying to create a formula to calculate unit dimensions from case dimensions

Question

This is kind of a specific question but I am attempting to calculate the dimensions of a unit within a case given the case dimensions. However, there are several configurations and counts for the units. The most common configurations and counts are (12 count 4X3X1), (24 count 6X4X1) and (24 count 4X3X2)(Units in LXWXH). I have calculated the cut-off case height to be 5.5 inches where anything above that must be the 2 high configuration of 4X3X2 assuming it is 24 count. Given only this information, is it possible to create a general formula or calculator?

Thanks.

+------+--------+--------+--------+--+---------+-------+
| Case | Height | Length | Width  |  | Volume  | Count |
+------+--------+--------+--------+--+---------+-------+
|      |      4 |      7 |   5.25 |  |     147 |    12 |
|      |   4.25 |  10.75 |   7.75 |  |     354 |    24 |
|      |   4.25 |  10.75 |   7.75 |  |     354 |    12 |
|      |    3.5 |    7.5 |    5.5 |  |     144 |    12 |
|      |   7.75 |     10 |      9 |  |     698 |    60 |
|      |      7 |   6.75 |   5.75 |  |     272 |    24 |
|      |   4.25 |  10.25 |   7.25 |  |     316 |    24 |
|      |      7 |  14.25 |    8.5 |  |     848 |    24 |
|      |      7 |  14.25 |      9 |  |     898 |    24 |
|      |   4.25 |  10.25 |   7.25 |  |     316 |    24 |
|      |   4.25 |   10.5 |   7.25 |  |     324 |    24 |
|      |   4.25 |  10.75 |   7.25 |  |     331 |    24 |
|      |   4.25 |  10.75 |   7.25 |  |     331 |    24 |
|      |    6.5 |     19 |   12.5 |  |    1544 |    48 |
|      |   5.51 |  10.51 |   7.95 |  |     460 |    12 |
|      |   6.85 |   6.69 |      5 |  |     229 |    24 |
|      |   7.28 |   7.48 |   5.59 |  |     304 |    24 |
|      |   8.78 |   9.06 |   6.89 |  |     548 |    24 |
|      |   4.25 |  10.75 |    7.5 |  |     343 |    24 |
|      |   4.25 |  10.75 |    7.5 |  |     343 |    24 |
|      |   4.25 |  10.75 |   7.75 |  |     354 |    24 |
|      |   4.25 |   11.5 |      8 |  |     391 |    24 |
+------+--------+--------+--------+--+---------+-------+

Answer 1

Had a lot of fun with this. Not sure if this is what you want 100% but will likely help. The code allows you to select a config, and it displays an output of what the stacks look like. All data stuff is done with formulas, which are in the photo. GL

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
        Set rng = ActiveSheet.Range("I3:I5")

    If Not Intersect(Target, rng) Is Nothing Then
        Dim rngClear As Range
            Set rngClear = ActiveSheet.Range("C14:AA100")

        rngClear.ClearFormats

        Dim str As String
            str = Target.Value

        str = Replace(str, Left(str, 5), "")

        Dim arr() As String
            arr = Split(str, "x")

        Dim count As Long
            count = ActiveSheet.Range("G" & Target.Row)

        count = count / arr(2)

        Dim perCase As Long
            perCase = ActiveSheet.Range("J" & Target.Row)

        Dim startCol As Long
            startCol = 3

        Dim startRow As Long
            startRow = 16

        Dim rngObj As Range
        Dim cell As Range
        Dim runLoop As Boolean
            runLoop = True

        Do While runLoop = True
            Dim strtRng As Range
                Set strtRng = ActiveSheet.Cells(startRow, startCol)
            Dim endRng As Range
                Set endRng = ActiveSheet.Cells(startRow + CInt(arr(1) - 1), startCol + CInt(arr(0)) - 1)

            Set rngObj = ActiveSheet.Range(strtRng.Address, endRng.Address)

            For Each cell In rngObj
                If count = 0 Then
                    Exit Sub
                End If
                If arr(2) = 1 Then
                    cell.Interior.color = vbRed
                Else
                    cell.Interior.color = vbBlue
                End If
                count = count - 1
            Next cell
         startCol = startCol + CInt(arr(0) + 1)
        Loop
    End If
End Sub