Optimizing combinations to minimize cost - cabin rental for school trip task

[Sjakktrekk]

I went to a computer class for a year when I was studying to become a teacher. During this year, we all got a task/problem to solve, which I never quite got out of my head. The task was part of an exam, and I solved it, but I suspect that my answer to the solution wasn't very elegant or efficient, and it did not always work.

Here's the task:

you are going on a trip with your pupils. During the trip you will rent some cabins to live in. There will be three sorts of cabins to choose from: Small size, middle size and big size. The smaller cabins are cheaper, of course, but the capacity is lower, so it will probably be cheaper per person for the big cabins. There are a finite number of the different cabins, so if you have all the biggest cabins filled up, you'll have to put the rest of the pupils into the smaller cabins. The girls can't be in the same cabin as the boys, so there'll be pure girl-cabins, boy-cabins and teacher/parent-cabins.

The challenge is: What is the cheapest and most optimal combination of cabins for this trip?

Example:

Small cabin
Price (per day): $60
Capacity: 4
Number of cabins: 6

Middle-sized cabin
Price (per day): $80
Capacity: 6
Number of cabins: 6

Big cabin
Price (per day): $100
Capacity: 8
Number of cabins: 8

Girls (count): 24
Boys (count): 25
Teachers/parents: 5


Obviously there's going to be room to spare in some of the cabins, so this should be in a cell somewhere in the result-table. Also all of the numbers should be changeable (count of boys, girls, price of cabins, capacity etc.).

When I solved this, I made huge tables which sorted out the cheapest combinations. I didn't know a lot of the functions, so I primarily used MAX and MIN and IF - functions. But it worked only to a certain point - if the number of pupils got too big, it collapsed.


So if anyone should be interested in trying to solve this excel challenge and uploading it to somewhere others could download it, I would be very interested in seeing what you came up with!

Greetings from Norway

Soundtrack to this challenge: The cabin - by Ylvis