ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Excel Goal Seek Function

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel Goal Seek Function

    Hi,

    Does anyone known if in db2 exists a function like Excel Goal seek?
    Many thanks.

    Bye

  • #2
    Can you describe what you're trying to do? I'm not familiar with Excel Goal Seek. I did a quick Google on it, and it seems to relate to Excel formulas, which doesn't make much sense in DB2.

    Comment


    • #3
      HI Scott,

      thanks for your reply,

      my English is too poor to explain my problem.

      really I need a function like what is explained here:

      Stop guessing! Susan Harkins shows you how to use Excel's Goal Seek what-if analysis tool to achieve your goals.


      I need to recalculate the rate based from another condition. I know is a bit complicated, I was thinking about regressions functions in db2, the examples and the explainations in db2 reference are very poor,

      Is there anyone who know it?

      Many thanks.

      Bye

      Comment


      • #4
        According to your link, Goal Seek does this:

        1) You tell it a cell that contains an excel formula
        2) You tell it a goal result of the formula
        3) You tell it a cell used in the formula that you want to adjust

        And it figures out what the proper value of the cell in #3 would be to meet the goal in #2.

        So that makes sense in Excel where you have formulas, but does not make sense in DB2 where you do not have formulas.

        Comment


        • #5
          Hi there, I read the document you just shared with us and learned a bit about excel's goal seek. So in simple excel terms: You have input fields, a result field (with a formula behind it). Goal seek will ask you which field you'd like to change, then prompts the value to set it to and then asks you which field (or "a cell" in excel) it can change in order to garantee that the formula is still correct.
          Simple pseudocode example of what goal seek does:
          var A = 5
          var B = 10
          var Result = A + B
          // Result is obviously = 15.. Now for the goal seek function:
          set Result = 20
          set var that must be changed to give a true expression = var A
          //goal seek's logic:
          if A + B = Result:
          A = A //since it's correct nothing has to change...
          else
          //showing it with B and Result's values here to make everything a bit more clear:
          A + 10 = 20
          A = 20 - 10
          A = 10
          //goal seek will now change A to 10 and it's done!
          end-if

          I am honestly not sure why you would need something like this in DB2? Maybe you need it in an RPG program and you're trying to solve it using DB2? Don't, this is plain programming logic. You could either based on your formula write a logic that achieves the desired outcome(s) yourself using IF, ELSE statements or a SELECT. If there are many formulas etc... You could probably write a service program to achieve this.. You could even generalize it so it works on any formula.. But that would be a lot of work.. I would do one of the following things:

          - SOLUTION1. something like write every formula you have in programming logic as a "String" as well... Example:

          dcl-s formula = 'a^2 + b^2 = c^2';
          goalValue = MyGoalSeekServiceProgram(formula:varForNewValue:ne wValue:varAllowedToChange); //call to your service program which calculates the goal you seek and returns the correct value for that "goal".

          You could then (in your SRVPGM) split the formula "String", into an array or something and you could easily tell how many variables are on either side of the equasion.. Compare them with the other inputs you gave NOTE (you also want to give the actual values of the variables in the formula at runtime to your SRVPGM as well) - for this I would attempt to write a procedure to somehow parse the formula with it's values to a Datastructure or something and pass it to the SRVPGM as well, a lot of options though you'd want something generic that works with any formula, so you'll have to think carefully about your approach to this)

          Using all your parameters you can then calculate the desired outcome as goal seek would.

          - SOLUTION2. Put your formulas in a DTAARA (way cleaner) then use these in SOLUTION1 instead of the hardcoded dcl-s formulas (this way adding formulas has to be only done in the DTAARA, if you ever need a new one you don't have to change the program.. or recompile everything) --> only works if your program is generically capable of handling any formula.

          - SOLTUION3. Use excel (if this is at all an option), use it's native goal seek function and then read your result from EXCEL into RPG or wherever you need it. I think most programming languages can easily grab data from excel. I would go for this if it's an option... Because programming a generic goal seek in RPG is difficult, it requires a good understanding of generic programming as well as a good understanding of Math. It's possible.. But difficult

          Many other solutions plausible...

          Finally, are you sure that you need this? Can't you think of any other way to solve the problem you're facing? Maybe talk to a teamlead, an experienced colleague and find out if you're actually on the right track trying something like goal seek in DB2/RPG.

          Kind regards,

          Lorenz

          Comment

          Working...
          X