automating percentages calculations

[mwedemeier11]

Hi, I need some help with an Excel sheet that I'm trying to write. I'll explain it the best I can.

What I do at my job is schedule production runs for bakery items. these items consist of many ingredients that go into them (Flour, Sugar, etc.) The products go into the run at certain percentages (20% of the run is flour, 30% is eggs, 15% is Sugar, etc) Many times we will have two or more types of a product, sugar for example. between the different types they can all have a different cost per pound, so what we try to do is use the cheapest stuff first to save money. So for part of this excel sheet what I would like it to do is to automate what percentage I should use the sugar based on cost. the total percentage for the sugar would add up to 100%.

for the second half of this sheet, there is another variable to account for. for this product we also need to hit target specifications so the product will be ok to sell. The example is Protein in this situation. we have a target protein that we need to hit per serving of 24.2 grams. I have the file set up to convert the protein percentage to protein per serving in grams, so that is already done.

my problem with this sheet is I can only seem to do one or the other. I can set it up to use the cheapest product first and add up to 100%, but it won't hit the target protein. Or I can have it set up to hit the target protein, but then I won't hit the 100%, it's be above or below. I need it to account for both variables and then give me the suggested percentages.

So is there a way that I can have it formulate what percentage to use something at to hit a target and equal 100%?
all of the cells in blue are cells where we have the information provided to us, so you don't need to worry about that. all of the cells that are clear, are ones that I have formulas written in, and all of the cells that are yellow are the percentage cells for the product. currently there is no formula written in the yellow cells, and you have to manually enter it and guess. We also do not need to use all of the products that are listed either, they are just what are available

tl;dr I need to account for two variables. hit target protein, and have the total percentage equal 100%

I owe you huge if you can help me out with this!


Percentage Calculations.xlsx

[Kaper]

This sounds like a task fol Solver - if you do not see it on the right end of Data ribbon go to options-addins-manage and select it. (check with MS pages or help file if it was not that easy - I have no english version handy).

Once you have solver avalable your gola will be the lowest cost (so cell K26 as min.). The changed cells are these in column H. Boundary conditions/limits
H28=1 (100%)
N29=0
column H>=0 and column H<=columnG

Solve should find an optimal solution. See attached file.

[mwedemeier11]

Thank you so much Kaper! this is absolutely perfect, and what I was trying to do.

[Kaper]

Thanks for rep. point. Would be also lovely if you mark thread as solved (see how-to in forum rules).