Best Combination for the Price

[dclasen]

I'm trying to determine a way (maybe without VBA) to find the best combination of product for the price.
It seems like this Knapsack formula I did some research on, but I was hoping for something in the existing formulas/solvers available standard in Excel. Any help is appreciated.

 

[dclasen]

Is there a way to evaluate a first part and then solve?

As an option, after satisfying the amount of UI, the remaining (extra) UI can be used as DI, lowering the requirement.
If I select a Req'd (using SHG's idea) of 5UI, 16DI, 0P, 0AO, 4DO-R, 4DOTriac, the result is going to be Qty1 Type A and Qty 2 Type B.
Using remaining UI as DI means that I'd select Qty 1 each Type A and B.

It would be something like solving the UI first, then taking the extra and subtracting it from the DI requirement and then solving the rest.

 

[dclasen]

I think maybe I understand this now - Looks like I can make a bunch of buttons that each are a macro to a different solver-set kept somewhere else.
As I push each button then its working its own Req'd amount list based on the combinations and I solve the first resolution, calculate and then the second.
The restriction there is always need to run the whole thing in order.
Thanks for the help.

 

[shg]

if UIs can serve as DIs, then the number of DIs available could be calculated as DIsAvail + UIsAvail - UIsUsed.