Goal Seek Question

[tryingtoexcelatexcel]

Im using this code that says "'find the value of cell. Even if the cell is a formula, it is ok! (Originally, goal Seek does not allow this cell to be a formula.)"

I will admit I found this on the internet as I didnt know how to do it.

But when I tried with a linked cell it didnt provide the right answer.

I had to create another macro to copy and paste as values

Is there anyway to link the cell instead of using values only?

Please Login or Register  to view this content.

[tryingtoexcelatexcel]

Any help please?

[MrShorty]

It is difficult to make suggestions without the rest of the context. All you have given is the your code (that you say doesn't work) with no indications of what the variables contain or what is in the spreadsheet.

I am often surprised on this forum how many "goal seek" questions would be much better solved as algebra problems. Something like y=x+0.1*x if I want to make y=10, what value should x be? Certainly that can be solved using Goal Seek, but it would be much easier to solve for x algebraically and put that formula into the spreadsheet for x. My first suggestion is to look at your formulas and see if it would be easier/better to solve this as an algebra problem before making it a programming problem. Since you have not given us any indication what your formulas look like, we cannot do this for you.

If I understand what you are trying to do and it is determined that Goal Seek is required for a solution, my suggestion for this is to avoid the whole "value cell is a formula" issue by adding a simple helper cell/column. Assuming your desired formula "set to value" cell is column B2, and your "set target cell" is C2, then D2 could be a simple =B2-C2. Then your goal seek routine will tell goal seek to A) Set target cell: D2 (the helper cell)
B) To a value of 0 (this is no longer a formula, but a value that will be the same for every row).
C) By changing: whatever the changing cell is
This way I don't have to worry about Goal Seek's unwillingness to use a formula for the target value, because my target value is always the same. If you really dislike the helper cell, you can add the subtraction as part of the target cell column (=current formula-target value cell).

If I understand what you are trying to do, I would expect to approach the problem in one of those two ways. Beyond that, we probably need more details about the problem you are trying to solve (a sample file uploaded to the forum would probably be the easiest) in order to provide more specific suggestions.

[tryingtoexcelatexcel]

Hi MrShorty,

I tried to come up with an algebraic formula but I'm stuck. Maybe my math is not up to par. Here is a sample of the file I am working with.

[MrShorty]

Since you did not share what you tried, I cannot comment on the formula you tried to come up with. There are several steps between column N and column BF (focusing on the net sales set, the direct costs set looks similar). Rather than try to combine it all into one big ugly formula, I would probably work backwards in sequence -- just like you are currently working forwards in sequence.

1) You know what value you want Total!BF52 to be. Enter =Input!B2 into Total!BF52.
2) The current formula in BF52 is =BB12+BB52. BB12 is a constant value, so I can easily solve this for BB52 BB52=target value-BB12. Enter =BF52-BB12.
3) The current formula in BB52 is =AV52*(1-AY52). Step 2 tell me what BB52 must be to meet the target value, so solve this for AV52 to get the target value for AV52 AV52=BB52/(1-AY52).
4) Continue working backwards towards column N. If you're careful with each step, you should be able to enter a simple formula into N52 as a function of V that satisfies the target value for BF52. The current formula in V52 is =V12+N52*S52. N52 should be =(V52-V12)/S52
4a). I note that AJ52 -- referring to both V52 and AD52 will potentially cause some confusion. AD52 is also a function of V52, so I would probably spend some time with this to eliminate AD52 from the calculation string. This will make V52 more complex function of AJ52 than the others, but should still be doable.

Taking good care at each step, that's probably how I would solve this problem. Then you eliminate the need for VBA and the calculations will calculate automatically as data are entered/edited.

[tryingtoexcelatexcel]

Hi MrShorty,

Actually I tried it on paper, I couldn't even solve it on paper so didn't have an excel version. Where I am getting stuck is where I am moving between Wins to P or D. which is your point 4a. There are two paths to a D. From either an Offer + Win --> D or an Offer + Win + P --> D

So my ratio D no P separates what goes into P and then D and what goes into D directly.

This is where my maths is not up to par and I was not able to solve it on paper which then meant I couldn't solve it in Excel. I just spent a good 2 hours this morning trying to figure out how to do it algebraically by getting only the "offers" variable on 1 side of the equation.

I'm basically still using the macro but with a copy paste macro as well so everything is in values. I just couldn't separate both "offer" variables onto 1 side of the equation..

[MrShorty]

AJ52 =AJ12+V52*(1-Y52)*AA52+AD52*(1-AG52)
AD52 =AD12+V52*(1-Y52)*(1-AA52)

Substitute AD52 into AJ52
AJ52=AJ12+V52*(1-Y52)*AA52+(1-AG52)*(AD12+V52*(1-Y52)*(1-AA52)). AD52 is no longer a part of this function -- now being only a function of V52. Solve this for V52 to get the formula that should go into V52 (only a function of AJ52). Can we assume you can solve this?

[tryingtoexcelatexcel]

oh wow.. I can't..

I was able to get Offer to one side of the equation but the other Offer is still stuck on the other side of the equation, I can't get both onto 1 side. My maths is pretty horrible I can't seem to simplify the equation.

[tryingtoexcelatexcel]

It is this component: (1-AG52)*(AD12+V52*(1-Y52)*(1-AA52))

I cant get the V52 alone so I can move it to the other side.

[MrShorty]

Use the distributive property to get rid of the parentheses:

(1-AG52)*(AD12+V52*(1-Y52)*(1-AA52)) -> Multiply both terms inside of the parenthesis by the (1-AG52) outside
(1-AG52)*AD12 +V52*(1-Y52)*(1-AA52)*(1-AG52)

AJ52=AJ12+V52*(1-Y52)*AA52+(1-AG52)*(AD12+V52*(1-Y52)*(1-AA52)) should then become
AJ52=AJ12+V52*(1-Y52)*AA52+(1-AG52)*AD12+V52*(1-Y52)*(1-AA52)*(1-AG52) subtract the terms that do not contain V52 from both sides:
(AJ52-AJ12-AD12*(1-AG52))=V52*(1-Y52)*AA52+V52*(1-Y52)*(1-AA52)*(1-AG52) factor V52 out of the right side of the equation:
(AJ52-AJ12-AD12*(1-AG52))=V52*((1-Y52)*AA52+(1-Y52)*(1-AA52)*(1-AG52)) now divide both sides by that mess in parentheses to solve for V52:
(AJ52-AJ12-AD12*(1-AG52))/((1-Y52)*AA52+(1-Y52)*(1-AA52)*(1-AG52))=V52 now you have your formula that goes into V52.

[tryingtoexcelatexcel]

Use the distributive property to get rid of the parentheses:

(1-AG52)*(AD12+V52*(1-Y52)*(1-AA52)) -> Multiply both terms inside of the parenthesis by the (1-AG52) outside
(1-AG52)*AD12 +V52*(1-Y52)*(1-AA52)*(1-AG52)

AJ52=AJ12+V52*(1-Y52)*AA52+(1-AG52)*(AD12+V52*(1-Y52)*(1-AA52)) should then become
AJ52=AJ12+V52*(1-Y52)*AA52+(1-AG52)*AD12+V52*(1-Y52)*(1-AA52)*(1-AG52) subtract the terms that do not contain V52 from both sides:
(AJ52-AJ12-AD12*(1-AG52))=V52*(1-Y52)*AA52+V52*(1-Y52)*(1-AA52)*(1-AG52) factor V52 out of the right side of the equation:
(AJ52-AJ12-AD12*(1-AG52))=V52*((1-Y52)*AA52+(1-Y52)*(1-AA52)*(1-AG52)) now divide both sides by that mess in parentheses to solve for V52:
(AJ52-AJ12-AD12*(1-AG52))/((1-Y52)*AA52+(1-Y52)*(1-AA52)*(1-AG52))=V52 now you have your formula that goes into V52.

oh my. I see. so you were able to get the V52... + V52... on the right side then you factored out the V52

thank you very much, it has been so long since I've needed to do algebra (ashamed). I was just looking for the excel solution.

mr shorty thank you very very very very much.

[tryingtoexcelatexcel]

Apologies.. I tried to get the same answer as when I use the goal seek function.

But it isnt the same. I actually got quite a high number, 130m plus

Could it be in AJ52 I am double counting?

[MrShorty]

Differences between what you did and what I would have expected (working backwards).

1) BJ52 has your target value. Based on the first file, I expected BF52 to be your target value. Perhaps I have misunderstood, but make sure that you know which cell is your desired target value.
2) With BJ52 having the target value, you put =BJ52-BB12 in BF52. I would have expected =BJ52 to create a copy of the target value in BF52. Again, this could be my misunderstanding.
3) Based on the formula in BF52 in the original file, I would have expected BB52 to be =BF52-BB12. You have =BF52/(1-AY52) in BB52.
4) and so on -- each step seems different from what I expected (except for the AJ to V step).

I am uploading my testing sheet. In row 52, you can see your formulas. In row 53 you can see mine. I compared my backwards result to forward results from your original spreadsheet, and row 53 matches those from the original spreadsheet. It's possible I misunderstood something or you have made a change on your end. In any case, it looks to me like you just need to go through and be more careful with the algebra at each step in the backwards calculation.

For the record, I get a result in N52 (well N53) of about 39E6. I don't know what constitutes a "high" number, but that seems to be the solution for the problem I solved.

[tryingtoexcelatexcel]

Mr Shorty, I can't thank you enough. Really thank you very much.

[tryingtoexcelatexcel]

Mr Shorty, may I ask. Now I need to add some if conditions to the equation. For example.. if the cancellation rate is bigger than 10% then 0% or something along these lines.

I dont think I can use algebra to solve this now right? I need to use goal seek?

[MrShorty]

I don't see why a simple condition would change the overall algebra. For example, looking at the AV - BB pair:

Current forward function: BB=AV*(1-AY)
Inverse function AV=BB/(1-AY)

Hypothetical forward function with condition BB=AV*(1-IF(AY>0.1,0,AY))
Hypothetical inverse function with condition AV=BB/(1-IF(AY>0.1,0,AY))

All we do is substitute the IF() function in for the simple reference. Otherwise, the algebra and logic are all the same. The only thing that might happen is, in some stages, you could get a div/0 error, and you would have to think through what that means -- It could mean no solution is possible and even goal seek won't solve the problem. That involves looking at the exact calculation you want to do and understanding it.