Tag Archives: Solver

Excel 2010: Macros, Solver and Data Analysis

When you first use Excel 2010, you may have a few items that you have to go hunting for in the ribbon.  If you use macros, you will need to have the Developer tab available.  By default, the Developer tab does now show.  On the File tab, click Options, then select Customize Ribbon in the left pane.  On the list on the right, click the Developer check box and then click OK.  The developer tab will now be available for you.

Your buttons for access to macros and Visual Basic elements are on the developer tab.  In addition, you have form and Active X controls, XML schemas, and Add-Ins. There are two of these common Add-Ins that you might be looking for that you used in previous versions of Excel or that you would like to explore.  To get these to show up, click on the Developer tab and select the Add-Ins button (look for the gears on the button).  Then, click to place a check box for Analysis Toolpak and Solver Add-in.

Once these two elements are enabled, they will appear under the Data tab.  The Data Analysis button will be on the far right side of the Data tab ribbon.  This is a fantastic tool for statistical analysis of your data.  If you currently use a separate statistics package for market research or similar analysis, you might be able to use Excel instead.  Many of the everyday market research statistics can be done within the data analysis feature of Excel!

Solver is a great tool, too. Once you click the Solver option, it opens a dialog box that will allow you to enter parameters and conditions to try to solve a problem.  For example, if you are attempting to rent vehicles to transport a certain number of people to a conference, you could use solver to plug in the total number of people and the target budget.  You would input which cells had the numbers in it that could change and adjust (i.e. the number of vehicles and the number per vehicle).  You would also key in the constraints, like how many people could ride in each vehicle type (van, car, etc.) and that there could only be whole numbers of people in each vehicle. Once you set all of this information, Solver will attempt to analyze it for you. I actually used solver to figure this out for one of my advanced Excel classes.  It was really interesting to watch the class come together to think through all of the constraints, etc.

Enjoy using your tools!

________________________________________________________

For more information on BOSS software offerings, the BOSS degree and certificates, and how the BOSS program can help you with your career, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.