Hi,

I have a list of items in Excel, each of which has two values attached, credit and hours.

There's a requirement that a choice of items needs to be made such that their total credit is above a certain value. I then need to find out which valid combination of units has the lowest number of hours. Example:

Minimum credit from group = 10

Item 1 - 5 credits, 5 hours


Item 2 - 5 credits, 5 hours
Item 3 - 5 credits, 7 hours
Item 4 - 10 credits, 12 hours

I'd like this to return 'Minimum hours for group = 10' and ideally let me know that this is the combination of Items 1 & 2.

If Item 4 had been, say, 8 hours, then that on its own would have been the smallest 'combination' meeting the rules. I'm not convinced Excel even knows how to do this, does anyone have any thoughts? I can find the minimum hours value per units but it's the caveat of a minimum total credit that's giving me trouble.

Thanks,

Ben

Hi Ben
Can you post a copy of your spreadsheet so we can see exactly what you have done so far.
Tony(OTPM)

Attached, with a couple of notes and two basic examples of what I'm trying to do. GLH stands for Guided Learning Hours, not that that's relevant.

After a bit of research I think Optimization is what I'm trying to do. I've installed the Solver but I'm stumped by it!

Ben

Attached Files

example sheet.xlsx (10.8 KB, 10 views)