Genetic Algorithm Optimization for MS Excel

Description

GA Optimization for MS ExcelThis software allows the user to take an Excel spreadsheet with any type of calculation data (no matter how complex) and optimize a calculation outcome (e.g. total cost). This is based on the selection of up to five design variables and up to five constraints. The optimization can be performed as a maximization, minimization or the attempt to reach a target value. Applications for this technique lie in every field of work. If the problem can be modeled in Excel, it can be optimized using this program.

The main advantage of this program over the Solver, which is supplied with Excel is that it can solve highly nonlinear problems or problems that feature discontinuous functions. Both of these can be problematic for the gradient-based optimization routine that Solver is based on. The software available here is the result of a term project in a class on engineering design optimization. It was written for research and should only be used for that purpose since it may contain bugs (please report any bugs to me using the contact form). It has sufficient capability for small projects and study examples. See below for the specs.

This software is provided free of charge. Two working examples have been included with the installer. Please read the terms of the license that is provided with the software before using it. This current version (v. 1.2) replaces the earlier one that was available from this site.

Please Note: With Office 2010, Excel’s Solver add-on actually includes a genetic algorithm (evolutionary) solver. You can read more about it here.

Introduction

Genetic algorithms (GAs) are based on biological principles of evolution and provide an interesting alternative to “classic” gradient-based optimization methods. They are particularly useful for highly nonlinear problems and models, whose computation time is not a primary concern. Continuity of functions is not required. Similar to other methods such as Simulated Annealing, they perform better than gradient-based methods in finding a global optimum if a problem is highly nonlinear and features multiple local minima. In general, GAs approach the entire design space randomly and then improve the found design points by applying genetics-based principles and probabilistic selection criteria. Although a large number of modified algorithms are available, a GA typically proceeds in the following order:

  1. Start with a finite population of randomly chosen chromosomes (“design points”) in the design space. This population constitutes the first generation (“iteration”).
  2. Evaluate their fitness (“function value”).
  3. Rank the chromosomes by their fitness.
  4. Apply genetic operators (mating): reproduction (reproduce chromosomes with a high fitness), cross-over (swap parts of two chromosomes, chosen based on their fitness to create their offspring) and mutation (apply a random perturbation to parts of a chromosome). All of these operators are assigned a probability of occurrence.
  5. Assemble the new generation from these chromosomes and evaluate their fitness.
  6. Apply genetic mating as before and iterate until convergence is achieved or the process is stopped.

A Windows user interface was created for the GA routine, which allows the user to easily use the GA model without much prior knowledge. As can be seen in the screen shots, an Excel file, which contains the calculation model, can be selected and cell references for the function value, all design variables and all constraints can be specified. On another tab, the user can modify the given GA parameters and then on a third tab, the user can run the GA algorithm and capture its output. Optionally, the user can save all GA and model parameters to a text file and restore them from there later.

Downloads

Download “GA Optimization for Excel” xlgaoptim_1_2_setup.zip – Downloaded 3197 times – 892 KB

Quick Start Tutorial – This document will get you going with the software. It will be included in the next revision as a help file.

Paper: “Thermal and Structural Stud-Wall Optimization in Excel using Genetic Algorithms” – This document shows some verification calculations (also provided in the download) and explains parameters and settings a bit more in detail.

This project is also now open-source on GitHub.

Screenshots

Revision History

v. 1.2 (Build 1018) (August 16, 2005):

  • Accepts now 5 design variables
  • Variables can be integer or real (on a per-variable basis)
  • Expanded cross-over type selection dialog
  • Nicer About dialog
  • Added Windows installer and uninstaller
  • Fixed bugs: – Implementation of decimal precision improved – Routine was only doing 1pt cross-over before, fixed now – Fixed problem with large negative values (caused hangups) – Fixed rounding problems (now scientific, not bank) – Improved constraint handling (precision-based comparison)

v. 1.0.0.10 (May 16, 2005):

  • 1 Target function [minimize, maximize, target]
  • 3 Variables [real only, lower / upper bounds]
  • 5 Constraints [“< =”, “>=”, “=”]
  • Excel file selection
  • Load / save model in text file
  • Plot each chromosome in every generation option
  • Application of fixed value constraint penalty
  • Option to define: – Number of chromosomes – Cross-over probability – Cross-over type [1P, 2P, uniform] – Mutation probability – Random selection probability – Max. # of generations – # of preliminary runs – Max. # of generations in preliminary run – Convergence tolerance – Constraint tolerance – Numeric precision
  • Installation from ZIP archive

Miscellaneous

  • Genetic Algorithm – Wikipedia page on the general topic.
  • Microsoft Excel Solver – Help document.
  • GA in Excel – Blog post announcing the new Excel 2010 functionality
  • Related commercial software (I don’t have the time to make mine commercial, so check these out for supported software):
    • Solver – From the makers of the original Excel plugin. has a hybrid evolutionary/classical solver.
    • GeneHunter – Comes in many flavors but also has an Excel interface.
    • OptWorks Excel – Also implements Simulated Annealing, Coordinate Pattern Search, Grid Search and other methods.
  • I just open-sourced this project. Look for the GitHub link in the post.

    • Hisham Hiz

      sir would you like get me some good rendering settings for sketch up for getting inerior ,exterior as a good setup for me please so please kind on me my email id is hishamkp0@gmail.com

  • satheesh

    Hi alex

    i need a help im not having any clear idea about genehunter in ms excel and to use it .i doing a project in construction resource levelling by using GA.how could i use genehunter in resource levelling. how to workout that

  • Anja

    Hi, could somebody help, because I have to multi objective problem and I don’t know if I can solve it with this solver? Thank you in advance.

  • Nandi manasa nandini

    good morning sir. i want to do my project on time cost optimization using GA but i don’t know how to do that. i have created a model of 18 activities in excel sheet & the problem is how can i use the evolver to it and how the population,fitness value,crossover and mutation etc are considered. pleas reply me soon
    thanking you

  • kurosh

    hi alexander,thanks,can we use this sWare for multi objective programming?
    i need more samples

    • I wish I had time to update this software. For now you’d have to create a weighted function or something similar.

  • John Taulo

    Please can anyone help me how to insert the design variables. the instructions are not clear to me. i have tried several times, it keeps on telling me that B is not an integer. Further, it does not plot the results

  • vasanth

    Hello, i am using this for my final project, small doubt in plotting graph from the iterations, is that possible in this GA optimization in excel, if it is possible, can yo explain it???????

    • I am assuming you mean to plot the values for each iteration. I have to admit that I haven’t opened the software in a while but have you tried to copy the text output – I thought that has the values in it. You might need to do some text processing before you can plot that data, though.

  • vasanth

    Hello, i am using this for my final project, small doubt in plotting graph from the iterations, is that possible in this GA optimization in excel, if it is possible, can yo explain it???????

  • Anderson4000

    Hi Alex. Great program, I have been using it recently to optimize a spreadsheet and it works just great! My spreadsheet has lots of IF statements which Excel Problem Solver simply cannot cope with but your software handles it all with no problems! It is a shame you have not developed it commercially or increased the amount of variables it can handle to 10. Anyway, I just wanted to pass on my appreciation of your work. Alan.

  • wilbur

    hello 
    I love your program but i dont know how to use it

    • It comes with documentation and a sample. Should be in the ZIP file.

  • Eddy Parkinson

    I like what you have done with the GA and want to build on it. I am a researcher looking at spreadsheet based optimisation. 
    I would like to be able to look at the code. 

    Thanks
    Eddy.

  • Tarek Nagla

    Dear Alexander Schreyer
    Pls, where the file==>  StudWall_Excel.xls  ?
    Thank you

    Tarek

    • It should be in the ZIP download.

      • Steve Sexton

        Okay, Where is the ZIP download?

        • Just remembered that it’s now an installer file (it has been a while). If I remember right, after installation, the sample should be under c > program files > as software …

          • Steve Sexton

            Only inequal_constr2 and landscape_nonlin are in the example directory.

  • sivam

    sir,

    i am also try to do a project with related to optimization of nonlinear and undefined function through the gradient based can you give some idea 

  • Russ Abbott

    P.S. Your GA found a solution to a problem that Evolver didn’t find.

  • Russ Abbott

    Alex, Since you are no longer developing this software, how about making it open source so that others can extend it?

    — Russ

    • I should really do that at some point. Question: Would the Turbo Pascal source code be of use to anyone now?

      • Russ Abbott

        Turbo Pascal! Wow! 

        Actually Delphi Pascal (http://www.embarcadero.com/products/delphi) is still alive and well.  The translation shouldn’t be too difficult. And even a translation into some other language would probably be a nicer way to begin for some people than to start from a blank piece of paper.So I recommend that you release it.

        Looking at the list of comments, I don’t see the one I (thought I) posted prior to the P.S. comment above.  As I recall, it said that I liked your GA but wished it (a) allowed for more variables and (b) were updated to work with Excel 2007.

      • Russ Abbott

        Turbo Pascal! Wow! 

        Actually Delphi Pascal (http://www.embarcadero.com/products/delphi) is still alive and well.  The translation shouldn’t be too difficult. And even a translation into some other language would probably be a nicer way to begin for some people than to start from a blank piece of paper.So I recommend that you release it.

        Looking at the list of comments, I don’t see the one I (thought I) posted prior to the P.S. comment above.  As I recall, it said that I liked your GA but wished it (a) allowed for more variables and (b) were updated to work with Excel 2007.

        • Russ Abbott

          Apparently the Delphi Pascal link was interpreted to include the final parenthesis. It shoudn’t:  http://www.embarcadero.com/products/delphi

        • Russ Abbott

          Apparently the Delphi Pascal link was interpreted to include the final parenthesis. It shoudn’t:  http://www.embarcadero.com/products/delphi

  • sunil

    sir
    iam glad using this software but can u help me with more constraints atleast of 10.
    plz help me my project depends on it

    • Sorry but I am not planning on any features at this point.

      Cheer, Alex

  • ken

    Love the program… Have used my own GA codes (calling PIKAIA) for many years. How did you implement the constraints (penalty function?). Would love to have a few more (like 10).

    Thanks for an excellent code. BTW – runs under Windows 7 w/ Excel 2002 just fine.

  • Nick

    I greatly appreciate you providing this software free of charge!!

    The usability/interface is great. I do have one question.
    I am trying to minimize a particular function (whose form is unknown). Your program appears to be working, however, it doesn't converge to meaningful solutions — EXCEL Solver finds much lower minima.
    I should note however, that I don't believe I have it set up correctly as the fitness function does not change from -1000000000000.

    I appreciate any help.

    Also, do you know of any free simulated annealing optimization programs?

    Thanks!!

  • George Ho

    I am very interested in your developed GA software. I would like to introduce your software to my students in one of my subjects. Could you mind sending me the two sample excel files you mentioned in the website? As I cannot find in your Windows installer. Many many thanks.

  • @harsha: Not sure, depends on how the problem is set up. If you can do it with a limited number of variables, then you should be okay.

    @Alex_Odessa: Localization may not be perfectly implemented in the software. Change the Excel/Windows settings in that case.

  • harsha

    can i solve flow shop scheduling problem using MS-Excel.

  • Alex_Odessa

    xlaoptim not works with built example.

    Error in point 0.5 (in program) or 0,5 (in Excel)
    Please help me

    thank’s

  • Daniele, At this point, this software does not link to other programs (even if you can set this up in Excel). You could try to use a software like VisualDOC.

  • Jan, This might have to do with calculation times in Excel. I haven’t tested non-instantaneous calculations and there may be the possibility that it picks a target cell value before it gets updated.

  • Jan Kiehne

    Alex, I am trying to use your software to optimize nonlinear maximization problems in the context of a principal agent model. Unfortunately, I don’t seem to be able to get your tool to work properly. Most of the times the process stops after the first generation (regardless of how many max runs I have speficied in the set-up) and it always picks the highest numbered chromosome of the last generation as the optimal solution which is obviously not correct. Also, from the plottings it seems as if the fitness is not being calculated correctly (alway same value of -1000000000). I would very much apprciate if you could give me a hint what I may be doing wrong. Regards, Jan

  • Daniele

    Hello, I’m a civil engineering student. Thanks very much for your software and for keeping it free.
    Maybe you have a solution for my little problem.
    My target function is the result of a FEM analysis based on the five design variables and so I can’t put it in excel as a formula, but it should be typed generation by generation, giving the current state of the design variables (or dynamically extracted from the FEM analysis, but this is much more complicated!)
    Is it possible?
    Any suggestion on which way I should try (also other softwares) will be apreciated.
    Thanks!!

  • Thanks, Ray for the comment – it is a good suggestion. I would also like to get unlimited variables/constraints into it. Maybe I can do some work on it over summer.

    Cheers, Alex

  • Hi Alex,

    I like your GA Program.

    Are you planning any updates to it?

    If so, could you make it calculation error tolerant. In other words, if a particular set of values does not give a result, just ingore and continue, or allow a restart from that point. If some number, set by the user of non-results, then stop.

    Thanks for the program,

    Ray

  • nitin m mohite

    Hello sir;

    if the number of variable is increases to 1000 can MS Exel ga solve the problem.

  • I need help files which contains some tutorial problems

  • I want to maximize objective fun
    subject to
    some constraints
    How to use the software

  • nitin Mohite

    Sir,
    I am doing my reserarch civil Engineering with specilization in WaterResources. For optimization of reservoir i want to use genetic algorithm techenique. for this i am getting following problem.

    1) can ga solve large size problem (3000 decision veriable and 3006 constraint)
    2) how to accomodate the constraints
    i am waiting for your reply

  • [quote comment=”2722″]Thank for your software. It’s great!
    Can you send me the full Excel spreadsheet ? There are some things I don’t understand much although I have read the Quick Start Tutorial , e.g how to insert the function…..
    Thank you![/quote]

    The target function is simply contained in a cell, e.g. C1 contains “=A1*B1” where A1 and B1 are your design variables.

  • calvados

    Thank for your software. It’s great!
    Can you send me the full Excel spreadsheet ? There are some things I don’t understand much although I have read the Quick Start Tutorial , e.g how to insert the function…..
    Thank you!

  • [quote comment=”2262″]Dear Alex,
    Thanks for the software, easy to use & efficient on excel for NLP. will i be able to do multiobjective optimization using this GA software? Thanks again[/quote]

    Vijay, I haven’t done much of that, but as I understand it, you just need to define the weights as variables (in addition to the design variables). After a few runs, the collection of solution points (which you can copy from the text box) gives you optimal solutions that lie on the Pareto front.

    Unfortunately at this point, my software only allows for five variables, but with a two-objective problem, you’ll only use one for the weights since the other one is (1-w).

  • Vijay

    Dear Alex,
    Thanks for the software, easy to use & efficient on excel for NLP. will i be able to do multiobjective optimization using this GA software? Thanks again

  • [quote comment=”1319″]i would like to use this software, but are there any simple examples to follow?[/quote]

    Farayi, There are two simple examples included in the installer. Try them out, they should run right off the bat. Also look at the Quick Start Tutorial (download) to help you along.

  • farayi

    i would like to use this software, but are there any simple examples to follow?