How can I change a range of variables in percentage that equal 100% and end up giving two values that are needed, one a ratio en another one a percent

[bras87]

Hello dear fellow excel fanatic,

I have a question:

I have an excel sheet where I manually change percentages to reach close to two required values. I would like to see if I can make this automatic with a macro or formula.

The range I use is between 1 and 100 %. I have to calculate how much percentage I need to use for per ingredient giving two nutrient variables.

View image: excel problem

In this link one can find a picture of a hypothetical equivalent.
Part 1. I manually change the percentages to reach a total of 100% and get two outcomes that are close to my goals.

So I set my goal to 30%mep and a 2.5 F/C ratio.

What I do then is manually change the percentages until I reach the closest number to the two required values.

I tried to make a formula but could not because If i calculate back there is no way of knowing the Kcal.

Part2. As I change the % of ingredients this part enters the variables and calculate the end result.

Part3. is the actual calculated result

So my question is can I make a macro or a formula to solve this.

-With a macro so that my manual input of percentages is done automatically
-with formula so I calculate in a reversed way back to the % of ingredients I need, as I set up the two requirement for nutrients.

I would really appreciate the help I have tried a everything Analyses, Macros, Formula's. I am really stuck.

Thanks!!