+ Reply to Thread
Results 1 to 2 of 2

Optimization of implicit variables

  1. #1
    Registered User
    Join Date
    04-19-2012
    Location
    U.S.
    MS-Off Ver
    Excel 2003
    Posts
    6

    Optimization of implicit variables

    I am trying to use an Excel spreadsheet to optimize an implicit variable. Since the variable is implicit, I use the Solver add-in to solve for it once I input all of my data. But, since I can't write an explicit function for the variable, I can't use Solver to maximize or minimize it. I could optimize it by hand using sensitivity analysis and all of that, but I really don't want to spend the time!

    Does anyone have any suggestions? I am currently trying to use a circular reference to iteratively solve for the variable, and then use Solver to maximize the final result, but I am not sure if it will work properly.

    With regards to using circular references to solve for an implicit variable, are there any good/standard ways to do this? The way I am using works but it is kind of clunky (I basically use an if/then statement based on the relative error between two values in the spreadsheet, and either add or subtract 0.001 to the variable accordingly until the error goes to 1e-6 or less.).

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,829

    Re: Optimization of implicit variables

    An actual example of what you are doing would probably help. Programming the various root finding algorithms into a spreadsheet is easy enough (http://en.wikipedia.org/wiki/Root-finding_algorithm). Solver's default is a Newton-Raphson type algorithm. You might spend some time with that.

    Sometimes, I prefer to create a user-defined function in VBA (http://office.microsoft.com/en-us/ex...996.aspx?CTT=1) using one of the above root finding algorithms. I sometimes find I get more reliable results (and I can better control the loops and convergence criteria) by coding the root finding algorithm in VBA than as a circular reference in the spreadsheet.

    Spreadsheets have enough cells/columns/rows that I sometimes find it preferable to program the "loop" as copies across columns or down rows. NR usually only needs a few iterations to converge, so I only need a few "copies" to get the algorithm to converge. Sometimes this works better than circular references.

    Those are some general ideas. Post some more specifics and we can give more specific suggestions.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. dir function returns not always implicit extension
    By bartw in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-24-2013, 03:38 PM
  2. implicit file reference
    By Tuee in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-10-2006, 10:55 AM
  3. [SOLVED] Possible to chart data for dates implicit within a range?
    By Nechama in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-01-2005, 06:05 PM
  4. implicit activation
    By jgreif in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-26-2005, 05:19 PM
  5. Replies: 7
    Last Post: 03-02-2005, 04:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1