SOLVER Notes

SPREADSHEET CAPABILITIES

In addition to obtaining general proficiency in the use of spreadsheets to carry out calculations, you will need to know how to utilize some of the ‘higher level’ data analysis functions of Excel.

SOLVER:

This is a routine that essentially will utilize trial and error to determine the solution to a mathematical expression.

Ex. x + 2x² = 10

You might be able to ‘see’ immediately that the answer is x=2 by inspection,

Or know that you could find the answer using the quadratic formula.

Alternatively you could plug this into your calculator and start guessing ‘x’ values till it is ‘close enough’. This later approach is basically what solver does.

This is no longer simple if there is more than one variable:

Ex. 2x + 2y = 9

x² – 5y = -4

And certainly by the time you have three or more variables, algebra is very tedious (and error prone) and trial and error takes thousands of iterations.

Solver provides a way to find a solution to such problems quickly and easily in a spreadsheet. However, Solver is not installed with the typical default installation of MS Excel. You will have to use the main menu: for

• Office 2007 <TOOLS><Add Ins>, and check the box that says “Solver Add-In”.

• Office 2010, and the locaiton was under <DEVELOPER><Add Ins> where you can check the box of those Add-ins that are available .... hoping to see <Solver Add-in>

NOTE: if you bought a computer with MS Office installed, it is not unusual for the computer manufacturer to install an OEM version of Office that does not correspond to the MS Office CD Rom disk installation version. In this case, installation of solver or other add-ins may fail. The only solution is to completely re-install MS Office. Although an annoyance, it is easily accomplished using <Start><Settings><Control Panel><Add/Remove Programs>.

Use of Solver takes practice because unlike most other spreadsheet functions, the actual formulation of the solver problem is not shown in the spreadsheet. There will be some initial homework assignments to use this routine, but you will probably want to experiment.

STATISTICS, DATA ANALYSIS, REGRESSION.

You can carry out most of the statistical analysis such as calculation of variance and even linear regression simply using the built in Excel functions. However, there have been interfaces created for routine purposes such as carrying out a linear regression that have a nice ‘user friendly’ interface. Later in the course you will have to carry out data analysis including Analysis of Variance (not simply the generation of a table but interpretation of results). For this reason, you should have the “Data Analysis” tools enabled in Excel as well. This is accomplished by the same process as described above for “Solver” through the menu <Tools><Add-Ins> and checking the appropriate box.

Excel has many other add-in functions that you may find useful; I recommend you check them out.

In particular, you should become familiar with the graphing functions and the ability to efficiently create a logical multi-page layout of a problem.