Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

Excel solver

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

  • 2
  • 2
1 Solution
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)
cpeters5Author Commented:
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.

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.


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?

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now