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, with dozens of experiment I have come up with this...
Now the only problem remains is the part marked in red

I want the formula to go for the lower size containers when ever there is a possibility

hope anyone can help..

VBA Working.xlsm
	A	B	C	D	E	F
1	Container Size	M3		M3	Required Containers	Correct Answer
2	[20]	25		25	1 [20]
3	[40-STD]	50		30	1 [40-STD]
4	[40-HC]	65		50	1 [40-STD]
5				75	1 [40-HC] + 1 [20]	1 [40-STD] + 1 [20] Reason: [40-STD] can lift 50 M3 & [20] can lift 25 M3 so why choose a higher container
6				90	1 [40-HC] + 1 [20]
7				95	1 [40-HC] + 1 [40-STD]
8				101	1 [40-HC] + 1 [40-STD]
9				205	3 [40-HC] + 1 [20]	2 40 [HC] +1 [40-STD] + 1 [20] Reason: 2 [40-STD] will lift 130 M3 + 1 [40-HC] will lift 50 M3 + 1 [20] will lift 25 M3
10				210	3 [40-HC] + 1 [20]
11				215	3 [40-HC] + 1 [20]
12				220	3 [40-HC] + 1 [20]
13				225	3 [40-HC] + 1 [40-STD]
Container Breakup Formula (2)

Cell Formulas
Range	Formula
E2:E13	E2	=IF(D2<=$B$2,"1 "&$A$2,IF(D2<=$B$3,"1 "&$A$3,IF(D2<=$B$4,"1 "&$A$4,IF(AND(D2>$B$4,MOD(D2,$B$4)=0),(D2-MOD(D2,$B$4))/$B$4&" "&$A$4,IF(AND(D2>$B$4,MOD(D2,$B$4)<=$B$2),(D2-MOD(D2,$B$4))/$B$4&" "&$A$4&" + 1 "&$A$2,IF(AND(D2>$B$4,MOD(D2,$B$4)<=$B$3),(D2-MOD(D2,$B$4))/$B$4&" "&$A$4&" + 1 "&$A$3,ROUNDUP(D2/$B$4,0)&" "&$A$4))))))

 

[JGordon11]

You could use Solver to minimize the excess capacity as below. In the example the total is 234 and solver arrived at a container inventory of 1 small, 3 medium, and 1 Large with a capacity of 240 (excess capacity = 240 -234 =6). Note that three large and a medium would have a capacity of 245 and an excess capacity of 11, so even though this configuration is fewer containers it isn't chosen because it has more excess capacity (11>6).

PQ 20210725.xlsx
	A	B	C	D	E
1	1	3	1		Excess Capacity
2	25	50	65		6
3
4				Target	234
Sheet3

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(A1:C1,A2:C2)-E4

 

[hrayani]

Hi JGordon,

Sorry for coming back late on this....
I am not familiar with solver & also this would not serve the purpose as every time one has to run it

A formula is what I need

I am just posting the sample data again which would be easier to understand as compared to my initial post

Formula needed for Columns (D:F) to efficiently figure out how many containers would be required keeping in view that it would cost less
I have also added an example case in the sample data

Hope anyone can help

VBA Working.xlsm
	A	B	C	D	E	F	G	H
1	SIZE	M3	Freight
2	[20]	25	$ 25
3	[40 STD]	55	$ 55
4	[40 HC]	65	$ 65
5
6			M3	[20]	[40 STD]	[40 HC]	Total Cost
7		Case 1	25	1	-	-	$ 25
8		Case 2	55	-	1	-	$ 55
9		Case 3	65	-	-	1	$ 65
10		Case 4	80	1	1	-	$ 80
11		Case 5	145	1	1	1	$ 145
12		Case 6	149	1	-	2	$ 155
13	Example	Case 7	200	1	2	1	$ 200	Efficient as compared to the next row because costing less
14	Example	Case 7	200	1	-	3	$ 220	Not efficeint as compared to the above row because costing more
15		Case 8	255	1	3	1	$ 255
Sheet5 (3)

Cell Formulas
Range	Formula
G7:G15	G7	=D7*$C$2+E7*$C$3+F7*$C$4

Regards,
Humayun

 

[JGordon11]

What is the largest M3 you would anticipate?

 

[hrayani]

What is the largest M3 you would anticipate?

Well last time I had it was around 7,000 M3
It would not cross 10,000 I reckon

 

[JGordon11]

Can be done with a VBA user defined function. It would be one heck of a complicated formula. The following code optimizes on cost then if there are more than one min cost option it selects the option with the min total number of containers.

If your 25, 55, 65 numbers change then change the Cost and Capacity array assignment lines in the code.

Function OptimumCapacity(x As Double)
    Dim Capacity, Cost, 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 Quant As Long, OrderQuant As Long
    Capacity = Array(25, 55, 65)
    Cost = Array(25, 55, 65)
    MinCost = 9E+99
    Quant = x
    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

OptimizeCapacity.xlsm
	A	B	C	D	E	F
1	M3	25	55	65	Capacity	Excess Capacity
2	5	1	0	0	25	20
3	10	1	0	0	25	15
4	15	1	0	0	25	10
5	20	1	0	0	25	5
6	25	1	0	0	25	0
7	30	2	0	0	50	20
8	35	2	0	0	50	15
9	40	2	0	0	50	10
10	45	2	0	0	50	5
11	50	2	0	0	50	0
12	55	0	1	0	55	0
13	60	0	0	1	65	5
14	65	0	0	1	65	0
15	70	3	0	0	75	5
16	75	3	0	0	75	0
17	80	1	1	0	80	0
18	85	1	0	1	90	5
19	90	1	0	1	90	0
20	95	4	0	0	100	5
21	100	4	0	0	100	0
22	105	2	1	0	105	0
23	110	0	2	0	110	0
24	115	2	0	1	115	0
25	120	0	1	1	120	0
26	125	5	0	0	125	0
27	130	0	0	2	130	0
28	2000	0	8	24	2000	0
29	3000	1	8	39	3000	0
30	7000	0	2	106	7000	0
31	10000	0	1	153	10000	0
Sheet4

Cell Formulas
Range	Formula
B2:D31	B2	=OptimumCapacity(A2)
E2:E31	E2	=SUMPRODUCT(B2:D2,$B$1:$D$1)
F2:F31	F2	=E2-A2
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[hrayani]

Hi JGordon,

Thanks for the UDF....
I tried but it is not giving me the correct results

Please let me know if I am doing anything wrong here

VBA Working.xlsm
	A	B	C	D	E	F
1	M3	25	55	65	Capacity	Excess Capacity
2	5	1	1	1	145	140
3	10	1	1	1	145	135
4	15	1	1	1	145	130
5	20	1	1	1	145	125
6	25	1	1	1	145	120
7	30	2	2	2	290	260
8	35	2	2	2	290	255
9	40	2	2	2	290	250
10	45	2	2	2	290	245
11	50	2	2	2	290	240
12	55	0	0	0	0	-55
13	60	0	0	0	0	-60
14	65	0	0	0	0	-65
15	70	3	3	3	435	365
16	75	3	3	3	435	360
17	80	1	1	1	145	65
18	85	1	1	1	145	60
19	90	1	1	1	145	55
20	95	4	4	4	580	485
21	100	4	4	4	580	480
22	105	2	2	2	290	185
23	110	0	0	0	0	-110
24	115	2	2	2	290	175
25	120	0	0	0	0	-120
26	125	5	5	5	725	600
27	130	0	0	0	0	-130
28	2000	0	0	0	0	-2000
29	3000	1	1	1	145	-2855
30	7000	0	0	0	0	-7000
31	10000	0	0	0	0	-10000
Sheet3

Cell Formulas
Range	Formula
B2:B31	B2	=OptimumCapacity(A2)
C2:C31	C2	=OptimumCapacity(A2)
D2:D31	D2	=OptimumCapacity(A2)
E2:E31	E2	=SUMPRODUCT(B2:D2,$B$1:$D$1)
F2:F31	F2	=E2-A2
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[JGordon11]

Needs to be entered into a one row by three column selection. So select B2:D2 then enter the formula =OptimumCapacity(A2) then enter by ctrl-shift-enter. Then drag the the three cell selection down.

 

[hrayani]

Needs to be entered into a one row by three column selection. So select B2:D2 then enter the formula =OptimumCapacity(A2) then enter by ctrl-shift-enter. Then drag the the three cell selection down.

Thanks... Got it worked...
I am still wondering how did you do that... I mean writing the UDF... hats off to you
I use VBA but at a very basic level....
Thanks Again

Few Questions:
1) Can the UDF be amended to look at the cell values for Cost & M3
2) What if I want to run this on my original sheet (this was a dummy sheet as you know) Like starting from row # 5 Column # 2.. Can you please guide me which part of the code needs to be changed..

Regards,
Humayun