Goal seek for two cells

[YasserKhalil]

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

Sub Goal_Seek_Tax()
    With Worksheets("Sheet1")
        .Range("G10").GoalSeek Goal:=.Range("C10").Value, ChangingCell:=.Range("H10")
    End With
End Sub

Thanks advanced for help

[MrShorty]

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.

[YasserKhalil]

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

[MrShorty]

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

[YasserKhalil]

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 ..

[MrShorty]

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.

[YasserKhalil]

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?

[MrShorty]

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.

[YasserKhalil]

My try but not complete
I put this formula in D10

=$C10*0.05*$H10

and in E10

=$C10*0.1*$H10

and in F10

=$C10*0.25*$H10

cell H10 (the changing cell)

In G10 put the formula

=SUM(D10:F10)

Last I use this code

Sub Goal_Seek_Tax()
    With Worksheets("Sheet1")
        .Range("G10").GoalSeek Goal:=.Range("C10").Value, ChangingCell:=.Range("H10")
    End With
End Sub

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

[MrShorty]

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.

[Crooza]

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

[YasserKhalil]

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

[Crooza]

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.

[Crooza]

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.

[YasserKhalil]

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

[YasserKhalil]

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

[Crooza]

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

[YasserKhalil]

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

[MrShorty]

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.

[Crooza]

replace the old macro with this

Sub goalseek()
Dim Opt_a, Opt_b, Opt_c, t As Integer
Dim A, B, C As Single

Range("D10:f10").Clear

Range("E10").Select
ActiveCell.FormulaR1C1 = "=(RC[-2]-RC[-1]*R[-1]C[-1]-RC[1]*R[-1]C[1])/R[-1]C"
Range("D11:F12").Select

For t = Int(Range("b10").Value / 2) To Range("b10").Value
    Range("d10").Value = t
    Range("G10").goalseek goal:=1, ChangingCell:=Range("F10")
    
     A = Round(Range("d10").Value, 2)
     B = Round(Range("e10").Value, 2)
     C = Round(Range("f10").Value, 2)
     
    If A > 0 And B > 0 And C > 0 And A = Int(A) And B = Int(B) And C = Int(C) Then
         
    
     A = Range("d10").Value
     B = Range("e10").Value
     C = Range("f10").Value
       
     GoTo 10
     
    End If
Next t
10
Range("d10").Value = A
Range("e10").Value = B
Range("f10").Value = C

End Sub

[YasserKhalil]

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

[Crooza]

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

[YasserKhalil]

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

[Crooza]

It works for me. See attached file

[YasserKhalil]

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

     A = Round(Range("d10").Value, 2)
     B = Round(Range("e10").Value, 2)
     C = Round(Range("f10").Value, 2)
     
    If A > 0 And B > 0 And C > 0 And A = Int(A) And B = Int(B) And C = Int(C) Then

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

[Crooza]

Great. Glad it worked