Very Tricky "Max Value" Question

Question

Hey there all....

I'm pretty familiar with excel equations, but have come up with one that I don't even know where to begin. This is a very simplified version of a rather massive spreadsheet containing over 6000 entries.

I want to type a formula in H3 that returns the Maximum value possible based on criteria I've written into F3 and G3. So in this example, if I type in "12" in F3, that means that the sum from the B column cannot exceed 12. The number of cards being 3 means that I want the maximum possible value from 3 cards where the "B" column does not exceed 12. In this case, the value is 160 and Peter is not part of that 160 because there is no combination of cards that wouldn't exceed 12 if he is included. Keep in mind that F3 and G3 are things that I would type in and are not static except in this example. So if I upped the amount of F3 to 16, Then H3 would show 170 because Peter could then be included. Ultimately, I would also like it to highlight the cards that make up that value as I have done for the example.

I hope I have described this well.

Answer 1

Here is a sample file:

https://www.dropbox.com/scl/fi/wym59mjeh1gms34x1g7lo/e0833f65-7e38-40e9-b570-8eece30a2990.xlsx?rlkey=hnfvk7jefjn9xzyt6p888htgz&st=z9i9r5po&dl=1

Open the Solver and click solve. Look at the status bar and you see how many steps are needed to calculate the solution with this few variables.

Andreas.