Calculating the best combination of coaches for passengers?

Question

I have a table in Microsoft Excel that I'd like to use to calculate the best combination of coaches to house the supplied number of passengers. Here is a simplified version of the table:

I need to enter three formulas in the coach count column that calculates the best value-for-money combination of coaches that can carry all the passengers. For example, if there was 40 passengers, the result should be one 49-seat coach as opposed two 20-seat coaches as it's the cheapest combination. I have no idea how I would work on implementing these formulas and would appreciate some pointers. So far, all I have in C4 is =IF(MOD(B1, A4) = 0, B1 / A4, 0) which only works with multiples of 20 and does not account for combinations of coaches or cost efficiency.

Perhaps this is too complex of a task to implement in formulae? Would I be better off using a VB macro, or simply leaving it to the user to calculate the best combination?

Answer 1

There are two ways to address this problem. I will outline both solutions:

Option 1: In Worksheet Formulas

I'd have to spend more time on this in order to find a really elegant solution for this route, but here's a functional approach that should work well enough. Here are some quick highlights:

• Firstly, you need to add a column to your table that outlines the minimum number of seats a coach carries. This helps to facilitate the vlookup.
• Secondly, make sure that your lookup table is sorted in ascending order according to the minimum # of seats.
• I have made the assumption that the most effective pricing model is to get the majority of people onto the largest coach (or many of the largest coach), and then to use the smallest coach that would accommodate the remaining people. If this is not a fair assumption, then this solution may not be appropriate.

Here are screenshots of the final outcome:

And the formulas required to make it: (and a link in case you need to blow it up: https://i.sstatic.net/hKjQK.jpg)

Note: You'll notice that the previous answer is incorrect, as it suggested that 74 people would need to spend $180 instead of $140.

Option 2: Using Excel's Solver Add-In

1. Enable the solver add-in (File --> Options --> Add-ins --> Excel Add-ins (Manage) --> Solver Add-In)
2. Configure worksheet as shown:

UI:

The formulas:

3. On the Ribbon, go to the Data Tab, Analysis Group, & Click Solver.
4. Configure Solver as follows:
5. Click "Solve" and then click "Ok"
6. Final Outcome:

Answer 2

This seems to be a classic linear programming problem. You need to minimize total cost = (number of coach 1 times 50) + (number of coach 2 times 60) + (number of coach 3 times 80), subject to the constraint that (number of coach 1 times 20) + (number of coach 2 times 29) + (number of coach 3 times 49) is greater than or equal to (number of attendees), and all numbers of coaches are greater than or equal to zero. I think Excel's Solver is the tool for such a problem. You don't need to implement any of the solution yourself, you just set it up and Solver handles the algorithmic stuff.

Answer 3

Try this:

Sample calculation

With Formulas showing

The idea is to check for the largest coach first, using the integer value of division Count/Seats. The do the same for the 2nd largest coach with the remaining people. Etc etc.