Solving for Unknown Variable

    I am very new at working with VBA and have been trying to devise a way to automatically solve for a missing variable in a formula. In the attached example I have inserted a spin control which increments by one when pressed. What I am wondering is if some sort of automatic counter can be devised which will increment the Equity Dividend Rate in the example until the Capitalization Rate 1 is equal to Capitalization Rate 2. Any help would be greatly appreciated.

    Re: Solving for Unknown Variable


    Hi thomas,


    Any reason why you can not use a formula?


    This will give you the required Equity Dividend Counter


    Code
    F7: =((F9-F3)/D6)*10000


    As you can see the result is not a whole number. So even using code on the spin button would not be too accurate.

    [h4]Cheers
    Andy    [/h4]

    Re: Solving for Unknown Variable


    Thank you for the reply Andy. The reason why I am not using a formula is because of the variable I am trying to solve for. The example I used was not the actual problem I am working on, but if I can come up with some type of counter then I can solve a much more complex set of calculations. As far as accuracy goes I only need it to six digits.
    Thanks again, Thomas

    Re: Solving for Unknown Variable


    To do this, set up a cell that finds the difference between Rate1 and Rate2.
    Select this cell, then Tools>Solver, which will open with your cell selected.
    That is the target cell which you want to optimise to 0, by changing the cell linked to the counter. (This, in effect, alters the counter until the two rates are equal, and is not an integer in the simple model you gave.)
    If it can find a solution it will tell you: press Accept Solution.


    Cheers


    Stephen

    Re: Solving for Unknown Variable


    Thanks for taking the time to consider my problem Steve, I really do appreciate everyone's input. I have tried using Solver before to arrive at a solution but for some reason it does not produce the correct answer. In my worksheet, the variable derived from my devised counter is the end result of thirty-nine different moderately complex formula calculations. I believe that the incorrect results are due to the order in which each formula is executed. For whatever reason, Solver does not seem to execute at the end where it should but at the beginning. That is why I thought that if I could come up with a counter in VBA I could control when it is to be executed in my long daisy chain of calculations. I used the manual counter linked to the spin control to make a very basic counter like in my example and it produced an accurate result. Unfortunately it just takes a heck of a long time to work. I'm sorry if my explanation is a little unclear but due to size limitations (413 kb) I can't post my actual spreadsheet to give you a better idea of what I am trying to do. Thanks again.
    Thomas

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login