VBA Optimization using matrixes

Hello,

I'd like to know the proper method to do the following:

I've a table consisting of different products (raw materials) with different properties. Refer to the first input table in the attachment for a small example. The different products are in the columns, the properties are the rows.

I want to calculate a mix from these different products to a desired composition. See Input table 2 in the attachment.

All these raw materials have different prices, there are multiple solutions available. I want to have the cheapest combination of raw materials. Refer to the output table.

What my idea is that I have two matrixes: the raw materials matrix and the desired product matrix. I'd like to know the best way by using these matrixes (in vba) to optimize the composition with the lowest price and eventually have an optimization to maximize consumption of a raw material, while still having a low price combination.

Kind regards,
whitetigerck

Hi,

Thanks for responding.

I want to minimize the price by optimizing the product mix. Depending on the composition of the raw material (which, for this case, should not be 100% total), the value of the raw material is different.

Different raw materials have different component values and different prices, but are for example limited in the maximum amount of components you can use in the end product.

The real table is larger, multiple solutions are possible. I want to have 2 solutions:
- the cheapest solution
- solution wich maximum consumption of product B, while still having a low price

Depending on the end product, not each desired product composition is relevant. Most of the time only 4 different components are relevant.