Excel Formula

[hrayani]

Hello Friends,

Please have a look at the below table...
Formula required for Column E & F

Any help would be appreciated

Book2
	A	B	C	D	E	F
1	Container Size	M3		M3	Required Containers	Total Containers
2	[20]	30		30	1 [20]	1
3	[40-STD]	50		50	1 [40-STD]	1
4	[40-HC]	65		65	1 [40-HC]	1
5				95	1 [40-HC] + 1 [20]	2
6				96	1 [40-HC] + 1 [40-STD]	2
7				115	1 [40-HC] + 1 [40-STD]	2
8				129	1 [40-HC] + 1 [40-STD]	2
9				130	2 [40-HC]	2
10				135	2 [40-HC] + 1 [20]	3
11				160	2 [40-HC] + 1 [20]	3
12				161	2 [40-HC] + 1 [40-STD]	3
13
14	20 feet container can take a load of max 30 M3
15	40-STD feet container can take a load of max 50 M3
16	40-HC feet container can take a load of max 65 M3
Sheet1

Regards,
Humayun

 

[hrayani]

Hi JGordon,

Just tried & found that it worked on any range... There is no stopping to that... Sorry I should have tested this before

Only thing remains is I want the code to look at the cell values for cost & capacity ... Just in case if there is a need to change the capacity or the cost then one would be able to change it on the sheet rather than opening the VBA editor...

I also noticed that if the cost is same for 40 STD & 40 HC then the UDF gives preference to 40 HC - rightly so

 

[JGordon11]

This version takes three parameters: M3 as either a range reference or a number, Capacity of the three containers as a three cell range, and Cost to use each container as a three cell range. The Capacity and Cost ranges should be absolute references (example $B$1:$D$1) or a named range for example you could name one range Capacity and the Cost in the Formulas tab Name Manager. That is what I have done in the example below. As you've discovered you can put the formula in any 1 row by three column range and if provide the correct parameters you should get the correct result. Note that the capacity range needs to be in increasing order and the cost range needs to correspond to the capacity range. Also if the per unit cost of the large container is more than the per unit cost of a smaller container it won't provide optimal results because for run time efficiency the code assumes using the large container a lot for large orders and then optimizes the remaining 10 or so containers.

Function OptimumCapacity(Quant As Double, myCapacity As Range, myCost As Range)
    If myCapacity.Cells.Count <> 3 Or myCost.Cells.Count <> 3 Then
        OptimumCapacity = "Invalid range"
        Exit Function
    End If
    Dim c, Containers(1 To 1, 1 To 3), MinCost As Double, OrderCost As Double, Num As Long, i As Long, j As Long, k As Long
    Dim Capacity(0 To 2), Cost(0 To 2), OrderQuant As Long
    MinCost = 9E+99
    i = 0
    For Each c In myCapacity
        Capacity(i) = c
        i = i + 1
    Next
    i = 0
    For Each c In myCost
        Cost(i) = c
        i = i + 1
    Next
    Do While Quant - Capacity(2) > Capacity(2) * 10
        Quant = Quant - Capacity(2)
        Num = Num + 1
    Loop
    For i = 0 To 10
        For j = 0 To 10
            For k = 0 To 10
                OrderCost = i * Cost(0) + j * Cost(1) + k * Cost(2)
                OrderQuant = i * Capacity(0) + j * Capacity(1) + k * Capacity(2)
                If OrderQuant >= Quant And OrderCost < MinCost Then
                    Containers(1, 1) = i
                    Containers(1, 2) = j
                    Containers(1, 3) = k + Num
                    MinCost = OrderCost
                End If
            Next
        Next
    Next
    OptimumCapacity = Containers
End Function

RandomQuestions.xlsm
	A	B	C	D	E	F
1	Capacity	25	55	65
2	Cost	25	55	65
3
4
5	M3	25	55	65	Capacity	Excess Capacity
6	20	1	0	0	25	5
7	25	1	0	0	25	0
8	30	2	0	0	50	20
9	40	2	0	0	50	10
10	50	2	0	0	50	0
11	60	0	0	1	65	5
12	70	3	0	0	75	5
13	80	1	1	0	80	0
14	90	1	0	1	90	0
15	100	4	0	0	100	0
16	110	0	2	0	110	0
17	120	0	1	1	120	0
18	130	0	0	2	130	0
19	140	3	0	1	140	0
20	150	6	0	0	150	0
21	160	2	2	0	160	0
22	170	2	1	1	170	0
23	180	2	0	2	180	0
24	190	1	3	0	190	0
25	200	1	2	1	200	0
26	210	1	1	2	210	0
27	220	0	4	0	220	0
28	230	0	3	1	230	0
29	2000	0	8	24	2000	0
30	3000	1	8	39	3000	0
31	7000	0	2	106	7000	0
32	10000	0	1	153	10000	0
Sheet4

Cell Formulas
Range	Formula
B6:D32	B6	=OptimumCapacity(A6,Capacity,Cost)
E6:E32	E6	=SUMPRODUCT(B6:D6,$B$5:$D$5)
F6:F32	F6	=E6-A6
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Capacity	=Sheet4!$B$1:$D$1	B6:D32
Cost	=Sheet4!$B$2:$D$2	B6:D32

 

[hrayani]

Works like a charm

Many Thanks

Regards,
Humayun