variable slot bookings how can this be achieved to show the cheapest cost?

[deanmoakes]

Hi All,

I am struggling with the best way to create the below
I have a column with hours of slots and the corresponding cost (all been randomly filled).
The box at the top has a time that can change (07:43:55).
I need the total at the bottom to be the most efficient in cost but still meeting the required time needed.

In this example it would be cheaper to book an 8 hr slot vs a number of smaller slots.

Any help would be appreciated

Eg

		Target time	07:43:55​
Item	Cost	Time	Time balance	Slots booked	Time booked	Costs
120 hr	11200​	120:00:00​	7:43:55​	0​	0:00:00​	0​
24 hr	2600​	24:00:00​	7:43:55​	0​	0:00:00​	0​
8hr	950​	8:00:00​	7:43:55​	0​	0:00:00​	0​
3hr	400​	3:00:00​	7:43:55​	2​	6:00:00​	800​
1hr	150​	1:00:00​	1:43:55​	1​	1:00:00​	150​
15 min	40​	0:15:00​	0:43:55​	2​	0:30:00​	80​
1 min	3​	0:01:00​	0:13:55​	14​	0:14:00​	42​
					7:44:00​	1072​

 

[Eric W]

This looks like a Solver task. Your workbook is already pretty close to how you need to set it up. I added the formulas to it:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G
1		Target time	7:43:55
2
3	Item	Cost	Time	Time balance	Slots booked	Time booked	Costs
4	120 hr	11200	120:00:00	7:43:55	0	0:00:00	0
5	24 hr	2600	24:00:00	7:43:55	0	0:00:00	0
6	8hr	950	8:00:00	over	1	8:00:00	950
7	3hr	400	3:00:00	over	2	6:00:00	800
8	1hr	150	1:00:00	over	0	0:00:00	0
9	15 min	40	0:00:15	over	0	0:00:00	0
10	1 min	3	0:00:01	over	0	0:00:00	0
11
12						14:00:00	1750
Sheet17

Cell Formulas
Range	Formula
F4:F10	F4	=C4*E4
G4:G10	G4	=B4*E4
D4:D10	D4	=IFERROR(EXP(LN(C$1-SUMPRODUCT(C$4:C4,E$4:E4))),"over")
F12	F12	=SUMPRODUCT(E4:E10,C4:C10)
G12	G12	=SUMPRODUCT(E4:E10,B4:B10)

If you haven't yet, you can install the Solver by clicking on File > Options > Add-ins > Go (next to the Excel Add-ins box on the bottom) > and check the Solver Add-in box. Then go to the Data tab, click Solver (on the far right), and set up the parameters like this:

Then click Solve. The Solver is not guaranteed to find the best answer, although it should get close. The only way to make sure you get the best answer is with a macro that checks all possibilities.