Calculating Percentages

Hi, I will try my best to explain what is going on with the attached sheet.

Rows 4 through 21 show the "Standard Formula" and in my job we will have multiple lots of the same product/ingredient and they can vary by price. I have Ingredient "A","B","Flavor", and "Other".

32.526% of the finished product will contain ingredient "A", 59.173% of the finished product will contain ingredient "B", 4.836% to "Flavor", and 3.465% to "Other".

Ingredient A and B are interchangeable ingredients, so I can use more than 59.173% of B if I want to. Ingredient B costs much less than A so we try to use as much of B as possible. The Flavor and Other ingredients will always stay the same so no need to worry about them (Highlighted in Yellow).

The criteria that we need to meet for this is that we have a "Target Protein Per Serving" and on this sheet it is 24.2 grams and the "Standard Formula" shows a finished product of 24.349 grams so we can lower this by .149 grams per serving.

The next table below (Rows 23 through 40) is pretty much identical except it also shows the amount of each lot available, product cost per pound, and how much product we would use based on the percentage. Currently on the "Adjusted Formula" table the "Formula" column is zero for every row except "Flavor" and "Other". Currently the formula is at 9.201%, so the remaining 90.799% needs to be determined. This is what I would like to automate. I would like it to automatically calculate the percentage of each ingredient starting with the cheapest products and working up to the most expensive, while the "Target Protein Per Serving" is still near 24.2 grams. it doesn't have to be exactly 24.2 but it can get closer by using less of product A and more of product B.

I know this is a lot to read, and I hope that you can understand it. If anyone can help me out I will owe you HUGE! Thanks for taking the time to read this, and let me know if you have any questions about it.

Percentage Calculations.xlsx