"Control" Factor

[athinaida]

Hello,

I have the following case (Excel file attached):
After a complex calculation (in the background), I get 2 final Results ( Cells F7 and F9)
The value that we find in the cell A8 plays a major role in this background calculation (so, there is a correlation between A8 and the 2 final results, which I can't Show, because a lot of different worksheets and calculations are involved, but when the A8 changes, then the 2 results Change correspondingly)

For the definition of the A8 value, I have a short analysis (really simple) in the line 2, where the value A2 plays an important role.

What I would like to do is that I have a Kind of "control" formula that defines the value of the cell B2 (Factor 1), in order that I get as a result:
- at least 20% as Result 1 AND
- at least 10% as Result 2

Is something like this possible?

[MrShorty]

Short but useless answer, yes, something like this is almost certainly possible. I didn't understand your explanation of the relationship between B2 and result 1 and 2. I think it will be difficult to give a more specific answer without knowing something more about what you are trying to understand or do with this "control factor".

[athinaida]

Well, I would like to set in a quick way this B2 value (Factor 1) which brings me to the Minimum requirement of the 2 results.
Was it a bit more clear now??
I don't know how to better explain it :-/

[MrShorty]

Without knowing the details, it is difficult to make a solid recommendation. My first guess, because it works in a lot of different situations is to use Excel's built in Solver utility (https://support.office.com/en-us/art...b-f63e45925040 ). My best guess at what you are trying to do might look like:

1) I assume you can incorporate B2 into your calculation in A8. Make those changes to the A8 formula.
2) Compute in a convenient place (maybe even a hand calculator) whatever "20% of Result 1 [F7]" means to you.
3) Call Solver and tell it to:
3a) Set Target Cell: F7
3b) To a value of: whatever you calculated in step 2.
3c) By changing: B2
4) Note and evaluate Solver's result (assuming it converges onto a solution).
5) Repeat for the A2,B2 -> A8 -> F9 sequence of calculations. Compare the 2 results for B2 and choose which one you want.

At this stage of development, it is probably easiest to run Solver manually and evaluate the results manually. Once it is determined that this approach will reliably work for you, then we can look at automating this, or explore other options.

[athinaida]

Thank you so much! This is exactly what I was looking for! Great, it worked!