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: