+ Reply to Thread
Results 1 to 6 of 6

Complex Optimization problem through VBA

  1. #1
    Registered User
    Join Date
    08-09-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    3

    Complex Optimization problem through VBA

    I need to solve an very important and abstruse optimization problem using VBA in excel.
    I have attached the workbook with this question and basically, i need my macro to check all combinations
    with 0.2% - 5% (with increments of 0.2% - so values would 0.2%,0.4%.....5%) in each cells from Range "Assumptions!(P3:S31)"...
    What combination would maximize the value in cell "Backtest!I284"
    I with a few of my buddies have been trying to solve this problem for so many days, but it the problem we
    are facing is that there are too many possible combinations and the macro takes hours and hours to compute and then crashes.
    I met a couple of professors who said that I would need a dynamic programming approach to solve this but I haven't been
    sucessful in that front either. If there is any genius out there who can help me with this, I'll be extremely grateful.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Complex Optimization problem through VBA

    Sorry but I don't understand what you are doing. More details are required I think.

  3. #3
    Registered User
    Join Date
    08-09-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Complex Optimization problem through VBA

    My goal is to maximize the value of cell I284 in sheet "Backtesting".
    This number is generted from a combination of percentages in cells "P3:S31" in sheet "Assumptions" using array formulas that are listed int he spreadsheet.
    I want to test all combinations in the range "P3:S31" in a way that all cells in this range can have a value of 0.2% to 5% with increments of 0.2
    so lets say cell P3 can have a value of 0.2% or 0.4%, 0.6%, all the way to 5% . Same is the case for all cells in this range.
    P3 could be 0.4%, where S16 could be 2.4%
    The goal is to maximize I284 and i need my macro to figure out what combination of percentages would do that.
    Does that make sense?

  4. #4
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Complex Optimization problem through VBA

    So each cell can take any of 25 values and there are 116 cells?

    You're going to need a super-computer, or a different approach.

  5. #5
    Registered User
    Join Date
    08-09-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Complex Optimization problem through VBA

    Exactly, 25 values and 116 cells. so the total number of combinations come out to be 25^116 according to combinatorics theory. What other approach is there from an algorithmic stand-point? Is there no way to optimize this?

    Usman

  6. #6
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Complex Optimization problem through VBA

    It looks like you get the biggest value when everything is 5%?

    Without more details of your model it's a bit hard for anyone to suggest alternatives.

    Edit: why do you have a random cell - couldn't that throw everything out?
    Last edited by StephenR; 08-21-2012 at 06:12 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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