2. Minimizing the Spread
The mean has a special characteristic that makes it
more useful for certain advanced statistical analyses than the median
and the mode. That characteristic has to do with the distance of each
individual observation from the mean of all observations included in
calculating the mean.
Suppose
you have a list of ten numbers—say, the ages of all your close
relatives. Pluck another number out of the air. Subtract that number
from each of the ten ages and square the result of each subtraction.
Now, find the total of all ten squared differences.
If the number that you chose, the one that you
subtracted from each of the ten ages, happens to be the mean of the ten
ages, then the total of the squared differences is minimized, thus the
term least squares. That total is smaller than it would be if you chose any
number other than the mean. This outcome probably seems a strange thing
to care about, but it turns out to be an important characteristic of
many statistical analyses, as you’ll see in later chapters of this book.
Here’s a concrete example. Figure 4 shows the height of each of ten people in cells A2:A11.
You should fill in columns B, C, and D as described later in this section. The cells B2:B11 in Figure 4
will then contain a value—any numeric value—that’s different from the
actual mean of the ten observations in column A. You will see that if
the mean is in column B, the sum of the squared differences in cell D13
is smaller than if any other number is in column B.
To see that, you will need to have made Solver available to Excel.
About Solver
Solver is an addin that comes with Microsoft Excel.
You can install it from the factory disc or from the software that you
downloaded to put Excel on your computer. Solver helps you backtrack to
underlying values when you want them to result in a particular outcome.
For example, suppose you have ten numbers on a
worksheet, and their mean value is 25. You want to know what the tenth
number must be in order for the mean to equal 30 instead of 25. Solver
can do that for you. Normally, you know your inputs and you’re seeking
a result. When you know the result and want to find the necessary values of the inputs, Solver provides one way to do so.
The example in the prior paragraph is trivially
simple, but it illustrates the main purpose of Solver: You specify the
outcome and Solver determines the input values needed to reach the
outcome.
You could use another Excel tool, Goal
Seek, to solve the latter problem. But Solver offers you many more
options than does Goal Seek. For example, using Solver, you can specify
that you want an outcome maximized or minimized, instead of solving for
a particular outcome. That’s relevant here because we want to find a
value that minimizes the sum of the squared differences.
Finding and Installing Solver
It’s possible that Solver is already installed and
available to Excel on your computer. To use Solver in Excel 2007 or
2010, click the Ribbon’s Data tab and find the Analysis group. If you
see Solver there you’re all set. (In Excel 2003 or earlier, check for
Solver in the Tools menu.)
If you don’t find Solver on the Ribbon or the Tools menu, take these steps in Excel 2007 or 2010:
1.  Click the Ribbon’s File tab and choose Options.

2.  Choose AddIns from the Options navigation bar.

3.  At
the bottom of the View and Manage Microsoft Office AddIns window, make
sure that the Manage dropdown is set to Excel AddIns and then click
Go.

4.  The AddIns dialog box appears. If you see Solver Addin listed, fill its check box and click OK.

You should now find Solver in the Analysis group on the Ribbon’s Data tab.
If you’re using Excel 2003 or earlier, start by
choosing AddIns from the Tools menu. Then complete step 4 in the
preceding list.
If you didn’t find Solver in the Analysis group on
the Data tab (or on the Tools menu in earlier Excel versions), and if
you did not see it in the AddIns dialog box in step 4, then Solver was
not installed with Excel. You will have to rerun the installation
routine, and you can usually do so via the Programs item in the Windows
Control Panel.
The sequence varies according to the
operating system you’re running, but you should choose to change
features for Microsoft Office. Expand the Excel option by clicking the
plus sign by its icon and then do the same for Addins. Click the
dropdown by Solver and choose Run from My Computer. Complete the
installation sequence. When it’s through, you should be able to make
the Solver addin available to Excel using the sequence of four steps
provided earlier in this section.