Formula that finds the proper combination based on a certain quantity

Hey all:

I am not sure if this will be a formula or maybe it is VBA which is something I'm not too proficient in, so please let me know.

I have two different types of trucks.

Truck Type A rents for $325 and holds 75 panels
Truck Type B rents for $450 and holds 150 panels

I want a formula that will choose the cheapest combination of trucks up to 10,000 panels

So if we have 75 panels it will choose Truck A
If we have 76 panels it will choose one Truck B
If we have 150 panels it will choose one Truck B
If we have 151 panels it will choose one Truck A and one Truck B
and so forth up until we reach 10,000 panels

I could create a whole table I suppose and reference that with an if or lookup option but I am wondering if there is a cleaner/easier/less time consuming way to do this

Thanks for any and all suggestions

So you are saying that if the quantity can be divided by 150 then it will choose truck B.

Can you help me with this? I've never used the INT formula before.

Originally Posted by MrShorty

How thoroughly have you analyzed the problem? It looks like you have convinced yourself that you will preferentially use type B trucks, with the occasional type A truck thrown in. Based on what I can see:

1) The number of type B trucks will be INT(panels/150).
2) If the number of remaining panels is 75 or less, then add a type A truck, else add another type B truck.

Does that seem like a correct algorithm? As those should be relatively simple Excel formulas, can you piece them together, or do you need help putting the formulas together?

So you are saying that if the quantity can be divided by 150 then it will choose truck B.

Can you help me with this? I've never used the INT formula before.

So this is what I have so far, but there is an error that happens when I get to 226. It is choosing 2 small trucks and 1 big instead of 2 big. Thoughts?

# of Panels: B2
Big Truck: =IF(AND(B2>75,B2<150),1,QUOTIENT(B2,150))
Small Truck: =IF(B4=-1,0,ROUNDUP((B2-150*B3)/75,0))

Hi,

Well, let's just get it done for OP...

Columns A and B are not really necessary, just for informational purposes Only.
Follow the logic of this sample and apply it to your set up:

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G
1	Truck Type	Cost	Capacity		Panels	Truck B Needed	Truck A Needed
2	A	325	75		75	0	1
3	B	450	150		76	1	0
4					150	1	0
5					151	1	1
6					900	6	0
7					950	6	1
8					1000	7	0
9					1100	7	1

Sheet: Sheet69

Excel 2016 (Windows) 64 bit

	F	G
2	=INT(E2/C$3)+IF(MOD(E2,C$3)>C$2,1,0)	=IF(F2*C$3>=E2,0,1)

Sheet: Sheet69

Don't want to confuse OP, so I'll post this separately, basically the same formulas, just written "short-hand":

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G
1	Truck Type	Cost	Capacity		Panels	Truck B Needed	Truck A Needed
2	A	325	75		75	0	1
3	B	450	150		76	1	0
4					150	1	0
5					151	1	1
6					900	6	0
7					950	6	1
8					1000	7	0
9					1100	7	1

Sheet: Sheet69

Excel 2016 (Windows) 64 bit

	F	G
2	=INT(E2/C$3)+N(MOD(E2,C$3)>C$2)	=N(F2*C$3<E2)

Sheet: Sheet69

Legit no clue how you know how to do that but it is awesome and it worked and I am extremely appreciative!!!!!