Goal seek for two cells

Hello everyone
I have two constants in B10 and C10 ( 185 and 21.6)

In the cells (D10 & E10 & F10) I need to solve discover the values (these are unknown values)

D10 & E10 & F10 would be of total 185
At the same time I need to sum (D10 * 5% ,E10 * 10% , F10 * 25%) to get the total 21.6

So I have two goal seeks (yellow cells)

I can deal with just one goal seek not two

Please Login or Register  to view this content.

Thanks advanced for help

I would first make it an algebra problem. It is basically a "solve a system of equations" problem. For an algebra refresher: http://www.purplemath.com/modules/systlin1.htm you might also look back through some of your old threads, because it seems like I have responded to other "solve systems of equation" problems that you have asked.

The first real challenge I see is that you have a system of 2 equations with 3 unkowns, so there will not likely be a single, unique solution to this problem. For example, {70,71,44} also seems to satisfy the two conditions you describe, so why did you choose {61,83,41} as your preferred solution?

When all is said and done, I would probably use a matrix algebra/Gaussian elimination/=MINVERSE() type solution for a system of equations like this rather than Goal Seek/Solver. But that will require doing some algebra before doing the programming.

Thanks a lot MrShorty for reply
In fact I can't figure it out ..any possible numbers that fulfill the two conditions would be acceptable for me
The first is the total of the three unknown values would be 185
The second is each unknown value multiplied by fixed percent ( First * 0.05 + Second * 0.10 + Third * 0.25) should be equal to 21.6
Please give me the solution

Is there a spot you are stuck on?

If any solution is good enough, pick a random number for any of the three unkowns. Then, the problem reduces to solving a system of 2 equations in 2 unkowns.

This link shows an example of solving 3 equations in 3 unkowns -- it should be easy to adapt to 2 equations in 2 unkowns: http://www.excel-easy.com/examples/s...equations.html
This one is more math and not so much Excel, but the example is 2 equations in 2 unkowns: http://www.dummies.com/how-to/conten...the-inver.html
This video walks through solving a system of 2 equations in 2 unkowns in Excel: https://www.youtube.com/watch?v=bsgw3p36RUI

Of course, if you absolutely prefer to do this in VBA, the MMULT() and MINVERSE() functions are available to use in VBA. https://msdn.microsoft.com/en-us/lib.../ff836235.aspx

Is it possible to deal with the problem using Goal Seek ...?
Is it possible to deal with Goal Seek for two cells at the same time?

I need ready solution as I spent more than two hours and i couldn't solve it ..

Goal Seek can only handle one "target" cell, so you need to change the problem to a single target cell. Again, start with some algebra for a system of equations:

21.6-0.05*D10=0.1*E10+0.25*F10
185-D10=E10+F10

Assign any suitable number to D10.

Solve both equations for E10
Set the resulting equations equal to each other
Have Goal Seek solve this equation for F10
Use the resulting value for F10 to find E10.

I am trying to get your steps but I can't get them at all ..how can I deal with three unknown values ??? you told me to assign any suitable number to D10 .. if I did it I may got the other two values will have fractions and i need whole numbers if possible
Can you give me ready solution?

You did not mention the "all three must be integers" constraint before. Goal Seek, to my knowledge, does not have a "constrain changing cells to integers". Solver might be a better choice, if you must go with a "trial and error" approach.

With this additional constraint, I would much prefer a matrix algebra approach rather than a goal seek/Solver approach. The trial and error aspect will be easier to deal with, IMO.

The overall algorithm will look something like:
Choose a value for D10
Solver for E10 and F10.
Test if E10 and F10 are integers
If yes, then end
If not, choose a new value for D10 and repeat.

The hardest part of this is what would be a good strategy for choosing values for D10. It appears that if you start at 40 and try successive numbers, you would never be more than a few iterations from a valid solution.

My try but not complete
I put this formula in D10

Please Login or Register  to view this content.

and in E10

Please Login or Register  to view this content.

and in F10

Please Login or Register  to view this content.

cell H10 (the changing cell)

In G10 put the formula

Please Login or Register  to view this content.

Last I use this code

Please Login or Register  to view this content.

But first I can't figure it out to get the total 185 .. Second I got fractions not whole numbers(integers)
Hope someone help me find the solution

The algebra you describe does not look right to me.

G10=D10+E10+F10
H10=0.05*D10+0.1*E10+0.25*F10

Assuming you choose a value for D10
G10-D10-F10=E10
H10-0.05*D10-0.25*F10=E10

G10-D10-F10=H10-0.05*D10-0.25*F10
Solve for F10 and, for any chosen value of D10, you can find F10 and test if it is an integer.

See attached. I used MrShorty's logic, tried every number in column D from 1 to 185 then used algebra to calculate column E and goal seek to get column F. The trick is to create one formula for the two solutions. The easiest way to do this is to put the two formulas as a ratio and goal seek to that ratio but I then divided that ratio by (185/21.6) just to clean it up and get it back to 1. Then a macro that steps through each option goal seeking to get to 1. it will make more sense when you look at the file. Pretty easy to then select the integer only solutions

Thanks a lot for help
I didn't expect all these possibilities ..
Can I pick the nearest to 21.6 *2 which will be
43 , 107 , 35 ...


Or the first value would be half of the second value such as this possibility 49 , 99 , 37 (as 49 * 2 =98 and 98 is the nearest to 99)

I just need one possibility and I need to do the task using VBA
Thanks advanced for help

I can have a look later tonight maybe to work up something for you. I'm still not clear on how you're determining the best solution. It sounds like you just want any solution that works but there's something in me that says this is too random and there must be a solution that meets your needs better than another! Anyway I'll work it to either of the two options you've suggested above and get back to you.

Try this. I've left the percentages and the targets (185 and 21.6) as variables so you can change them and the macro will take this into consideration. As far as picking the "optimum" solution I went for the one with the least spread of numbers from Min to Max. Hope this is OK. Try it out and let me know if its what you want.

Thank you very much Mr. Crooza for this great solution
I will test it in the original file and let you know later if it is ok or not
Thank you for wonderful help
Best Regards

Sorry for disturbing you again Mr. Crooza
I have reached a point that is to divide the number 185 by 2 = 92.5 and look for the nearest number above it that achieve the task that is as for the example in Post # 7 the best and nearest numbers would be ( 94 / 39 / 52)
Hope this logic is acceptable and possible
Thanks a lot for help

No problem. I'll look at it tomorrow if that's ok.

Thanks a lot for reply
Take your time Mr. Crooza . Have nice time

While Crooza is looking at adapting his, I might suggest that you revisit the algebraic solution that I suggested above. I know we try to avoid algebra as much as we can. Without having worked it all the way out, I could see a solution that works like this:

1) Algebra to find expressions/worksheet formulas E10 and F10 at any given value for D10.
2) A test for if E10/F10 are integers and how far they are from being integers. This would likely be a =MOD() or mod() like function
2a) in preparation for the next steps, you might want to spend some time with this function to explore the resulting MOD() function. A simple MOD function is kind of a "saw tooth" shape, but it would probably be worthwhile to spend some time understanding what your chosen function looks like so you can reliably guide these next steps towards the preferred solution.
3) Use whatever algorithm you like to come up with an initial guess for D10. =B10/2 sounds like the preferred initial guess.
4) Use Goal Seek (or similar) to set this mod-like function to a value of 0 (or 1 or whatever the appropriate criteria would be) by changing D10.

replace the old macro with this

Please Login or Register  to view this content.

Thank you very very much for this wonderful code
But why I got these numbers (97 / 35 / 53)
I expect these numbers (94 / 39 / 52)

But in fact it is awesome .. Thanks a lot for this great help

I just edited the macro. It wasn't picking up the first solution for some reason - probably due to precision errors. The macro has just been edited and gets to the correct solution now

Thanks again for reply but this is not accurate now as I calculate it and got 21.55 not 21.6 >>>> the best numbers would be 94 / 39 / 52
Best Regards

It works for me. See attached file

Yes it seems that it is my fault
I am very sorry as I didn't copy all the code .. I just copied these parts only

Please Login or Register  to view this content.

and I didn't pay attention to the other modifications in the code
Generally now it is perfect for me
Thanks a lot for help
Thank you very much. you saved me

Best and kind regards

Great. Glad it worked