Minimizing the amount of ressource needed

[MaxMax3]

Hello,

I want to make a workbook to help reducing the amount of primary ressource needed, and I am a bit lost on where to start...

Let's say I have three types of Ores, A, B, and C, than can each be reprocessed into different materials k, l, and m; with different amount as follow:
A = 200k + 100l + 150m
B = 500k + 200l + 500m
C = 300k + 110l + 300m

Now, what I want to do is to input a goal as the amount of materials I need, and the worksheet to display the least amount of Ore needed.
Let's say I need 3 000k, 4 000l, and 5 000m:

xA + yB +zC > 3000k + 4000l + 5000m

And I want to minimize x+y+z, which will indicate the least amount of Ore I need to gather to reprocess into the materials I need.

Any help will be appreciated! I'm sure there's a way to do this on excel, but I've never done similar task before.
Many thanks!

[Alf]

Perhaps you could use the excel add-in solver?

a_b_c_mix.jpg

The values in range C10:E10 is found by using the "SUMPRODUCT" function. For C10 the formula is "=SUMPRODUCT($F$7:$F$9,C7:C9)"

The Object function (cell C14) is set to minimize the difference between range C10:E10 and C12:E12.

Looking at the different units of k, l and m and the requirement the problem is to meet the demand of 4000l, solver does that with 40 units of A giving total "overshot" of 6000 as the best solution. Testing manually by setting F8 to 20 meets the demand for l = 4000 but the total "overshot" is then 12000 or testing with F9 equal to 36.36 also meets l = 4000 but in this case the total "overshot" is around 13815 so the solver solution is the one with the smallest "overshot"

Alf