Combinations

[Ben Allen]

Hi Guys,
I need a formula or macro, macro most likely as its quite complicated. That will find the combinations of transport needed to run a tour. I have a column containing the number of adults for each tour and the same for children.
The following table defines the cost of transport and capacity:

Transport Type Seating Capacity Cost
Minibus 20 £105
Tourbus 110 £220
Coach 56 £180


The macro must find the cheapest combination of transport that is suitable for that number of people (adults + children).
Thanks for all the help so far,
--
Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"

 

[Rob van Gelder]

Solver Add-In can do these sort of formulas as the cost minimising becomes
more complex, but in this case I think a simple set of formulas will do.

Assuming your grid spans A2 to C4

E2: Number of passengers
D2: =ROUNDUP((E2-(D3*B3+D4*B4))/B2,0)
D3: =INT(E2/B3)
D4: =INT((E2-D3*B3)/B4)


--
Rob van Gelder - http://www.vangelder.co.nz/excel


Hi Guys,
I need a formula or macro, macro most likely as its quite complicated. That
will find the combinations of transport needed to run a tour. I have a
column containing the number of adults for each tour and the same for
children.
The following table defines the cost of transport and capacity:

Transport Type Seating Capacity Cost
Minibus 20 £105
Tourbus 110 £220
Coach 56 £180


The macro must find the cheapest combination of transport that is suitable
for that number of people (adults + children).
Thanks for all the help so far,
--
Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"

 

[Frank Kabel]

Hi
if these are your prices and I did calculate it correctly it is always
best to use the vehicle with the larger capacity.
If A1 stores the number of passengers use the following formulas to get
the number of vehicles:
1. Number of Tourbusses:
=INT(A1/110) + (MOD(A1,110)>56)

2. Number of coaches
=(MOD(A1,110)<=56)*(MOD(A1,110)>20)

3. Number of Minibus
=--(MOD(A1,110)<=20)

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag Hi Guys,
I need a formula or macro, macro most likely as its quite complicated.
That will find the combinations of transport needed to run a tour. I
have a column containing the number of adults for each tour and the
same for children.
The following table defines the cost of transport and capacity:

Transport Type Seating Capacity Cost
Minibus 20 £105
Tourbus 110 £220
Coach 56 £180


The macro must find the cheapest combination of transport that is
suitable for that number of people (adults + children).
Thanks for all the help so far,
--
Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"

 

[Frank Kabel]

Hi
change the last formula to
=(MOD(A1,110)<=20)*(MOD(A1,110)>0)

 

[Ben Allen]

Thanks Frank, problem is these are subject to change so it might not always
be cheaper.
--
Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"

 

[Frank Kabel]

Hi
in this case you may have a look at 'Tools - Solver'