Excel solver

Posted on 2011-10-22
Last Modified: 2012-05-12
I have a table in excel spreadsheet that summarize data from a number of detail tabs.  
The cost function is represented in Table 1:  There are three competing alternatives.  Their cost depends on the year they become operational

The decision parameter is the year (represented in table 2 or equivalently Table3)

THe objective is to select the year for each alternatives so that total cost is minimal, subjected to 3 constraints (based on Table 3):

1)   The choice value in Table 3 (B11 to F13) is either 1 or zero.
2)   The sum of each row (G11 to G13) is strictly = 1.
3)   The sum of each column in table 1 (B14 to F14) is bounded by a parameter, say 2 in this example.

I never use Excel to perform optimization, often get by using Perl.  This time, the users request the result in Excel....

I would greatly appreciate any help from the respectable gurus in this board.
Thank you in advance,
Table 1: dollar amount
    A      B      C      D      E      F
1   year   2013   2014   2015   2016   2017
2   Alt1   23     34     36     39     41
3   Alt2   0      15     24     32     42
4   Alt3   0      0      40     41     43

Table 2: decision parameter. Column B values can be any val;ue matching the values (year) in row 1 of Table 1 (subjected to the constraint given below:

    A       B
6   alt1    2013
7   alt2    2015
8   alt3    2015

Equivalently, Table 2 can be written as
Table 3: Constraintd

    A      B      C      D      E      F      G
10  year   2013   2014   2015   2016   2017  SUM(B:F)
11  Alt1   1      0      0      0      0      1
12  Alt2   0      0      1      0      0      1
13  Alt3   0      0      1      0      0      1
14  Sum    1      0      2      0      0      3

Open in new window

Question by:cpeters5
    LVL 7

    Expert Comment

    seems simple enough if I'm understanding it correctly. One question though: is total cost simply the one value corresponding to the year the alternative starts? or is it that year plus all subsequent years?
    If the former, then in your example would the correct solution be Alt1: 2013, Alt2: 2013, Alt3: 2014?
    (total cost = B2 + B3 + C4  =  23 + 0 + 0 = 23)
    or if the latter, would the correct solution be Alt1: 2017, Alt2: 2016, Alt3: 2017?
    (total cost = F2 + E3 + F3 + F4  =  41 + 32 + 42 + 43 = 158)

    Author Comment

    What I am looking for is the algorithm rather than the anser.  The actual problem is much bigger to solve manually.  This is just a simplify example to demonstrate how the solver works.

    The cost function: what we are minimizing is the sum of each algernative value for the year they start.

    LVL 7

    Accepted Solution

    yes, I realize you're not looking for the answer to the example - but I asked because depending on your answer it would affect the approach.

    That being said, I took your tables and threw them into a sheet. Then I added a cell to calculate the cost, which is simply the formula:

    Then using Excel's solver, you would set the objective cell (B16 in my case) to "Minimum"
    by changing cells B11:F13

    Subject to the constraints:
    (must be a 0 or a 1)
    B11:F13 <= 1  
    B11:F13 = integer
    B11:F13 >= 0

    (sum of alternatives in each year <= 2)
    B14:F14 <= 2

    (each alternative only once)
    G11:G13 = 1

    Seems to work for your example.

    LVL 26

    Expert Comment


    If I've understood the requirements correctly, your solution does meet the constraints, but it's not the minimum (e.g. "2013, 2013, 2014" gives a total cost of 23). The problem is that the GRG Nonlinear method you used can return local (not global) minimums. On the other hand, the Evolutionary does give the global minimum - but I don't know whether that's guaranteed (as it would be with Simplex LP).


    Are zero-values allowed - there's no explicit constraint preventing them?


    Author Closing Comment

    Though it does not lead to a solution, there is enough information for me to follow and eventually got there!

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now