% IncomeDistribution/contents.tex \chapter{\mychaptername} \label{\here} \tocnotetoo{ This chapter covers a lot of ground -- two new kinds of average (median and mode) and ways to understand data when they they come in large quantity rather than just a few at a time: bar charts, histograms, percentiles and the bell curve. To do that we introduce spreadsheets as a tool. }% \teachertag \begin{teacher} We find students come to our course with a wide range of technology experience. They can all manage word processing, web searching and email. Some have used Excel. Those for whom it is new find this chapter hard going. Often pairing experienced and inexperienced students in the lab classroom works well. \end{teacher} \begin{goals} \begin{goal}{excelaverages} Work with mean, median and mode of a dataset. \end{goal} \begin{goal}{normaldistribution} Introduce the normal distribution, with its mean and standard deviation. \end{goal} \begin{goal}{skew} Understand how skewed distributions lead to inequalities among mean, median and mode. \end{goal} \begin{goal}{excelroutine} Make routine calculations in Excel. \end{goal} \begin{goal}{excelwhatif} Use Excel to ask and answer ``what-if'' questions. \end{goal} \begin{goal}{excelchart} Create bar charts and other types of charts in Excel. \end{goal} \begin{goal}{histograms} Use histograms to group and explore data. \end{goal} \begin{goal}{histogramaverages} Calculate averages for grouped data. \end{goal} \begin{goal}{percentiles} Understand what a percentile is and how to interpret percentile information. \end{goal} \end{goals} \begin{chapterpix} There are lots of Excel cartoons out there. We should be able to find one that's suitable and free (or inexpensive enough). \includegraphics[width=60mm]{\here/excel-cartoon.jpg} \url{http://jennyhansenauthor.wordpress.com/2011/06/28/should-writers-use-excel-part-1/} This one's from The New Yorker, probably prohibitive. \includegraphics[width=60mm]{\here/mike-twohy-i-d-like-you-to-excel--new-yorker-cartoon.jpg} \url{http://www.condenaststore.com/-sp/I-d-like-you-to-excel-New-Yorker-Cartoon-Prints_i8478424_.htm} \end{chapterpix} \qrsection[wingaero]{\smallCo{}} Table~\ref{wingaero} shows the distribution of workers' salaries at \smallCo, a small hypothetical company.% \footnote{ In keeping with our \CommonSense\ philosophy we should work with real data. But most companies keep this kind of information private. Any similarity between our imagined \smallCo{} and any real company is purely coincidental. In \exref{usincomedistibution} we'll apply the lessons we learn to look at income distribution in society at large. } The company has about 30 employees. We want to understand the salary distribution. A natural place to begin is with the average. But adding thirty numbers by hand (even with a calculator) is tedious and error-prone. An {\em spreadsheet} computer program can do the arithmetic faster and more accurately. In this text the spreadsheet we use in Microsoft's Excel. We've organized this chapter as a spreadsheet tutorial -- you can follow it step by step in Excel as you read it.% \footnote{Or in your classroom, if it is equipped with computers, or if you have a laptop with you.} See \sref*{usingsoftware} for some general software tips and information about alternatives to Excel. If you're on line you can save typing time by downloading the \smallCo{} spreadsheet from \link{WingAero.xlsx}. If you build it for yourself, use column \cell{A} for the employees and column \cell{B} for the salaries. Put the labels in row \cell{8} and the data in rows \cell{9:38}, not side by side as in the table. You should see Figure~\ref{fig:WA1screenshot}.% \footnote{This is the first of what will be a sequence of screenshots showing what you should see as you work through the chapter. The published version of \commonsense{} will have more, and may point you to on line videos as well.} % side by side tables courtesy of % http://people.csail.mit.edu/jrennie/latex/ \begin{table}[ht] \centering \begin{minipage}{2.2in} \begin{tabular}{|l|c|} \hline Employee & Salary \\ & (thousands of \$) \\ \hline CEO & 299 \\ CTO & 250 \\ CIO & 250 \\ CFO & 290 \\ Manager & 77 \\ Manager & 123 \\ Manager & 84 \\ Manager & 63 \\ Manager & 68 \\ Manager & 49 \\ Manager & 82 \\ Manager & 87 \\ Supervisor & 42 \\ Supervisor & 37 \\ Supervisor & 29 \\ \hline \end{tabular} \end{minipage} \begin{minipage}{2.2in} \begin{tabular}{|l|c|} \hline Employee & Salary \\ & (thousands of \$) \\ \hline Supervisor & 43 \\ Supervisor & 51 \\ Supervisor & 38 \\ Supervisor & 33 \\ Supervisor & 42 \\ Supervisor & 49 \\ Worker & 25 \\ Worker & 19 \\ Worker & 41 \\ Worker & 17 \\ Worker & 26 \\ Worker & 25 \\ Worker & 21 \\ Worker & 28 \\ Worker & 27 \\ \hline \end{tabular} \end{minipage} \caption{\smallCo{} Salary Distribution} \label{wingaero} \tablesource{Made up numbers.} \end{table} \begin{figure}[ht] %\begin{wrapfigure}{l}{2in} \centering \includegraphics[height=75mm]{\here/WA1screenshot} \caption{\smallCo{} spreadsheet (some hidden rows).} \figsource{screen capture from Excel} \label{fig:WA1screenshot} \end{figure} %\end{wrapfigure} What do you notice? The employees are listed in decreasing order of importance (or prestige), but only approximately in decreasing order of salary. Some Supervisors make more than some Managers, and some Workers more than some Supervisors. We can make those discrepancies visible by \emph{sorting} the data. Select the rectangular block of data in rows \cell{9} through \cell{38}, columns \cell{A} and \cell{B}. Be sure to select both columns so they will be sorted together. Choose the \excel{Sort} dialog box from the \excel{Data} teb, select sorting by Salary, \excel{Largest to Smallest}, as in Figure~\ref{fig:WA2screenshot}.% \footnote{This is one way to sort, in Excel 2010. There are others. Later versions may display a different menu, but the ability to sort will be available in any spreadsheet program you use. } Now you can clearly see the anomalies. \begin{figure}[ht] \centering \includegraphics[height=40mm]{\here/WA2screenshot} \caption{Sorting \smallCo{} salaries.} \figsource{screen capture from Excel} \label{fig:WA2screenshot} \end{figure} %\end{wrapfigure} If you sort the data again alphabetically (by Employee, \excel{A to Z}) the table returns (nearly) to its original state, because the employee categories were alphabetical at the start. But each category is now sorted by salary. To find the \emph{average} salary at \smallCo{} we tell Excel to add the entries in column \cell{B} and divide by the number of employees. Enter the label \excel{Total} in cell \cell{A40}. Then go to cell \cell{B40}. Make sure the \excel{Formula Bar} is visible. (Use the \excel{View} menu to find it if it's not.) In the formula box type an equals sign \excel{=}, to tell Excel you want it to do some arithmetic, and then the name of the operation, \displayexcel{ =SUM(} \noindent since you are about to add up some numbers. The open parenthesis \excel{(} asks Excel to prompt you for information. It suggests \displayexcel{ SUM(number1, [number2], $\ldots$) } as in Figure~ref{fig:WA3screenshot}. \begin{figure}[ht] \centering \includegraphics[height=30mm]{\here/WA3screenshot} \caption{Summing \smallCo{} salaries.} \figsource{screen capture from Excel} \label{fig:WA3screenshot} \end{figure} Select cells \cell{B9:B38}, close the parentheses and type \excel{enter} (or click the check icon on the \excel{Formula Bar}. \displayexcel{ Total \ \ 2315 } \noindent in cells \cell{A40} and \cell{B40}. \smallCo's total annual payroll is \$2315K -- about \$2.3 million. To find the average salary we must divide the total \$2315K payroll by the number of employees. Rows \cell{9} through \cell{38} contain employee records so there are $38-9+1=30$ employees. But it's better to ask Excel to count the rows for you. Type the label \excel{ Count} in cell \cell{A41} and begin formula \excel{ =COUNT(} in cell \cell{B41}. Finish the formula by selecting cells \cell{B9:B38} or by typing the addresses of those cells and closing the parentheses. You should see \displayexcel{ Count \ \ 30 } \noindent Finally, type the label \excel{ Average} in cell \cell{A42} and put formula \excel{ =B40/B41} in cell \cell{B42}. You should see \displayexcel{ Average \ \ 77.16667 } \noindent so the average annual salary at \smallCo{} is about \$77,000. Excel rounded the exact $77.1666\ldots$ to $77.16667$ when it ran out of space. We rounded to two significant digits because that's all the precision we have in almost all the data. In \exref{format} you'll learn how to tell Excel to round for you. Excel's built-in functions \excel{SUM} and \excel{COUNT} are separately useful, which is part of why we showed them to you, but if it's the average you want Excel can find it in one step. Enter \excel{=AVERAGE(B9:B38)} in cell \cell{B43}, click the check icon and Excel tells you again that the average is 77.16667. Put ``\excel{computed using SUM/COUNT}'' in cell \cell{C42} and ``\excel{computed using AVERAGE function}'' in cell \cell{C43}. \qrsection[whatif]{What if?}\index{what if} Suppose that the CEO convinced the Board of Directors to double his salary, to \$598K (even though the company lost money). \footnote{Shades of \myindex{Wall Street} bonuses in the wake of the 2009 financial meltdown. } To see how that would affect the payroll statistics, go to cell \cell{B9} and change the 299 there to 598. Excel automatically updates all your computations, increasing the total annual payroll to \$2,614 million and the average annual salary by about \$10,000 to more than \$87,000. We can learn several useful lessons from our work so far. \begin{itemize} \item Excel is faster and more accurate at arithmetic than we are. \item Excel is an excellent tool for answering ``what-if'' questions, because it automatically updates its computations when the data change. \item The average we calculated with \excel{SUM/COUNT} and then again with \excel{AVERAGE} is a terrible way to summarize the salary structure at \smallCo. The CEO's 100\% raise increases the average salary by about \$10,000, or 13\% -- but he's the only employee who's actually better off! \end{itemize} After you've tried out these changes, restore the original values by clicking the \excel{ Undo} icon on the toolbar. This very useful feature allows you to undo the last few changes you've made. Use it to fix mistakes or to get back to where you were before you asked a ``what-if'' question. \qrsection[usingsoftware]{Using software} Why use a spreadsheet at all? There are several reasons. \begin{itemize} \item Carry out large tedious calculations rapidly and correctly. \item Answer ``what-if?'' questions without having to redo arithmetic. \item Draw charts. \item Learn a tool you may use long after you've finished this course. \end{itemize} Here are some tips for working with Excel, and with software packages in general. \begin{itemize} \item To figure out something new, you can use the application's built in help, search the web, ask a friend or teacher, or just play around. Which you try first depends on your personality. \item Many applications provide several ways to do the same task. That means you may get different advice or instructions from different sources. Choose a way that suits your style. \item Use the menus for things you do rarely, but learn the keyboard shortcuts for things you do often -- in particular, \excel{control-C} for copy and \excel{control-V} for paste can save time. \item Learn about \excel{undo}. \textbf{Save your work often}. \item When you are about to make significant changes to a spreadsheet or a document make a copy of what you have, so that you can return to it if you change your mind about what you should have done. In the \smallCo{} study we created several, calling them \verb!WingAero1.xlsx!, \verb!WingAero2.xlsx!, and so on. \item Create backup copies of important documents often -- off your computer. Use a thumb drive (flash drive), a cd, or a web service. \item Some things work the same way in different applications (browsers, Word, Excel) -- for example, selecting with the mouse, cut and paste. \item In many software applications placing the mouse over a feature you are interested in and {\em right clicking} \index{right click} often lets you view and change the {\em properties} of that feature. ``Do the right thing'' is a good mnemonic. \item Applications often try to guess what you intend to do. That can be good or bad. We'll see soon that Excel can adjust cell references automatically -- that's usually, but not always, what you want. Word processors may try to fix your spelling -- perhaps correctly, perhaps not. \end{itemize} Why Excel? It's the most commonly available spreadsheet, so it's the one we use. But there are others available. In particular, Open Office (\url{http://www.openoffice.org/}) offers free spreadsheet and word processing software.\index{Open Office} The spreadsheet program at Google docs is probably also powerful enough to do everything you need for the examples in this book. We have tested our spreadsheets in several such environments. We've written our tutorial in a way that explains both what each step should accomplish as well as which actions to take to make it happen. It should help you even if you use another spreadsheet, or a newer or older version of Excel. \qrsection[median]{Median}\index{median} In \sref*{wingaero} we found that the average salary at \smallCo{} was \$77,000. This is a pretty good annual salary. If you saw that in a job advertisement you'd think it was a pretty good company to work for. Maybe, maybe not. In \sref*{whatif} we saw how it's skewed by the CEO's earnings. When his (or her) salary increased from \$299,000 to \$598,000, the average salary jumped by \$10,000 to \$87,000. But no one else's salary changed! The \$77,000 ``average'' is misleading in other ways too. Most of the employees -- 26 out of 30 -- have salaries less than the average. That contradicts what we like to think ``average'' means. To find a salary that's ``in the middle'', sort the 30 line table again so that salaries are increasing. Since the table starts in row \cell{9} and has 30 entries, rows 23 and 24 are the middle rows and the entries in cells \cell{B23} and \cell{B24} are the middle salaries. That means half the employees make \$42,000 or less (the entry in cell \cell{B23}) and half make \$43,000 or more. So we might want to say that the ``average'' salary is \$42,500. There's a name for this kind of ``average'' -- it's the \emindex{median}. The first ``average'' we computed above is called the \emindex{mean}. On the spreadsheet, change the \excel{ Average} label in cell \cell{A42} to \excel{ Mean}. Then put \displayexcel{ Median \ \ 42.5 \ \ computed by finding middle of sorted list } \noindent into cells \cell{A46}, \cell{B46} and \cell{C46}. In some ways the median is a fairer ``average'' than the mean for describing the \smallCo{} salary structure. It tells you more about the way salaries are distributed. In particular, the median salary isn't affected by the CEO's big raise. Try changing that salary again in Excel: the mean changes, as it did before, but the median stays the same. Excel knows how to compute medians. Enter \excel{ =MEDIAN(B9:B38)} in cell \cell{B47} and check that you get the same value: 42.5. Enter ``\excel{computed using MEDIAN function}'' in \cell{C47}. Finding the median with the \excel{ MEDIAN} function is better than finding the middle of the sorted list yourself because it works even when the data aren't sorted. Suppose the Supervisor making \$43K gets a raise to \$50,000. Enter that new value as \excel{50} in the spreadsheet. Then see that Excel has recalculated the median in cell \cell{B47}: it's now 45.5. There's a third kind of average, the {\em mode}. We'll return to that after we've summarized the salary data in a different way. \qrsection[barcharts]{Bar charts} Often pictures are better than numbers when we wish to convey information convincingly. A \emindex{bar chart} is one such picture. We use bar charts when we have data that falls naturally into categories. The height of each bar represents the data value for that category. When you want general understanding rather than numerical detail it's easier to compare the heights of bars (in both relative and absolute terms) than the values of numbers. In order to understand the \smallCo{} income distribution better we will use Excel to draw two bar charts with four columns each, one showing the number of employees in each of the four job categories Executive, Manager, Supervisor and Worker, the other the total earnings in each category. We will use the original Wing Aero salary data from \sref*{wingaero}. \teachertag \begin{teacher} We strongly recommend drawing these bar charts first by hand on the board, and asking students to do the same from time to time on paper. \end{teacher} \begin{table}[ht] \centering \begin{tabular}{|c|r|r|} \hline Job & Number & Total salary (\$K) \\ \hline Executive & 4 & 1089\\ Manager & 8 & 633 \\ Supervisor & 9 & 364 \\ Worker & 9 & 229 \\ \hline \end{tabular} \caption{\smallCo{} Salary Distribution by Job Category} \tablesource{Made up numbers.} \label{table:wingaerobycategory} \end{table} You can find the data from Table~\ref{table:wingaerobycategory} in the range \cell{D8:F12} in the copy of the \smallCo{} salary distribution spreadsheet at \link{WingAeroBarCharts.xlsx}. Excel calculated the values in columns \cell{E} and \cell{F} using the \excel{ COUNT} and \excel{ SUM} functions. Figure~\ref{fig:showformulas} shows those calculations. We created it by selecting \excel{Show Formulas} from the \excel{Formulas} tab. \begin{figure}[ht] \centering \includegraphics[height=60mm]{\here/showformulas} \caption{Showing the formulas used in a spreadsheet.} \figsource{Image from an Excel spreadsheet we created.} \label{barCharts} \end{figure} Figure~\ref{barCharts} shows the first two pictures from that spreadsheet. \begin{figure}[ht] \centering \includegraphics[height=60mm]{\here/WingAeroBarCharts1cropped} \caption{\smallCo{} Employee Information by Category} \figsource{Image from an Excel spreadsheet we created.} \label{barCharts} \end{figure} These side by side bar charts dramatically demonstrate that the Executives make up a small part of the workforce but enjoy a large part of the salary expenses! Delete the charts from your copy of the spreadsheet, so that you can learn how to build them for yourself. For the first one, select the data in columns \cell{D} and \cell{E}, rows \cell{8} through \cell{12} -- the range \cell{D8:E12}. Then ask for a new chart by selecting \excel{Column} and \excel{2D Column} from the \excel{Insert} tab, as in Figure~\ref{fig:insertchart}. \begin{figure}[ht] \centering \includegraphics[height=60mm]{\here/insertchart} \caption{Inserting a chart in a spreadsheet.} \figsource{Image from an Excel spreadsheet we created.} \label{fig:insertcharts} \end{figure} Use the mouse to move the chart so that it does not hide any of the data. Then add the appropriate labels and change the colors so that the result is suitable for black and white printing. There are several ways to go about these tasks; experiment until you find ones that work for you.% \footnote{ \sref*{usingsoftware} has a tip about how the right mouse button can help with these tasks. } To build the second picture the same way you must select the data in columns \cell{D} and \cell{F} without selecting column \cell{E}. To do that, select rows \cell{8:12} in column \cell{D}. Then hold down the control (PC) or apple (Mac) key and use the mouse to select those rows in column \cell{F}. It would be even better to combine the two pictures. We can do that in Excel by building a column chart for the full range \cell{D8:F12}. The result (after adding titles and fixing colors) is the chart on the left in Figure~\ref{doubleHistograms}. \begin{figure}[ht] \centering \includegraphics[height=60mm]{\here/WingAeroBarCharts2cropped} \caption{\smallCo: Side by Side Bar Charts} \figsource{Image from an Excel spreadsheet we created.} \label{doubleHistograms} \end{figure} The problem with that picture is that the bars for the employee numbers are nearly invisible, because data values for the numbers vary from 4 to 9 while those for the total salaries vary from \$200K to \$1200K. We can fix that by reporting the salary totals in hundreds of thousands of dollars rather than just in thousands of dollars (that is, by changing the units for measuring salary). Column \cell{G} contains those numbers; we used it to draw the second picture. There you see clearly the opposing trends: total wages decrease as the number of employees increases. \teachertag \begin{teacher} Excel can use separate vertical scales to plot two data series. We think it's more useful and more interesting to teach this {\em ad hoc} solution. \end{teacher} \qrsection[piecharts]{Pie charts}\index{pie chart} Excel allows you to change the type of a graph on the fly. Select the column graph showing the total salary by job category. Right click on that chart. Select \excel{Change Chart Type \ldots} and then the first \excel{Pie}. Adjust labels and colors to create the first of the charts in Figure~\ref{pieCharts}. \begin{figure} \centering \includegraphics[height=60mm]{\here/PieChartscropped} \caption{\smallCo{} Salary Distribution Pie Charts} \label{pieCharts} \figsource{Charts from an Excel spreadsheet we wrote.} \end{figure} The pie chart shows clearly in yet another way that the executives are the winners at \smallCo. They take home nearly half the salary total. If you wanted to make that look a little less dramatic, you could omit the percentages and ask Excel to show a three dimensional version of the chart, as in the second picture. There we've rotated the picture so that the Executive wedge is at the back, so it looks even smaller in perspective. \qrsection[histograms]{Histograms}\index{histogram} \smallCo{} is small enough so that we can see the whole salary table at a glance. But if there were 1000 employees that wouldn't be possible. To understand the numbers we'd have to summarize them. The four categories in the previous section might not provide enough detail. Another useful way to summarize the data is to divide the salaries into ranges and count the number of employees whose salary falls in each range. Then we can use the ranges as the categories in a bar chart. In this example we'll use \$20K ranges. That means we think of two employees who make \$29K and \$33K as having approximately the same salary, since each falls in the \$20K-\$39K category. We need to count the number of employees making less than \$20K, then the number making between \$20K and \$39K, and so on. That's easy when the data are sorted in increasing order. Table~\ref{table:wingaeroranges} shows what we found. \begin{table}[ht] \centering \begin{tabular}{|r|r|} \hline salary range (\$K) & number of employees \\ \hline 0-19 & 2 \\ 20-39 & 10 \\ 40-59 & 7 \\ 60-79 & 3 \\ 80-99 & 3 \\ 100-119 & 0 \\ 120-139 & 1 \\ 140-159 & 0 \\ 160-179 & 0 \\ 180-199 & 0 \\ 200-219 & 0 \\ 220-239 & 0 \\ 240-259 & 2 \\ 260-279 & 0 \\ 280-299 & 2 \\ \hline \end{tabular} \caption{\smallCo{} Salary Distribution by Salary Range} \tablesource{Made up numbers.} \label{table:wingaeroranges} \end{table} To save you typing, we've listed the categories in cells \cell{D15:D29} in \link{WingAero.xlsx}. You should check our work and enter the data in column \cell{E}. To see that you haven't missed anyone, \excel{ SUM} the range \cell{E15:E29} to make sure the answer is 30, the known number of employees.% \footnote{ The sum isn't a perfect check. Although the total is correct, we might have put some employees into the wrong categories. } We can use the data to draw a \emindex{histogram} -- a bar chart that provides a visual representation of the data in a table where the first column lists categories each of which specifies a \myindex{data range} and the second the number of items in that range. Figure~\ref{fig:wahistogram} shows the result.% \footnote{% In the bar charts we've studied so far, the $x$-axis represents categories and the $y$-axis data values. In histograms the $x$-axis represents ranges of data values and the $y$-axis counts or percentages for each range. } Start as usual by building a column chart from the data in cells \excel{D14:E29}. In a histogram it's conventional to make adjacent bars touch. To do that in Excel, right click on one of the columns and select \excel{Format Data Series \ldots} from the menu. Then adjust the \excel{Gap Width} using the slider shown in Figure~\ref{fig:FormatHistogram}. While you're there, figure out how to change the colors of the bars, and fix the labels. You can see the full spreadsheet with all the computations we've done so far at \link{WingAeroHistogram.xlsx}. \begin{figure}[ht] \centering \includegraphics[width=9cm]{\here/FormatHistogram} \caption{Formatting a Histogram} \label{fig:FormatHistogram} \figsource{Excel screen capture.} \end{figure} \begin{figure}[ht] \centering \includegraphics[width=75mm]{\here/WingAeroHistogramCropped.pdf} \caption{\smallCo{} Salary Histogram} \label{fig:wahistogram} \figsource{Chart from an Excel spreadsheet we wrote.} \end{figure} It's worth taking some time to study this histogram, which shows how the data are distributed.\index{distribution} Most of the salaries are less than \$100K and there's a large gap in salaries between \$139K and the executives who make more than \$200K. Although this information is all in the table, the histogram makes it visible and dramatic. We chose \$20K for the size of the salary ranges so that we would have enough categories to show what was going on but few enough to make the graph understandable. In \exref{salaryrange} you can explore what happens with different choices. One of the themes of this chapter has been to have Excel to do tedious repetitive calculations. So you might reasonably wonder whether Excel could build Table~\ref{table:wingaeroranges} for the grouped data if we told it the ranges we were interested in. Then it would recompute when we asked ``what-if'' questions. The good news is that Excel can do this job. The bad news is that its histogram building tools are very clumsy.% \footnote{ If you're ambitious, try Excel help or search the internet for \excel{excel histogram} to find out how to build this table in Excel. } We will content ourselves with doing the counting by hand. \qrsection[mmm]{Mean, median, mode} There's a third kind of ``average'' -- the mode -- that's sometimes informative. The \emindex{mode} is the most common value. The histogram from the previous section shows that there are more employees with salaries in the \$20K-\$40K range than any other. So the mode is about \$30K. It's the highest point in the histogram. The mode is most useful for data aggregated into ranges, as in a histogram. In the raw \smallCo{} data there is no well defined mode. Each of the values \$250K, \$49K, \$42K and \$25K appears twice. In Excel the function \excel{ MODE(B9:B38)} reports \$250K when column B is sorted in descending order and \$25K when the column is sorted the other way. Each of ``mean,'' ``median,'' and ``mode'' can legitimately be called an ``average.'' That ambiguity makes it easy to lie with statistics without actually lying. The CEO at \smallCo{} may brag that workers at his company earn an average \$77K per year, while the union argues that the average salary is \$30K per year. A cynic would advise you to use the ``average'' that tells the story your way and hope your listener won't know the difference. When distributions are symmetric, the mean, median and mode are in the same place. The \smallCo{} salary distribution isn't symmetric, it's {\em skewed}\index{skew} to the right. That's the fancy way to say that the bulk of the data cluster toward the left of the histogram with a long tail off to the right. For data that's right skewed, as this is: \begin{center} \begin{tabular}{ccccc} mode & $<$ & median & $<$ & mean \\ 30 & $<$ & 42.5 & $<$ & 77.1 \end{tabular} \end{center} \begin{figure}[ht] \centering \includegraphics[width=100mm]{\here/MeanMedianMode.png} \caption{Symmetric and Skewed Distributions} \figsource{Hand drawn figure.} \figcomment{You can redraw this, or we will. But it should look like something a student could in fact do -- correct, but not professional.} \end{figure} In a histogram the mode is the peak, the median splits the area in half, and the mean is where the graph would balance if it were a cardboard cutout.% \teachertag% \begin{teacher}% This hand drawn figure may seem unprofessional, but in fact we think it's useful. It's more like what a student could produce than a fancy graphic would be. We think we should have more pictures like this. \end{teacher} If you're learning about these several kinds of averages for the first time you may want mnemonics to help you remember which is which. The ``med'' in median suggests correctly that it's in the middle. You can remember mode because there are ``mo' of them than anything else.''% \footnote{Nick Sullivan thought this up in class, November 2, 2010.} \qrsection[averagesfromhistograms]{Computing averages from histograms} Often all we know about data is a summary like that presented in a histogram. We'll see here how to estimate the mode, median and mean from that information. We will use the \smallCo{} histogram in Figure~\ref{fig:wahistogram} as an example. Since we know the correct averages we can see how good our estimates are.% \teachertag \begin{teacher} We find this a particularly valuable section -- it forces students to come to grips with the real meanings of each kind of average. Just memorizing definitions suffices for small data sets, but with grouped data the mean is a weighted average and real understanding is required. We recommend thinking about mode, median and mean in that order. \end{teacher} We've already found the mode. It's the highest bar in the histogram -- the range with the largest entry: \$20,000 -- \$39,000. Note carefully -- the mode is the range, not the height of the bar. We could report that range, or report the mode as the middle of the range: about \$30,000. The raw data do not have a mode that makes sense, so there's nothing to compare this estimate to. To estimate the median salary from the histogram we need to find the salary such that half the employees make less and half more. The first bar tells us that 2 make less than \$19K. Adding the number of employees in the first two ranges tells us that $2+10=12$ make less than \$39K. Looking at the next range we see that $2+10+7 = 12 + 7 = 19$ make less than \$59K. Since there are 30 employees, it's the $15^{th}$ and $16^{th}$ whose salaries are closest to the median. They are clearly pretty much in the middle of the third category, so we can estimate the median as the midpoint of that category, say \$50,000. The correct value (from the raw data) is \$42,500. Since there are just eight ranges with data and the median occurred in the third one, we didn't need Excel to do the arithmetic. In a more complicated example things might not be so easy, so let's see how to make Excel to the work. Label column \cell{H} in cell \cell{H15} as ``\excel{count so far}''. Then copy the value from \cell{E15} to \cell{H15} by typing \excel{=E15} in H15. Then enter \excel{=H15+E16} in \cell{H16}, to add the number of employees counted so far (in \cell{H15}) to the number in this range {\cell{E16}). Now select that formula and copy it to \cell{H17:H29}. A \myindex{miracle} has happened! Cell \cell{H29} contains the value 30. If you click on that cell you can see that it's the result of the formula \excel{ =H28+E29}. Excel read your mind, and automatically updated the row references to cells in columns \cell{H} and \cell{E} with each copy down the column. It {\em knew} you wanted to add the value in the cell above to the value in the cell three over to the left. \teachertag \begin{teacher} You will need to take time here to explain both the miracle -- Excel guessed that updating references was what was wanted -- and its value in saving time and typing. It takes getting used to. \end{teacher} The last entry should be 30 since all the employees make less than \$299K. Now it's easy to see that the count so far passes the midpoint of 15 in the middle of the third range. Estimating the mean salary is the hardest. Since the only \smallCo{} data we have is what we used to draw the histogram we can't expect to find it exactly. All we can say about the 10 employees in the \$20K\ --\ \$39K range is that they earn somewhere in the neighborhood of \$30K. So our best guess is to assume they all earn exactly that. If we make a similar assumption for each of the other ranges then our estimate for the mean is the \emindex{weighted average} \begin{equation*} \frac{ 2 \times \$10K + 10 \times \$30K + \cdots + 2 \times \$290K } {\mbox{total number of employees}} . \end{equation*} That's too much arithmetic to do by hand so we'll use Excel. Put the label ``\excel{mid range}'' in cell \cell{F14}. We want to use cells \cell{F15:F29} to hold the values 10, 30, $\ldots$, 290 that are the middles of the ranges in cells \cell{D15:D29}. There's a quick trick for that. Enter the 10 in cell \cell{F15}. Then go to cell \cell{F16} and enter the {\em formula} \displayexcel{ =F15+20 } \noindent Excel will display \excel{ 30} there. That's because it reads the formula as \begin{center} add 20 to the contents of cell \cell{F15} \end{center} Now we want to add 20 each time you move down a row. To do that, copy the formula in \cell{F16} and paste it into cells \cell{F17:F29}. (This takes advantage again of Excel's correct guess about what we are trying to do.) Next label column \cell{G} by typing ``\excel{range total}'' in cell \cell{G14}. Then put the formula \displayexcel{ =E15*F15 } in cell \cell{G15}. That asks Excel to multiply the numbers in cells \cell{E15} and \cell{F15}. You should see 20. That's the first number to add in the weighted average computation we're working on. When you copy that formula to cells \cell{G16:G29} you should see 580 at the end of the list. That's the miracle yet again. To compute the weighted average you need to sum the values in column \cell{G}. Since cell \cell{E30} contains the sum of the values in column \cell{E}, all you need do is copy the formula from that cell to cell \cell{G30}. Excel will automatically change the column reference, turning the formula \excel{ =SUM(E15:E29)} into \excel{ =SUM(G15:G29)}. The sum is 2360. To find the mean, enter the formula \excel{=G30/E30} in cell \cell{G31}. Excel shows you 78.66667. Label that value as the mean. Our estimate of the mean salary from the histogram is \$79,000. We shouldn't report more precision than that, since we made many approximations along the way. Do note that the estimate is not very far from the true mean of \$77,167 computed from the raw data. \qrsection[percentiles]{Percentiles} On June 7, 2011 we visited the website \url{http://www.infantchart.com/}. There we collected the data displayed in Table~\ref{table:babyweights}. Reading down the first column, you can see that a one year old male baby weighing 21.5 pounds would be in the 30.9th \emindex{percentile}. That means that he would weigh more than 30.9 percent of the babies, less than 69.1 percent. A baby weighing 22.5 pounds would be in the 46.5th percentile, so $46.5\% - 30.9\% = 15.6\%$ of the year old male babies weigh between 21.5 and 22.5 pounds. \begin{table}[ht] \centering \begin{tabular}{|r|r|r|} \hline weight (pounds) & percentile & difference \\ \hline 15.5 & 0.1 & 0.1\\ 16.5 & 0.2 & 0.1\\ 17.5 & 0.8 & 0.6\\ 18.5 & 3 & 2.2\\ 19.5 & 8.2 & 5.2\\ 20.5 & 17.5 & 9.3\\ 21.5 & 30.9 & 13.4\\ 22.5 & 46.5 & 15.6\\ 23.5 & 61.8 & 15.3\\ 24.5 & 74.9 & 13.1\\ 25.5 & 84.8 & 9.9\\ 26.5 & 91.4 & 6.6\\ 27.5 & 95.4 & 4.0\\ 28.5 & 97.7 & 2.3\\ 29.5 & 98.9 & 1.2\\ 30.5 & 99.5 & 0.6\\ 31.5 & 99.8 & 0.3\\ 32.5 & 99.9 & 0.1\\ 33.5 & 100 & 0.1\\ \hline \end{tabular} \caption{Year Old Male Baby Weights} \tablesource{Calculator at \url{http://www.infantchart.com/}} \tablecomment{Permission needed?} \label{table:babyweights} \end{table} To find the mode, we should look for the largest {\em difference} in percentiles. Since 15.6\% of the babies weigh between 21.5 and 22.5 pounds the mode is about 22 pounds. With guess-and-check on the website we found that a weight of 22.75 pounds was as close as we could get to the 50th percentile, so that's the median. Some other commonly used percentiles are the \begin{itemize*} \item tenth: any baby weighing less than about 19.2 pounds is in the tenth percentile, \item ninetieth: about 90\% of the babies weigh less than about 26 and a half pounds, \item the \myindex{quartiles} -- the 25th 50th and 75th percentiles. The 50th percentile is, of course, the median. \end{itemize*} Using the techniques from \sref*{averagesfromhistograms} we computed the mean as a weighted average. It is about 22.9 pounds. Therefore \begin{center} \begin{tabular}{ccccc} mode & $<$ & median & $<$ & mean \\ 22 & $<$ & 22.75 & $<$ & 22.9 \end{tabular} \end{center} That suggests that the distribution is just little bit skewed to the right. \qrsection[bellcurve]{The bell curve} The baby weight data in Table~\ref{table:babyweights} are just what we need to construct the histogram in Figure~\ref{fig:babyweights}. There you can see the mode (the highest bar) and you can see the little bit of right skew. \begin{figure}[ht] \centering %\includegraphics[height=70mm]{\here/babyweights.png} \includegraphics[height=70mm]{\here/babyweightscropped.pdf} \caption{Baby weights} \figsource{Chart is from an Excel spreadsheet we wrote. Data is from the calculator at \url{http://www.infantchart.com/}} \figcomment{Permission needed?} \label{fig:babyweights} \begin{center} Data source: \url{http://www.infantchart.com/} \end{center} \end{figure} Many factors contribute to a baby's weight at one year: birth weight, heredity, nutrition, \ldots. Whenever many small effects combine to give a total the distribution of values forms a \emindex{bell curve}. The one shown in the figure is a mathematically correct bell curve that approximates the real data. The mathematically correct name for this phenomenon is \emindex{normal distribution}. A normal distribution is always symmetrical. Its mean, median and mode are in the same place. To construct the one in the figure we need one more number besides the mean -- a measure of how fast the curve spreads out. That measure is called the \emindex{standard deviation}. It's usually written with the Greek letter sigma: $\sigma$; the mean is usually written with the Greek letter mu: $\mu$. For the baby weight data the standard deviation is about 2.6 pounds. There's a nice way to use the standard deviation to describe how a bell curve speads out. \begin{itemize*} \item 2/3 of the values are less than one standard deviation away from the mean, \item 95\% of the values are less than two standard deviations away, \item 99.7\% are less than three standard deviations away. \end{itemize*} Figure~\ref{fig:normalwithsigmas}% \footnote{From \url{http://en.wikipedia.org/wiki/File:Standard_deviation_diagram.svg} licensed under the Creative Commons Attribution 2.5 Generic license. } illustratates these percentages. Table~\ref{table:normalcurve} summarizes them in terms of percentiles. \begin{table}[ht] \centering \begin{tabular}{|c|r|} \hline value & percentile \\ \hline $\mu - 3\sigma$ & 0.1\\ $\mu - 2\sigma$ & 2.3\\ $\mu - \sigma$ & 15.9 \\ $\mu$ & 50.0 \\ $\mu + \sigma$ & 84.1 \\ $\mu + 2\sigma$ & 97.7\\ $\mu + 3\sigma$ & 99.9\\ \hline \end{tabular} \caption{Percentiles for the Normal Curve, mean $\mu$, standard deviation $\sigma$} \tablesource{Public information} \label{table:normalcurve} \end{table} \begin{figure}[ht] \centering \includegraphics[height=60mm]{\here/normalwithsigmascropped.pdf} \caption{How the normal distribution spreads out} \figsource{ \url{http://en.wikipedia.org/wiki/File:Standard_deviation_diagram.svg} licensed under the Creative Commons Attribution 2.5 Generic license. } \label{fig:normalwithsigmas} \end{figure} Since the baby body weight distribution is very close to normal, with mean $\mu$ about 22.9 pounds and standard deviation $\sigma$ about 2.6 pounds, we know about 2/3 of the babies weigh between $\mu - \sigma = 22.9 - 2.6 = 20.3$ and $\mu + \sigma = 22.9 + 2.6 = 25.5 $ pounds. Approximately 95\% weigh between 17.7 and 28.1 pounds. 2.5\% weigh more than 28.1 pounds and 2.5\% weigh less than 17.1 pounds. Figure~\ref{fig:threenormals} shows three bell curves with the same mean $\mu = 22.9$, but three different standard deviations, $\sigma = 1.3, 2.6$ and $5.2$. The middle one matches the baby weight data. \begin{figure}[ht] \centering \includegraphics[height=70mm]{\here/threenormalscropped.pdf} \caption{Three bell curves} \figsource{Chart from an Excel spreadsheet we wrote.} \label{fig:threenormals} \end{figure} The mathematics needed to calculate standard deviations and draw bell curves is more than we will present here. You will learn about it if you take an introductory course in statistics. All you should remember now is the rough relationship between standard deviation and spread we sketched above and that the official name for the bell curve is \emindex{normal distribution}. If you want to see how we asked Excel to calculate the standard deviation for the baby weights and draw Figures~\ref{fig:babyweights} and \ref{fig:threenormals}, you can look at spreadsheet \link{babyweights.xlsx}. %http://www.cdc.gov/growthcharts/data/set1/chart02.pdf %http://www.exceluser.com/explore/normalcurve.htm %http://www.vertex42.com/ExcelArticles/mc/NormalDistribution-Excel.html \qrsection[marginoferror]{Margin of error} On July 17, 2012 The Chicago Tribune ran a story under the headline \headline{Poll finds support for Obama's tax position}. It said (in part) \begin{qwrap} \begin{quotation} \firstline{A new survey by the nonpartisan Pew Research Center found that} 44\% said the tax increase would help the economy, 22\% said it would be harmful, and 24\% said the tax hike would not make a difference. \ldots The poll was conducted July 12-15 among 1,015 adults and has a margin of sampling error of plus or minus 3.6 percentage points. \end{quotation} \sourceinfo[363]{http://www.chicagotribune.com/news/politics/sns-la-pn-poll-finds-support-for-obamas-tax-position-20120716,0,12147.story, but the Tribune says they got the story from the LA Times.} \end{qwrap} The first paragraph quoted above reports the results of a survey. The second tells you something about how reliable those results are. It's clear that the {\em smaller} the \emindex{margin of error} the {\em more} you can trust the results. Understanding the margin of error quantitatively -- seeing what the number actually means -- is much more complicated. A statistics course would cover that carefully; we can't here. But since the term occurs so frequently, it's worth learning the beginning of the story. Even that is a little hard to understand, so pay close attention. \teachertag{} \begin{teacher} This is one of those places where it's hard to find the right compromise between appropriate simplifications and actual untruths about what the concepts mean and the numbers say. It might well take a whole class period to expand on the discussion here. You have to decide whether that's how you want to spend class time. \end{teacher} The survey was conducted in order to discover the number of people who believe the tax increase would benefit the economy. If everyone in the country offered their opinion then we would know that number exactly. If we gave the survey to just three or four people we could hardly conclude anything. The people at the Pew Research Center decided to survey the opinions of a {\em sample} of the population -- 1,015 people chosen at random. Of the particular people surveyed, $0.44 \times 1,015 = 447$ people believed that the tax increase would benefit the economy. If they'd surveyed a different group of 1,015 people, they would \myindex{probably} see a different number, so a different percent. The 3.6 percentage point \index{percentage points} margin of error says is that if they carried out the survey many times with different samples of 1,015 people, 95\% of those surveys would find a percent that was within 3.6 percentage points of the true value.% \footnote{ The paragraph doesn't explicitly mention 95\%. That's just built into the mathematical formula that computes the margin of error from the sample size. } There's no way to know whether this particular sample is one of the 95\%, or one of the others. If you read about surveys in 100 news articles, about five of them are likely to be bad ones where the margin of error surrounding the answer doesn't include the true value. Survey designers can reduce the margin of error by asking more people (increasing the sample size). But there will always be five surveys in every hundred that don't capture the true value. Even that conclusion may be too optimistic. The margin of error computation only works if the sample is chosen in a fair way, so that everyone was equally likely to be included. If they asked 1,015 people at random from an area where most people were Democrats (or Republicans) or rich (or poor) the result would be even less reliable. \qrsection[bimodal]{Bimodal data} The black data points in Figure~\ref{fig:lymphomadata.png} show how the rate of diagnosis (in cases per 100,000 people) of \myindex{Hodgkin lymphoma} (a kind of cancer) for white females depends on the age of the woman diagnosed.% \footnote{ Data source: Surveillance, Epidemiology, and End Results (SEER) Program (\url{www.seer.cancer.gov}) SEER*Stat Database: Incidence - SEER 9 Regs Limited-Use, Nov 2008 Sub (1973-2006) , National Cancer Institute, DCCPS, Surveillance Research Program, Cancer Statistics Branch, released April 2009, based on the November 2008 submission. Graphic drawn by Ben Bolker.\index{Bolker, Ben} } There are two peaks, one at about 20 years, the other at 75 years. There is no single value that can legitimately be called the mode -- this is a \emindex{bimodal distribution}. The mean and the median would each be about 45 years, but they make no sense at all. What is \myindex{probably} happening is that the disease has two very different causes, one of which occurs more often in young people, the other in old people. \begin{figure}[ht] \centering \includegraphics[scale=0.75]{\here/cancer-whitefemale.pdf} \caption{Hodgkin lymphoma incidence (white females)} \figsource{ Data source: Surveillance, Epidemiology, and End Results (SEER) Program (\url{www.seer.cancer.gov}) SEER*Stat Database: Incidence - SEER 9 Regs Limited-Use, Nov 2008 Sub (1973-2006) , National Cancer Institute, DCCPS, Surveillance Research Program, Cancer Statistics Branch, released April 2009, based on the November 2008 submission. Graphic drawn by Ben Bolker.} \label{fig:lymphomadata.png} \end{figure} We can understand this distribution as a combination of two normal distributions. The pink bell curve for the early onset of Hodgkin lymphoma has a mean of about 24 years with a standard deviation of 11 years. The blue bell curve for late onset has a mean of about 79 years with a standard deviation of 19 years -- it spreads out more slowly. The red curve is the sum of the two normal distributions. It matches the data very well. \exstart Excel is just a {\em tool}. It doesn't answer questions, it provides numbers and pictures {\em you} use to answer questions. So keep on writing complete sentences explaining the meaning and context of the numbers you report. The numbers in cells in a spreadsheet are no more use by themselves than the numbers in a calculator display. If an exercise asks for hard copy of a spreadsheet, format it well. Use \excel{Print Preview} to make sure that charts fit on one page and don't cover important data. Label the data columns, cells containing important computations, axes and legends in charts. Numbers are useless when they can't be understood. Use complete sentences to answer questions. \begin{exx}{\hassolution\routine\sref{wingaero}} \myindex{CXO} What do ``CEO'', ``CTO'', ``CIO'' and ``CFO'' stand for in the \smallCo{} salary table in \sref*{wingaero}? \begin{sol} In CXO the C and the O stand for Chief and Officer. The middle letter stands for Executive, Technology, Information (or sometimes Investment) and Financial. Sometimes you see COO (Operating). ``CIO'' also stands for ``Congress of Industrial Organizations,'' a federation of labor unions, founded in 1935. \end{sol} \end{exx} \begin{dummyexx}{\needsquestions\sref{whatif}} What if\ldots \end{dummyexx} %\begin{exx} %http://www.ultimate-photo-tips.com/what-is-a-histogram.html %\end{exx} \begin{exx}{\routine\sref{usingsoftware}\gref{excelroutine}} Formatting in Excel Format the cells containing averages in the \smallCo{} spreadsheet so that the number displayed for the various averages are rounded to the nearest thousand dollars (no decimal places). \end{exx} \begin{exx}[format]{\untested\sref{usingsoftware}\gref{excelroutine}} Formatting Have Excel display the mean \smallCo{} salary with just two significant digits, as \excel{77}. \end{exx} \begin{dummyexx}{\needsquestions\sref{median}} Median \ldots \end{dummyexx} \begin{exx}{\hassolution\sref{barcharts}\gref{excelchart}\gref{excelaverages}} Averaging averages \begin{abcd} \item Find the average (mean) salary at at \smallCo{} for each of the four categories of employees (use the original data set, from \sref*{wingaero}). \item Show the data in a properly labelled bar chart. \item Compute the weighted average of these averages to check that you get the correct mean for the whole payroll. \end{abcd} \begin{hint} You may draw your bar chart neatly by hand, or use Excel. \end{hint} \begin{sol} \begin{abcd} \item Find the average (mean) salary at at \smallCo{} for each of the four categories of employees. The following table shows the average salaries by category. I built the spreadsheet at \slink{WingAeroAverageByCategorySolution.xlsx} to find these numbers. \begin{center} \begin{tabular}{|c|r|r|r|} \hline Job & Number & Total salary (\$K) & Average salary (\$K) \\ \hline Executive & 4 & 1089 & 272.25\\ Manager & 8 & 633 & 79.13\\ Supervisor & 9 & 364 & 40.44 \\ Worker & 9 & 229 & 25.44 \\ \hline \end{tabular} \end{center} \item Show the data in a properly labelled bar chart. See Figure~\ref{fig:WAaverages}. \begin{figure}[ht] \centering \includegraphics[height=60mm]{\here/WingAeroAverageByCategorySolutioncropped.pdf} \caption{WingAero average salaries, by category} \label{fig:WAaverages} \figsource{Excel spreadsheet} \end{figure} \item Compute the weighted average of these averages to check that you get the correct mean for the whole payroll. The weighted average is \begin{equation*} \frac{ 4 \times 272.25 + 8 \times 79.13 + 9 \times 40.44 + 9 \times 25.44 } {30} = 77.61. \end{equation*} That's the same value as the ordinary average of the original data. \end{abcd} \end{sol} \end{exx} \begin{exx}{\hassolution\sref{barcharts}\gref{excelaverages} \gref{excelchart}} \headline{Cash-strapped T proposes 23 percent fare increase} \index{MBTA} %The graphic in Figure~\ref{fig:mbtaFareIncreases} appeared in %\theGlobe{} on March 29, 2012. % % %\begin{figure}[ht] %\centering %\includegraphics[height=60mm]{\here/mbtaFareIncreases.jpg} %\caption{MBTA Fare Increases} %\figsource{\theGlobe, March 27, 2012, http://bostonglobe.com/metro/2012/03/28/mbta-unveils-percent-fare-hike-limited-service-cuts-also-proposed/moCl42rwr0Nf5xyx20ZQGP/story.html} %\label{fig:mbtaFareIncreases} %\end{figure} % On March 29, 2012 the Massachusetts Bay Transportation Authority (MBTA) provided the data in Table~\ref{table:mbtaFareIncreases}. \webref{http://bostonglobe.com/metro/2012/03/28/mbta-unveils-percent-fare-hike-limited-service-cuts-also-proposed/moCl42rwr0Nf5xyx20ZQGP/story.html} \begin{table} \centering \begin{tabular}{|rrr|} \hline Fare Category & Current & Proposed \\ \hline Charlie Card & & \\ \hline Bus & \$1.25 & \$1.50 \\ Subway & \$1.70 & \$2.00 \\ Senior Bus & \$0.40 & \$0.75 \\ Senior Subway & \$0.60 & \$1.00 \\ Student Bus & \$0.60 & \$0.75 \\ Student Subway & \$0.85 & \$1.00 \\ \hline Charlie Ticket & & \\ \hline Bus & \$1.50 & \$2.00 \\ Subway & \$2.00 & \$2.50 \\ \hline \end{tabular} \caption{MBTA Fare Increases} \label{table:mbtaFareIncreases} \end{table} \begin{abcd} \item What are the relative and absolute changes in the Charlie Card bus fare? You don't {\em have} to do this in your head, without a calculator, but you should be able to. \item Create a spreadsheet for this data, with eight rows (one for each of the eight categories) and three columns, for the category name, the existing fare and the proposed fare. Create a properly labeled bar graph to display the data. \item Label the next two columns appropriately to hold the relative and absolute changes. Fill those columns with Excel {\em formulas} to compute the correct values. Do not compute the values elsewhere and enter them in the spreadsheet as numbers. \item Imagine that you are addressing a public meeting about these fare increases. How would you argue that an unfair burden is being placed on people who pay using a Charlie Ticket? How would you argue that senior citizens are most hard hit? \item Find the mean of the relative percent increases. Explain why the answer is {\em not} the 23 percent quoted in the headline. \item What extra information would you need to check that the correct mean is 23 percent? \item (Optional) Create a column for the percentage of MBTA revenue for each of the categories and fill in some values that sum to 100\% and give a weighted average fare increase of about 23\%. \teachertag \teachernote{ There's an important and subtle distinction here between weights as a percentage of revenue and weights as a percentage of trips. Worth a discussion in class if you have time and a good enough class. } \end{abcd} \begin{sol} \begin{abcd} \item What are the relative and absolute changes in the Charlie Card bus fare? The absolute change is \$0.25; the relative change is 20\%. I did them in my head. \item Create a spreadsheet for this data, with eight rows (one for each of the eight categories) and three columns, for the category name, the existing fare and the proposed fare. Create a properly labeled bar graph to display the data. My spreadsheet with its chart is at \slink{MBTAFareIncreasesSolution.xlsx}. \item Label the next two columns appropriately to hold the relative and absolute changes. Fill those columns with Excel {\em formulas} to compute the correct values. Do not compute the values elsewhere and enter them in the spreadsheet as numbers. \item In my solution the absolute and relative changes are in columns \cell{E} and \cell{F}. I put these formulas in cells \cell{E7:F7}: \displayexcel{ =D7-C7 \ \ \ \ \ \ =D7/C7 } I put the percent increase in cell \cell{G7} with the formula \displayexcel{ =F7 -1 } and formatted the result as a percent. Then I copied these formulas to the rest of the rows. \item Imagine that you are addressing a public meeting about these fare increases. How would you argue that an unfair burden is being placed on people who pay using a Charlie Ticket? How would you argue that senior citizens are most hard hit? The Charlie Ticket riders have the largest absolute change among all the categories: 50 cents. That's not fair. The senior citizens have the largest two relative changes among all the categories: 67\% for subway fares and a whopping 88\% for the bus. That's not fair. \item Find the mean of the relative percent increases. Explain why the answer is {\em not} the 23 percent quoted in the headline. According to Excel, the mean of the relative percent increases is 37\%. The correct mean is a {\em weighted} average of the percentages in each category, with weights the percentage of revenue in each category. \item What extra information would you need to check that the correct mean is 23 percent? To check that the Globe reported the correct mean I would need to know the percentage of rides in each category, or the percent of revenue for each category. \item (Optional) Create a column for the percentage of MBTA revenue for each of that categories and fill in some values that sum to 100\% and give a weighted average fare increase of about 23\%. They are in the spreadsheet. \end{abcd} \end{sol} \end{exx} \begin{exx}{\untested\sref{piecharts}\gref{excelchart}} Handcuffs? You can find Figure~\ref{recidivism} at \url{http://people.howstuffworks.com/prison4.htm}. It claims to provide information about the recidivism rate of released prisoners. \index{recidivism}\index{handcuffs} \begin{figure}[ht] \centering \includegraphics[width=80mm]{\here/prison.jpg} \caption{Recidivism Rates} \figsource{\url{http://people.howstuffworks.com/prison4.htm}} \figcomment{Needs permission.} \label{recidivism} \end{figure} \begin{abcd*} \item This looks like a pie chart. Is it? Explain. \item What is wrong or misleading in this graphic? \end{abcd*} \begin{sol} \begin{abcd*} \item This looks like a pie chart. Is it? Explain. The answer to the question is ``probably not''. It would be a pie chart if it were really round (it's not quite) and if the size of each sector represented the proportion of offenses of each particular kind. That might be true, but there is no way to tell. The percentages in the wedges are not the percentages of offenses of that kind, they are the recidivism rates for released prisoners who committed those particular kinds of offense. For example, 73.8\% of those released for a property offense committed another one. \item What is wrong or misleading in this graphic? I suspect that the sizes of the wedges are proportional to the percentages in them (which is why the one for property offenses seems to be the largest). In fact the sectors are all more or less the same size, with recidivism rates that don't differ a lot. So not much information is being conveyed. The handcuff frame says that the average rate was 67.5\%. That number should be a weighted average of the percentages in the five sectors. Since we're not told how big the sectors are, I can't check that figure. \end{abcd*} \end{sol} \end{exx} \begin{exx}{\untested\sref{piecharts}\gref{excelchart}} Why not pie charts? Do some internet research to discover why bar charts are usually better than pie charts. Write the reasons in your own words (don't just cut and paste). Identify the sources of your information and comment on why you think those sources are reliable. \end{exx} \begin{exx}{\artificial\untested\sref{piecharts}\gref{excelchart}} Misleading pie charts. Table~\ref{table:baddata} shows some student enrollment data at a college. It's clearly incomplete: Juniors and Seniors are missing. \begin{table}[ht] \centering \begin{tabular}{|c|r|} \hline Class & Percentage \\ \hline Freshman & 40\\ Sophomore & 25 \\ \hline \end{tabular} \caption{Freshman and Sophomore Enrollments} \tablesource{Made up numbers.} \label{table:baddata} \end{table} Flashy pie charts are common in the media, but staid and boring bar charts are usually more informative and less deceiving. Because Excel makes it so easy to switch to a pie from a bar, designers may be tempted by the glitz. You should not succumb to that temptation. Here's an exercise where you can find out why. \begin{abcd} \item Construct a bar chart displaying this data, with columns for Freshman and Sophomore enrollments. \item Change the Chart Type in Excel. \item Explain what is wrong with the chart Excel built for you. \end{abcd} \end{exx} \begin{exx}{\untested\complex\sref{histograms} \gref{excelchart}\gref{histograms}} River lengths At \url{http://en.wikipedia.org/wiki/List_of_rivers_by_length} Wikipedia offers a chart of 163 major rivers, organized by length. We've put the data there in the spreadsheet \link{rivers.xlsx} so you can play with it. \teachertag \begin{teacher} We haven't assigned this Benford's law exercise yet. We think it would be very interesting, but perhaps not worth the time it takes away from other more useful topics. \end{teacher} \begin{abcd} \item Construct a bar chart with nine categories, using the first digit of the length of the river to determine the category. You might expect all the bars to be the same height, since there are nine possible starting digits. The fact that they are not is an instance of {\em Benford's Law}. You can look up more about it if you're curious.\index{Benford's law} \item Explain why this bar chart is not a histogram. \item Convert the lengths to feet from miles, and redraw the bar chart. \end{abcd} \end{exx} \begin{exx}{\hassolution\worthy\sref{mmm}\gref{excelwhatif} \gref{excelaverages}} What if? Add five Workers each earning \$18K to the original \smallCo{} payroll by inserting some rows in the table. Comment on what happens to the mean, median and modal incomes. Note that Excel automatically recomputes these averages, but not the charts for which you created data by hand. When you've done this exercise, undo your changes and check that the three kinds of averages revert to their old values. \begin{sol} When I add five Workers earning \$18K each the mean falls from \$77.16K to \$66.71K, the median from \$42.5K to \$41K. The mode is now \$18K. (The value of the mode before the new workers was ambiguous. What Excel says it is depends on the order in which the employees are listed. In general you should report modes only for grouped data.) \end{sol} \end{exx} \begin{exx}{\hassolution\sref{mmm}\gref{skew}\gref{histograms}} Population pyramids. At the website \url{http://www.census.gov/ipc/www/idb/informationGateway.php} you can choose a country and a year, then ask for a kind of bar chart known as a \emindex{population pyramid}. You can also download that data used to build that chart. \begin{abcd} \item Construct population pyramids for the United States and for Sudan for the year 2010. \item Estimate the number of people in the United States in 2010 between 0 and 9 years old. Do the same for Sudan. What fractions of the populations do these numbers represent? \item Find the modal age range for the United States population. Do the same for Sudan. \item Find the age range for the United States that has the smallest population. Do the same for Sudan. \item Compare the population distributions of the United States and Sudan. Write several sentences that highlight aspects of each distribution that you think are quantitatively significant. Use the results of the previous part of the problem. %\item Careful reading shows that values for the %years beyond 1999 are estimates. You're told to visit %\url{http://factfinder.census.gov/} for up to date information. Do %that, and comment on something interesting you find there. \end{abcd} \begin{sol} \begin{abcd} \item Construct population pyramids for the United States and for Sudan for the year 2010. See Figures~\ref{fig:uspopulationpyramid} and \ref{fig:sudanpopulationpyramid}. \begin{figure}[ht] \centering \includegraphics[height=72mm]{\here/USPopulationPyramid.png} \caption{US Population} \label{fig:uspopulationpyramid} \end{figure} \begin{figure}[ht] \centering \includegraphics[height=72mm]{\here/SudanPopulationPyramid.png} \caption{Sudan Population Pyramid} \label{fig:sudanpopulationpyramid} \end{figure} \item Estimate the number of people in the United States in 2010 between 0 and 9 years old. Do the same for Sudan. What fractions of the populations do these numbers represent? I did Sudan first. To find that number I need to add the bottom two bars on each side, for the boys and girls age 0-4 and 5-9. That looks to be about $3.6 + 3.2 + 3.5 + 3.0 = 13.1$ million. Then I downloaded the underlying data tables as an Excel spreadsheet and checked my estimate. The number of 0-9 year olds in Sudan is $7,018,343 + 6,128,436$ which is indeed about 13.1 million. The total Sudan population is 43.9 million (from the data table). The fraction of children is thus $13.1/43.9 = 0.298405467 \approx 30\%$ The corresponding figures for the United States are about $11 + 11 + 10 + 10 = 42$ million children. That's about $42/310 \approx 13.5\%$. \item Find the modal age range for the United States population. Do the same for Sudan. The modal age is the largest bar. For the United States that's 45-49 years. For Sudan it's 0-4 years! \item Find the age range for the United States that has the smallest population. Do the same for Sudan. In each population the smallest age range is 100+. That's not surprising, and also not very interesting. \item Compare the population distributions of the United States and Sudan. Write several sentences that highlight aspects of each distribution that you think are quantitatively significant. Use the results of the previous part of the problem. The population pyramid for Sudan really looks like a pyramid. The number of people in each five year cohort decreases steadily with age. That indicates a very large birth rate, with lots of people dying young. It also spells disaster for the society -- there aren't lots of adults to take care of the children. The United States population pyramid looks more like a house: vertical walls with a slanted roof on top. That suggests that the population structure is relatively stable, with few deaths at young ages. It also looks likely that the little bulge at 45-55 years will lead to a large number of senior citizens in not too long. That's a social problem of a different kind. \end{abcd} \end{sol} \end{exx} \begin{exx}{\hassolution\sref{mmm}\gref{excelaverages}} Lake Wobegon \begin{abcd} \item Look back at the \smallCo{} data and answer the following questions: \begin{itemize*} \item What percentage of the employees make more than the mean salary? \item What percentage of the employees make more than the median salary? \item What percentage of the employees make more than the mode salary? \end{itemize*} \item In his radio show {\em A Prairie Home Companion} \index{Prairie Home Companion} host Garrison Keillor regularly tells his audience about \myindex{Lake Wobegon}, where ``all the children are above average.'' Is that possible, with any of the meanings of ``average''? \end{abcd} \begin{sol} \begin{abcd} \item Look back at the \smallCo{} data and answer the following questions: \begin{itemize*} \item What percentage of the employees make more than the mean salary? 8 of the 30 employees make more than the mean of \$77K. That's $8/30 = 0.266 = 27\%$. \item What percentage of the employees make more than the median salary? 50\%! That's the {\em meaning} of ``median'' so it's the answer to this question even if I don't know the value of the median. \item What percentage of the employees make more than the mode salary? This is a slightly subtle question. The mode only makes sense for grouped data. The figure in the text shows that the mode for \smallCo{} is about \$30K. 21 of the 30 employees, or 70\%, make more than that. \end{itemize*} \item In his radio show {\em A Prairie Home Companion} \index{Prairie Home Companion} host Garrison Keillor regularly tells his audience about \myindex{Lake Wobegon}, where ``all the children are above average.'' Is that possible, with any of the meanings of ``average''? No -- that's what makes Keillor's description funny. For the median, exactly half are above average. For the mean and the mode it's possible for (a lot) more than half to be above average, but never ``all''. \end{abcd} \end{sol} \end{exx} \begin{exx}{\hassolution\complex\sref{mmm}\gref{excelaverages}} \headline{Wal-Mart will pay \$40m to workers}\index{Wal-Mart} On December 3, 2009 \theGlobe{} ran a story under that headline that said (in part): \begin{qwrap} \begin{quotation} \firstline{Wal-Mart Stores Inc., the world's largest retailer, has} agreed to pay \$40 million to as many as 87,500 current and former employees in Massachusetts, the largest wage-and-hour class-action settlement in the state's history. The class-action lawsuit, filed in 2001, accused the retailer of denying workers rest and meal breaks, refusing to pay overtime, and manipulating time cards to lower employees' pay. Under terms of the agreement, which was filed in Middlesex Superior Court yesterday by the employees' attorneys, any person who worked for Wal-Mart between August 1995 and the settlement date will receive a payment of between \$400 and \$2,500, depending on the number of years worked, with the average worker receiving a check for \$734.% \webref{% http://www.boston.com/business/articles/2009/12/03/wal_mart_will_pay_40m_to_workers/ } \end{quotation} \sourceinfo{http://www.boston.com/business/articles/2009/12/03/wal_mart_will_pay_40m_to_workers/} \sourcewc{852} \end{qwrap} Bloomberg news carried more information about the settlement: \begin{qwrap} \begin{quotation} \firstline{The Massachusetts workers' lawyers asked the court to award} them \$15.2 million in attorneys' fees, to be paid out of the \$40 million settlement. They're also seeking reimbursement of expenses. \webref{ http://www.bloomberg.com/apps/news?pid=newsarchive&sid=a2AClc9J8WwE } \end{quotation} \sourceinfo{ http://www.bloomberg.com/apps/news?pid=newsarchive&sid=a2AClc9J8WwE } \sourcewc{754} \end{qwrap} \begin{abcd} \item Compute the mean compensation, assuming that there are 87,500 eligible employees and that the lawyers have taken their cut. \item Compare your answer to the reported minumum compensation of \$400 and the reported \$734 the average worker will receive. \item Draft a letter to the editor or the reporter, politely pointing out that both the reported ``averages'' made no sense, and asking for more detail or a correction. \item It is too late to send a letter to the editor, but may not be too late to comment on line. Look through the many reader comments on this article at \url{http://www.boston.com/business/articles/2009/12/03/wal_mart_will_pay_40m_to_workers/?comments=all}. See if any mention the problem with the averages. If none does, consider posting a comment of your own. \end{abcd} \begin{sol} \begin{abcd} \item Compute the mean compensation, assuming that there are 87,500 eligible employees and that the lawyers have taken their cut. \begin{equation*} \frac{\$40\hbox{M} - \$15.2\hbox{M} }{87,500 \hbox{workers}} = 283 \frac{\$}{\hbox{worker}}. \end{equation*} \item Compare your answer to the reported minumum compensation of \$400 and the reported \$734 the average worker will receive. The \$283{\em mean} compensation can't be less than the \$400 {\em minimum} compensation. Something is crazy here. The \$734 that the ``average worker'' gets is even crazier. It's much too large to be the median. There's no way half the workers can get more than \$734 if the minumum is \$734 unless the mean is more than halfway from \$400 to \$734. That's not true even before I take out the lawyers' \$15.2 million. \item Waiting for a student to provide a good answer. \end{abcd} \end{sol} \end{exx} \begin{exx}{\routine\artificial\hassolution\sref{mmm}\gref{excelaverages}} Is it \myindex{discrimination}? Table~\ref{salaryStructure} shows the salary structure of two departments in a hypothetical university. \begin{table}[ht] \centering \begin{tabular}{|r|r|r|r|r|} \hline & \multicolumn{2}{|c|}{Physics} & \multicolumn{2}{|c|}{English} \\ \hline & professors & salary & professors & salary \\ Women & 1 & \$100K & 8 & \$50K \\ Men & 9 & \$90K & 2 & \$40K \\ \hline \end{tabular} \caption{Salary Structure at a University} \tablesource{Made up data.} \label{salaryStructure} \end{table} \begin{abcd} \item What is the average (mean) salary of the professors? of the women professors? Of the men? \item Answer the same questions for the median. \item Answer the same questions for the mode. \item Write a few sentences to convince someone that men in this university are paid better than women. Then write a few sentences to convince someone of just the opposite. Explain the contradiction. \end{abcd} \begin{sol} \begin{abcd} \item What is the average (mean) salary of the professors? of the women professors? Of the men? The mean professor salary (in thousands of dollars) is \begin{equation*} \frac{1 \times \$100 + 9 \times \$90 + 8 \times \$50 + 2 \times \$40}{20} = \$69.5. \end{equation*} The mean for the women is \begin{equation*} \frac{1 \times \$100 + 8 \times \$50} {9} = \$61.1. \end{equation*} The mean for the men is \begin{equation*} \frac{9 \times \$90 + 2 \times \$40} {11} = \$80.9. \end{equation*} \item Answer the same questions for the median. The median salaries for all, women and men are \$70K (halfway between \$90K and \$50K), \$50K and \$90K respectively. \item Answer the same questions for the mode. The mode salaries for all, women and men are \$90K, \$50K and \$90K respectively. \item Write a few sentences to convince someone that men in this university are paid better than women. Then write a few sentences to convince someone of just the opposite. Explain the contradiction. Obviously the men are paid better than the women. Each of the three averages is higher for men than women! Obviously the women are better paid than them men: all the women do better than all the men in both departments. This seeming contradiction is possible because the women outnumber the men in the English (poorer) department, while the opposite is true in the Physics (richer) department. There the lone woman makes the highest salary -- perhaps because the Dean (a woman) insisted that an all male Physics department would discourage women students. \end{abcd} \end{sol} \end{exx} \begin{exx}[salaryrange]{\sref{averagesfromhistograms} \gref{histogramaverages}\gref{histograms}\hassolution} Choosing data ranges for a histogram. When you change the widths of the intervals in a histogram you get a (slightly) different picture of the data. \teachertag \begin{teacher} This exercise may be worth assigning for the Excel practice, and for reinforcing the computations of various averages from summarized data. But the conclusions aren't very striking. \end{teacher} \begin{abcd} \item Redo the \smallCo{} histograms in the text using salary ranges of size \$10K and then of size \$50K. \suspend{abcd} \begin{hint} Start with a copy of \link{WingAeroHistogram.xlsx}. Create new data in columns \cell{D} and \cell{E} below the existing data there for the two new histograms. Fill in column \cell{F} appropriately. Then you can copy and paste from what's there to fill in columns \cell{G} and \cell{H} and the sums and the mean. If Excel wants to treat your salary ranges as dates, try formatting the cells as \excel{text}. To make the charts, copy the histogram that's there, then find the place in Excel where you can change the source data to the new rows in columns \cell{D} and \cell{E}. \end{hint} \resume{abcd} \item In each case use the techniques from Section~\ref{sec:averagesfromhistograms} to estimate the mean, median and mode. \item Discuss the advantages and disadvantages of these possible choices for the salary range, comparing them to our choice of \$20K. \end{abcd} \begin{sol} \begin{abcd} \item Redo the \smallCo{} histograms in the text using salary ranges of size \$10K and then of size \$50K. See \slink{WingAeroHistogramRangesSolution.xlsx} for the new histograms. \item In each case use the techniques from Section~\ref{sec:averagesfromhistograms} to estimate the mean, median and mode. Here's a table showing all three averages, computed from the data directly and from the three histograms. \begin{center} \begin{tabular}{|r|r|r|r|} \hline Data & Mean & Median & Mode \\ \hline complete & \$77.2K & \$42.5K & nonsense \\ \$20K intervals & \$79K & \$48K & \$20K-\$39K \\ \$10K intervals & \$77K & \$45K & \$20K-\$29K \\ \$50K intervals & \$73K & \$45K & \$0K-\$50K \\ \hline \end{tabular} \end{center} The smaller the data ranges in the histograms the closer the approximated averages are to the true values. \item Discuss the advantages and disadvantages of these possible choices for the salary range, comparing them to our choice of \$20K. None of these histograms seems particularly ``better'' than the other two. Each gives a good visual picture of how \smallCo{} salaries are distributed. The various averages are close enough to the correct values so that they are not misleading. \end{abcd} \end{sol} \end{exx} \begin{exx}{\hassolution\sref{averagesfromhistograms}\gref{histogramaverages} \gref{histograms}\hassolution} Web sites are often confusing. Jakob Nielsen evaluated the usability of voter information websites for the 2008 election for each of the fifty states and the District of Columbia. You can read his analysis at \url{http://www.useit.com}. Here is some of what he discovered. \begin{qwrap} \begin{quotation} \firstline{The following histogram of homepage quality across the 51} websites shows [that] \ldots [some] sites have completely miserable homepages, whereas others are close to achieving all of the current best practices. \webref{% http://www.useit.com/alertbox/quality-correlations.html } \end{quotation} \sourceinfo{http://www.useit.com/alertbox/quality-correlations.html} \end{qwrap} His article includes the histogram in Figure~\ref{fig:nielsenhistogram} \begin{figure}[ht] \centering \includegraphics[width=4in]{\here/histogram-homepage-quality-voting-sites.png} \caption{Web Site Usability} \figsource{url{http://www.useit.com/alertbox/quality-correlations.html}} \figcomment{Permission needed.} \label{fig:nielsenhistogram} \end{figure} \begin{abcd*} \item What is the modal usability score for these 51 home pages? \item Reproduce this histogram in Excel. \suspend{abcd*} \begin{hint} When you enter the data in two columns in Excel put the categories (usability scores) on the left since they are the labels for the x\-axis. Put the numbers of websites on the right since they are the values that go with the categories, and should plot vertically, on the y\-axis. If your Excel is anything like ours, it may well think that you want to display both columns on the y\-axis, since both columns are numbers. If it does that it will label the x\-axis with the numbers 1 to 10. If that happens, delete the data series corresponding to the bars you don't want (the percentages). Then right click on the chart and explore until you find the place that allows you to enter the fields you want to use as x\-axis category labels. \end{hint} \resume{abcd*} \item Estimate the median usability score for these 51 home pages. \item How many of these home pages have a usability score less than the the median score? \item Estimate the mean usability score for these 51 home pages. \end{abcd*} \begin{sol} \begin{abcd} \item What is the modal usability score for these 51 home pages? The 20\% bar is the tallest, so that's the modal score. \item Reproduce this histogram in Excel. See Figure~\ref{fig:nielsenhistogramreproduced}. The spreadsheet is at \slink{HomePageQualitySolution.xlsx}. \begin{figure}[ht] \centering \includegraphics[width=4in]{\here/HomePageQualitySolutioncropped.pdf} \caption{Web Site Usability} \label{fig:nielsenhistogramreproduced} \end{figure} Excel ended the vertical scale at 14, but the scale in the histogram in the book ends at 16, so I changed the scale here. I matched the color as best I could. \item Estimate the mean usability score for these 51 home pages. It's not clear whether the scores are always exactly 10\%, 20\%, etc., or whether the 20\% category really means ``between 10\% and 20\%''. I {\em think} the first interpretation is right -- it looks as if there are 10 usability tests and you either succeed or fail in each one. If that's how the score was computed, no web site passed all ten tests and four sites failed them all. With that interpretation the mean usability score is the \myindex{weighted average} \begin{equation*} \frac{4 \times 0 + 4 \times 10 + 12 \times 20 + 6 \times 30 + 3 \times 40 + 7 \times 50 + 5 \times 60 + 4 \times 70 + 3 \times 80 + 3 \times 90} {51} = 0.396078431 \end{equation*} which is about 40\%. I did the computation in Excel, since I had already entered the numbers to build the histogram. Table~\ref{table:formulas} shows the formulas I used, starting at cell \cell{A:16}'' \begin{table} \begin{center} \begin{tabular}{|r|r|r|} \hline Usability Score & Number of websites & total contribution \\ \hline 0 & 4 & =A17*B17 \\ \hline =A17+0.1 & 4 & =A18*B18 \\ \hline =A18+0.1 & 12 & =A19*B19 \\ \hline =A19+0.1 & 6 & =A20*B20 \\ \hline =A20+0.1 & 3 & =A21*B21 \\ \hline =A21+0.1 & 7 & =A22*B22 \\ \hline =A22+0.1 & 5 & =A23*B23 \\ \hline =A23+0.1 & 4 & =A24*B24 \\ \hline =A24+0.1 & 3 & =A25*B25 \\ \hline =A25+0.1 & 3 & =A26*B26 \\ \hline =A26+0.1 & 0 & =A27*B27 \\ \hline sums & =SUM(B17:B27) & =SUM(C17:C27) \\ \hline mean & & =C28/B28 \\ \hline \end{tabular} \caption{Weighted average of usability scores} \label{table:formulas} \end{center} \end{table} With the second interpretation, I will count the web pages with scores between, say, 10\% and 20\% as if they all had scores of 15\%. Then the mean usability score is the weighted average \begin{equation*} \frac{4 \times 5 + 4 \times 15 + 12 \times 25 + 6 \times 35 + 3 \times 45 + 7 \times 55 + 5 \times 65 + 4 \times 75 + 3 \times 85 + 3 \times 95} {51} = 0.446078431 \end{equation*} which is about 45\%. It's not an accident that this is exactly 5 percentage points higher. Do you see why? To see the magic of Excel, just change the \excel{0\%} to \excel{5\%} in \cell{A17} and watch all the computed values change. \item Estimate the median usability score for these 51 home pages. I will add up the numbers in each category starting from the left, to see when I reach half the websites -- that would be 25 or 26. The sum of the first three categories is $4 + 4 + 12 = 20$; the sum of the first four is $4 + 4 + 12 + 6 = 26$. So the 25th site in the fourth category. That means about half the websites have usability scores of 30\% or less while the other half have scores 40\% or more. Since I have to report a single number for the median, I'll say it's 35\%. \item How many of these home pages have a usability score less than the the median score? Half the home pages have a score less than the median. That will be true even if I made a mistake computing the median! \end{abcd} \end{sol} \end{exx} \begin{exx}{\hassolution\sref{averagesfromhistograms} \gref{histogramaverages}\gref{histograms}} College costs.\index{college costs} The College Board (\url{http://www.collegeboard.org/} published the chart in Table~\ref{table:collegecostincrease} with data about increases in tuition and fees from 2009-2010 to 2010-2011 in public four year colleges and universities. The right-hand column gives the percentage of colleges and universities that increased tuition and fees by the dollar range in the left-hand column.% \footnote{You can find this data in \link{CollegCostIncrease.xlsx}.} \begin{table}[ht] \centering \begin{tabular}{|r|r|} \hline Dollar Increase & Percent of Colleges \\ \hline Under \$200 & 10.8\% \\ \$200 to \$399 & 26.8\% \\ \$400 to \$599 & 23.4\% \\ \$600 to \$799 & 15.4\% \\ \$800 to \$999 & 7.9\% \\ \$1,000 to \$1,199 & 5.0\% \\ \$1,200 to \$1,399 & 2.5\% \\ \$1,400 to \$1,599 & 1.4\% \\ \$1,600 to \$1,799 & 2.4\% \\ \$1,800 to \$1,999 & 0.8\% \\ \$2,000 to \$2199 & 3.6\% \\ \hline \end{tabular} \caption{College Cost Increase} \tablesource{\url{http://www.collegeboard.org/}} \tablecomment{Permission needed?} \label{table:collegecostincrease} \end{table} \begin{abcd} \item What is the mode increase in tuition and fees? \item What is the median increase in tuition and fees? \item Use Excel to calculate the average (mean) increase in tuition and fees. \item Construct a histogram displaying this data. \end{abcd} \begin{sol} \begin{abcd} \item What is the mode increase in tuition and fees? The mode is the highest bar: the range \$200 to \$399. If you need one number for the mode, use \$300. \item What is the median increase in tuition and fees? By computing a running total of the percentages in each range I discovered that the 50\% threshold is about halfway between the ranges \$200 to \$399 and \$400 to \$599, so the median increase is about \$400. \item Use Excel to calculate the average (mean) increase in tuition and fees. I computed a weighted average of the middles of the ranges, using the percentages for weights, to find out that the mean increase is about \$630. The spreadsheet at \link{CollegeCostIncreaseSolution.xlsx} shows the formulas I entered. \item Construct a histogram displaying this data. \begin{center} \includegraphics[width=80mm]{\here/CollegeCostIncreaseSolutioncropped.pdf} \end{center} \end{abcd} \end{sol} \end{exx} \begin{exx}[usincomedistibution]{\hassolution\complex\worthy \sref{averagesfromhistograms}\gref{histogramaverages}\gref{histograms}} Household income in the United States The histogram in Figure~\ref{householdIncome} \footnote{ From \url{http://en.wikipedia.org/wiki/Household_income_in_the_United_States}) } shows the percentage of the population in income groups \$10,000 increments apart, except for the furthest two right columns which correspond to increments of \$50,000. The data are from 2005. \begin{figure}[ht] \centering \includegraphics[width=130mm]{\here/householdIncome.png} \caption{United States Household Income -- 2005} \figsource{\url{http://en.wikipedia.org/wiki/Household_income_in_the_United_States}} \figcomment{What permission is needed to reproduce from Wikipedia? It would be easy to redraw this -- we have, in Excel.} \label{householdIncome} \end{figure} \begin{abcd} \item Enter the data in an Excel spreadsheet. \item Build a histogram in Excel that comes as close as possible to matching the one in wikipedia. Display it two ways, with and without the phony three dimensional effect. \index{phony 3D} \item Do the percentages sum to 100\%? If not, what might explain the discrepancy? \item Estimate mean, median and modal household income. Then discuss how your answers compare to the corresponding figures in wikipedia. \end{abcd} You can find the raw data at the US Census 2006 Economic Survey (\url{http://pubdb3.census.gov/macro/032007/hhinc/new06_000.htm}) but you should not need it to work this exercise. \begin{sol} \begin{abcd} \item Enter the data in an Excel spreadsheet. \item Build a histogram in Excel that comes as close as possible to matching the one in wikipedia. Display it two ways, with and without the phony three dimensional effect. \index{phony 3D} See \link{USIncomeDistributionSolution.xlsx}. \item Do the percentages sum to 100\%? If not, what might explain the discrepancy? The percentages sum to just over 97\%. I think the missing 3\% are the households whose income is {\em more than} \$200K -- the really well-off. \item Estimate mean, median and modal household income. Then discuss how your answers compare to the corresponding figures in wikipedia. The modal household income is \$10K-\$20K since that's the highest bar in the histogram. Adding up the percentages of households starting at the left, you pass 50\% just about halfway through the \$40K-\$50K category. So I estimate the median household income at about \$45K. The spreadsheet I made shows that that the mean household income based on this data is about \$54K. It's the weighted average of the incomes in each category. I remembered to divide by 97.13, the total of the percentages. If I estimate that the other three percent of households make an average of \$500K my estimate of the mean increases to \$67K. Wikipedia reports the census bureau assertion that the median household income in 2007 was \$50,233.00. The data in the chart are from 2005, which may explain the discrepancy. \end{abcd} \end{sol} \end{exx} \begin{exx}{\sref{averagesfromhistograms}\gref{skew}\untested} Are the data in the problem on household unemployment in \exref{hightower} consistent with the income distribution in the previous problem? \end{exx} \begin{exx}{\hassolution\sref{averagesfromhistograms} \gref{histogramaverages}\gref{histograms}} Fight for the Senate Figure~\ref{fig:senate} appeared in Nate Silver's Five Thirty Eight column in \theTimes{} on October 31. The $x$ axis displays the number of seats held by each party: the Tie in the middle is 50 Democrats, 50 Republicans. The +10 Dem corresponds to 55 Democrats, 45 Republicans. \begin{figure}[ht] \centering \includegraphics[height=1.7in]{\here/Oct31SenateProjectionFull} \caption{The Fight for the Senate} \label{fig:senate} \figsource{\url{http://fivethirtyeight.blogs.nytimes.com/2012/10/31/oct-30-what-state-polls-suggest-about-the-national-popular-vote/}} \figcomment{This particular graphic is probably no longer available.} \end{figure} Nate Silver constructed this histogram by imagining (simulating) many thousands of elections and recording the percentage of time each Democratic/Republican split occurred. We estimated the percentages in the chart and entered them in spreadsheet \link{Oct31SenateProjection.xlsx} so you don't have to type them yourself. (We rounded the really tiny percentages to zero.) Use Excel whenever it's most convenient for you. \begin{abcd} \item What is the most likely number of Democratic Senators? \item What number of Democratic Senators represents the mode of this distribution? \item What is the probability that there are more than 50 Democratic Senators? \item What number of Democratic Senators is the median of this distribution? \item If you had the complete list of all Nate Silver's imagined elections and sorted it by the number of Democratic Senators, how many Democratic Senators would there be in the middle election on that list? \item Use Excel to compute the (weighted) average number of Democratic Senators for these imagined elections. \item Recreate the chart in Excel, with proper labels. You need not match the format exactly, but it might be fun to try -- at home, after the exam, perhaps for Tuesday. \end{abcd} \begin{sol} \begin{abcd} \item What is the most likely number of Democratic Senators? 53 -- the highest bar. \item What number of Democratic Senators represents the mode of this distribution? 53 -- the highest bar. \item What is the probability that there are more than 50 Democratic Senators? 85.6\% -- it's \excel{=SUM(C14:C21)} in the spreadsheet. \item What number of Democratic Senators is the median of this distribution? The cumulative sum passes 50\% at 53 Democrats. So the median is 53. \item If you had the complete list of all Nate Silver's imagined elections and sorted it by the number of Democratic Senators, how many Democratic Senators would there be in the middle election on that list? There would be 53 Democrats. This is just the median again. \item Use Excel to compute the (weighted) average number of Democratic Senators for these imagined elections. The weighted average is 52.6 Democratic senators. \item Recreate the chart in Excel, with proper labels. You need not match the format exactly, but it might be fun to try. See \link{Oct31SenateProjectionSolution.xlsx}. \end{abcd} \end{sol} \end{exx} \begin{exx}{\hassolution\sref{averagesfromhistograms}\gref{excelaverages} \gref{histogramaverages}} Data from the census The Excel spreadsheet \link{FAM50.xlsx} contains information extracted from the March 2006 Current Population Survey. Scroll across to see the headings. There is an explanation of the headings in the Data Dictionary at \link{Fam50DataDictionary.pdf}. \begin{abcd} \item Look at the 6th individual in the survey. (Which row in the spreadsheet?) Write a paragraph that describes everything you know about this person. \item Explain why there might be different values for Personal Total earnings, Personal Total Income, and Family Income. \item Use Excel to find the mean and median for family income for the 50 individuals in the table. \item Sort the data so that family incomes increase as you read down the columns. Create a column chart for family income, with one column for each family in the sample. What do you notice? Does it seem strange? Is the distrbution consistent with the shape of the histogram in Figure~\ref{householdIncome}? \item Which of the two ``averages'' is most representative of the data set? \end{abcd} \begin{sol} \begin{abcd} \item Look at the 6th individual in the survey. (Which row in the spreadsheet?) Write a paragraph that describes everything you know about this person. Row 9 in the spreadsheet. It's an error to say ``not in universe'' - what that means is that occupation is not one of the listed categories. \item Explain why there might be different values for Personal Total earnings, Personal Total Income, and Family Income. Too easy \ldots \item Use Excel to find the mean and median for family income for the 50 individuals in the table. \begin{verbatim} Median $78,509 Mean $90,832 \end{verbatim} \item Sort the data so that family incomes increase as you read down the columns. Create a column chart for family income, with one column for each family in the sample. What do you notice? Does it seem strange? Is the distrbution consistent with the shape of the histogram in Figure~\ref{householdIncome}? \item Sort the data by family income and create a scatterplot for family income. What do you notice? Does it seem strange? \begin{figure}[ht] \centering \includegraphics[width=2.5in]{\here/FamilyIncomeScatterplotcropped.pdf} \caption{Family Income Scatterplot} \figsource{Chart from an Excel spreadsheet we built from census data.} \label{fig:weirdplot} \end{figure} Figure~\ref{fig:weirdplot} is really weird. All the values but the last one are on a straight line! If I made a histogram of this data the number of families in each income range would be about the same, except for that last family. I cannot believe that this is a random sample of census records. I wonder who selected these particular 50 families, how, and why. I noticed something else interesting: I can estimate the mean and median values for the first 49 families right from the chart. Both are just about \$80K, half the largest value. Adding one more family doesn't change the median, but it does change the mean, adding to it about $1/50^{th}$ of the \$500K outlier, giving a value of about \$90K. These figures agree with the Excel calculations (of course). \item Which of the two ``averages'' is most representative of the data set? The median is a better single statistical measure. The mean is much higher because the distribution is skewed by the single high outlier. \end{abcd} \end{sol} \end{exx} \begin{exx}{\hassolution}{\worthy\sref{averagesfromhistograms} \gref{excelaverages}\gref{histogramaverages}} What cities pay for fire protection. \index{firefighters' pay} On Monday, March 30 2009 \theGlobe{} published an article comparing the amount various cities spent on Fire and EMS services.% \footnote{ You can read the article at \url{http://www.boston.com/news/local/massachusetts/articles/2009/03/30/boston\_spends\_most\_on\_firefighters\_in\_us/}.} Figure~\ref{fig:FireSpending} is a screenshot of a spreadsheet where we entered some of the data, along with population figures. You can download that spreadsheet from \link{FireSpending.xlsx}. Use the data to answer these questions. %That article included Figure~\ref{firepay} (not available on the internet). % %\begin{figure}[ht] %\centering %\includegraphics[width=80mm]{\here/FireSpending.jpg} %\caption{Fire Protection Spending} %\figsource{Scanned from \theGlobe, 3/30/2009.} %\label{firepay} %\end{figure} \begin{figure}[ht] \centering \framebox{ \includegraphics[width=80mm]{\here/FireSpendingcropped.pdf} } \caption{Fire Protection Spending} \label{fig:FireSpending} \figsource{Excel screen capture, data from \theGlobe, 3/30/2009.} \end{figure} \begin{abcd} \item Population \begin{abcd} \item What is the {\em mean} population of the twelve cities for which data are presented. \item What is the {\em median} population of the twelve cities for which data are presented. \item Create Table~\ref{cityPopulations} in Excel. Fill in the second column there. Then create a properly labelled histogram for this data. \begin{table}[ht] \centering \begin{tabular}{|r|r|} \hline population range & number of cities\\ \hline 500K-600K & \\ 600K-700K & \\ 700K-800K & \\ 800K-900K & \\ 900K-1000K & \\ 1000K-2000K & \\ 2000K-3000K & \\ 3000K-4000K& \\ $>$ 4000K & \\ \hline \end{tabular} \caption{City Populations} \tablecomment{No data in this table.} \label{cityPopulations} \end{table} \item Use your histogram to estimate the mode population for these cities. \item What percent of the US population lives in these twelve cities? \end{abcd} \item Fire/EMS spending per person \begin{abcd} \item What is the {\em mean} amount spent for Fire/EMS services per person in these twelve cities? \suspend{abcd} \begin{hint} This mean is {\em not} the \excel{AVERAGE} of the amounts spent per person by each city. It's wrong to average those numbers since they are already averages. You must weight them by the city populations in order to compute the total amount spent by all the people in all the cities. Then divide by the total population. You can't compute the median with Excel's \excel{MEDIAN} function for the same reason. Further hint: almost everyone lives in New York. \end{hint} \resume{abcd} \item Estimate the {\em median} amount spent for Fire/EMS services per person by residents of these twelve cities. \item Estimate the {\em mode} amount spent for Fire/EMS services per person by residents of these twelve cities. \end{abcd} \item What do firemen earn? There is enough information in the spreadsheet to calculate the average (mean) earnings of Fire/EMS personnel in each of the twelve cities. Do that, in a fresh column in your spreadsheet. \begin{abcd} \item In which city do Fire/EMS personnel have the highest average salary? How much is it? \item In which city do Fire/EMS personnel have the lowest average salary? How much is it? \item Where does Boston rank in the list of Fire/EMS personnel salaries? \item Explain how Boston can be at the top of the list in Fire/EMS expenses per resident although it does not pay the highest salaries. \end{abcd} \begin{hint} You can't answer the first question by finding the mean of the twelve numbers in column C. Compute the mean correctly as a weighted average. You will probably\index{probably} want to start by creating a column labeled \begin{verbatim} total Fire/EMS expenses \end{verbatim} \noindent and fill in the value for each city. \end{hint} \item Correction the next day! On Tuesday \theGlobe{} published a correction, which read in part: \begin{qwrap} \begin{quotation} Boston spent \$285 per resident on its fire department during the last fiscal year, according to the revised report. \webref{% http://www.boston.com/news/local/massachusetts/articles/2009/03/31/error\_made\_in\_fire\_dept\_report/} \end{quotation} \sourceinfo{http://www.boston.com/news/local/massachusetts/articles/2009/03/31/error\_made\_in\_fire\_dept\_report/} \end{qwrap} Look at the answers to the questions above and indicate which have changed (and how), which stayed the same. \end{abcd} \begin{sol} You can find the work in the spreadsheet at \slink{FireSpendingSolution.xlsx}. \begin{abcd} \item Population \begin{abcd} \item A straightforward use of \excel{ =AVERAGE(B7:B18)} in Excel tells me that the mean population is 1,661,071. The last few digits really don't mean much since it's unlikely that the populations listed for the cities are really accurate down to the last person. A better answer is about 1,660,000. \item A straightforward use of \excel{ =MEDIAN(B7:B18)} in Excel tells me that the median population is 773,469. That's halfway between the populations of San Francisco and Columbus. It's much too precise. A better answer is that the median is about 770,000. \item Here is the table: \begin{center} \begin{tabular}{|r|r|} \hline population range & number of cities\\ \hline 500K-600K & 2 \\ 600K-700K & 3 \\ 700K-800K & 2 \\ 800K-900K & 1 \\ 900K-1000K & 1 \\ 1000K-2000K & 1 \\ 2000K-3000K & 0 \\ 3000K-4000K& 1 \\ $>$ 4000K & 1 \\ \hline \end{tabular} \end{center} See the histogram in the spreadsheet. Note the labels for the axes and the title, that the bars touch one another, and that the vertical scale does not list halves of cities. \item The highest bar in the histogram corresponds to the largest number in the table: the mode population is 600K-700K. \item The \excel{SUM} function in Excel tells me that the total population of these twelve cities is 19,932,848. That represents $19,932,848/300,000,000 \approx 7\%$ of the people in the United States \end{abcd} \item Fire/EMS spending per person \begin{abcd} \item Column C reports the average (mean) spending in each of the twelve cities. To find the overall average I can't just average these averages, since the cities are different sizes. I need to use those as the weights. So I created column E to hold \begin{verbatim} total Fire/EMS expenses \end{verbatim} \noindent and filled each row with the product of the values in columns B and C: the number of residents times the spending per resident. Then I summed column E to find out that these cities spent a total of \$3,707,134,657 (almost four billion dollars!) to provide Fire/EMS services for their total population of 19,932,848 people. Dividing, I found that on average they spent \begin{equation*} \frac{\$3,707,134,657}{19,932,848 \hbox{\ people}} \approx 186 \frac{ \$ }{\hbox{person}} \end{equation*} \item New York accounts for more than 40\% of the 20,000 people in these cities. If you add in Los Angeles then you get more than half. Since those two are the two cities at the bottom of the list, the median Fire/EMS cost per resident is somewhere between the values for those cities - probably\index{probably} about \$150. \item The most common amount is what the New Yorkers pay, so the mode is \$157.56. \end{abcd} \item What do firemen earn? There is enough information in the spreadsheet to calculate the average (mean) earnings of Fire/EMS personnel in each of the twelve cities. Do that, in a fresh column in your spreadsheet. I computed that two ways, just to make sure I was right. The first way uses the contents of columns C and D to fill column H this way: \excel{ =1000*Cn/Dn} for each of the rows. That works because the units for column C are \$ per resident while for column D they are firemen per 1000 residents. When I divide I get \$ per fireman, which is just what I want. The second way is to compute the number of firemen in each city -- fill column H with \excel{ =B7*D7/1000}. Then divide the total fire/EMS expenses (already computed in column E) by that number and put the result in column I. Columns H and I match. \begin{abcd} \item Firemen in Los Angeles have the highest average salary: \$153,111. \item Firemen in Baltimore have the lowest average salary: \$83,696. \item Boston ranks fourth from the top, at \$132,985. \item Boston spends more per resident on Fire/EMS personnel than other cities even though it pays them less because it has a lot more of them in proportion to population -- it's at the top of that list. \end{abcd} \item Correction the next day! When I change the Boston Fire/EMS expense per resident from \$452.15 to \$285 the following figures change: \begin{itemize} \item The mean Fire/EMS expense becomes \$181/resident, down from \$186 per resident. \item The mean Fire/EMS salary becomes \$107,600, down from \$110,635. \item The average Boston Fire/EMS salary falls from \$132,985 to \$83,824 -- second lowest, only about \$100 above the minimum. \end{itemize} \end{abcd} \end{sol} \end{exx} \begin{exx}{\untested\sref{averagesfromhistograms}\gref{excelaverages} \gref{histogramaverages}} When Does Old Age Begin? On June 29, 2009 \theGlobe{} reported that \begin{qwrap} \begin{quotation} \firstline{Americans differ on when old age begins. On average, they} say 68. People under age 30 believe it begins at 60, while those 65 and older push the threshold to 74. Of all those surveyed, most said they wanted to live to 89. \webref{% http://www.boston.com/news/nation/articles/2009/06/29/generation_gap_in_us_largest_since_1960s_study_reveals/} \footnote{ The survey that provided statistics is at \url{% http://pewresearch.org/pubs/1269/aging-survey-expectations-versus-reality?src=prc-latest&proj=peoplepress } } \end{quotation} \sourceinfo[792] {http://www.boston.com/news/nation/articles/2009/06/29/generation_gap_in_us_largest_since_1960s_study_reveals/} \end{qwrap} The article goes on to ask \begin{qwrap} \begin{quotation} When Does Old Age Begin? At 68. That's the average of all answers from the 2,969 survey respondents. But as noted above, this average masks a wide, age-driven variance in responses. More than half of adults under age 30 say the average person becomes old even before turning 60. Just 6\% of adults who are age 65 or older agree. \end{quotation} \sourceinfo[792]{see previous quotation - http://www.boston.com/news/nation/articles/2009/06/29/generation_gap_in_us_largest_since_1960s_study_reveals/} \end{qwrap} and displays Figure~\ref{oldage}. \begin{figure}[ht] \centering \includegraphics[width=80mm]{\here/OldAgeSurvey.jpg} \caption{When does old age begin?} \label{oldage} \figsource{From \theGlobe's website, 6/29/2009, \url{http://www.boston.com/news/nation/articles/2009/06/29/generation_gap_in_us_largest_since_1960s_study_reveals/}} \end{figure} The ``average'' of 68 is clearly a mean computed as weighted average. Try to verify it. You may need to estimate some statistics -- in particular, the fraction of respondents in each age category. Can you estimate the median age at which people think old age begins? \end{exx} \begin{exx}{\hassolution\sref{averagesfromhistograms} \gref{histogramaverages}} College presidents' pay. Here is a histogram showing the total compensation for the 100 best paid presidents of public universities. The data is from an article in the April 3, 2011 issue of the {\em Chronicle of Higher Education} (\url{http://chronicle.com/article/Presidents-Defend-Their/126971}) \begin{center} \includegraphics[height=60mm]{\here/CEOSalaryTablecropped.pdf} \end{center} \begin{figure} \addtocounter{figure}{-1} \figcomment{This figure has no figure number.} \figsource{Chart from an Excel spreadsheet we constructed. The data is from \url{http://chronicle.com/article/Presidents-Defend-Their/126971}} \end{figure} To save you staring at the picture, the number of presidents in each of the ranges (reading from left to right) is 51, 25, 15, 8 and then 1 in the last range. You may use Excel for this exercise if you wish, but you don't have to. \begin{abcd} \item What is the mode of this distribution? \item Estimate the median compensation. \item Estimate the mean compensation. \item Write two arguments one of which indicates that the average president is paid appropriately, one that presidential pay is too high. \end{abcd} \begin{sol} \begin{abcd} \item What is the mode of this distribution? The mode is \$350K-\$450K -- the most common salary range. \item Estimate the median compensation. 51 presidents are in the \$350K-\$450K range and 49 make more than that, so the median is \$450K -- right at the top of the most common range. \item Estimate the mean compensation. I computed a weighted average of the middles of the ranges, with weights the number of presidents in each range: \begin{equation*} (51*400 + 25*500 + 15*600 + 8*700 + 1*1300)/100 = 488, \end{equation*} so the mean compensation is approximately \$490K. Perhaps that should be rounded to \$500K. I redid the computation in the spreadsheet at \slink{CollegePresidentsSolution.xlsx}. \item Write two arguments one of which indicates that the average president is paid appropriately, one that presidential pay is too high. Most of the 100 best paid presidents of public universities make about \$400K per year. They run large organizations in the public interest and make much less money than CEOs of companies of comparable size. They are fairly paid. The average salary of these presidents of public universities is nearly half a million dollars a year. That is much more than faculty make at those institutions. They should be paid more like public servants. \end{abcd} \end{sol} \end{exx} \begin{exx}{\artificial\hassolution\worthy\sref{averagesfromhistograms} \gref{histogramaverages}\gref{histograms}} \myindex{Xorlon} \myindex{Fleegs} Table~\ref{table:fleegs} shows the distribution of weights of a sample of Fleegs from the planet Xorlon, where weight is measured in frams.\index{fram} \footnote{We made up the numbers in this artificial problem so that the arithmetic would be easy. We made up the words, too. ``frams'' is one Prof. Bolker could have used in a \myindex{Scrabble} game with his wife -- for 57 points on a triple word score.} \begin{table}[ht] \centering \begin{tabular}{|c|c|} \hline weight range (frams) & percent of sample \\ \hline 20-40 & 10 \\ 40-60 & 10 \\ 60-80 & 20 \\ 80-100 & 10 \\ 100-120 & 50 \\ \hline \end{tabular} \caption{Xorlon Fleegs} \tablesource{Made up data.} \label{table:fleegs} \end{table} \begin{abcd} \item Sketch a neat properly labelled histogram that displays this data. \item Create a properly labelled histogram in Excel that matches the one you just drew. You can start by downloading \link{XorlonFleegs.xlsx}. \suspend{abcd} Answer the following questions. You may work in Excel or with a calculator or do mental arithmetic. \resume{abcd} \item Estimate the mode Fleeg weight. \item Estimate the median Fleeg weight. \item Estimate the mean Fleeg weight. \item Estimate the percentage of Fleegs that weigh more than the median. \item Estimate the percentage of Fleegs that weigh more than the mean. \item Estimate the percentage of Fleegs that weigh more than the mode. \end{abcd} \begin{sol} \begin{abcd} \item Figure~\ref{fig:FleegsSketch} shows a neat properly labelled histogram that displays this data. \fromstudent{Courtney Allen}{}{November 1, 2011} \begin{figure}[ht] \centering \framebox{ \includegraphics[height=60mm]{\here/FleegsSketch.jpg} } \caption{Xorlon Fleegs} \label{fig:FleegsSketch} \end{figure} \item Figure~\ref{fig:FleegsExcel} shows an Excel histogram that displays this data. The source is the spreadsheet \slink{XorlonFleegsSolution.xlsx}. \begin{figure}[ht] \centering \includegraphics[height=60mm]{\here/FleegsExcelcropped.pdf} \caption{Fleegs on Xorlon} \label{fig:FleegsExcel} \end{figure} \item Estimate the mode \myindex{Fleeg} weight. The highest bar is 100-120 frams, so that, or 110 frams, is a good estimate for the mode. \item Estimate the median Fleeg weight. Half the Fleegs in the sample weigh less than 100 frams and half weigh more, so the median weight is 100 frams. \item Estimate the mean Fleeg weight. Averaging the midpoints of each weight range using the percentages as weights is the best way to estimate the mean. The result is \begin{equation*} 0.10 \times 30 + 0.10 \times 50 + 0.20 \times 70 + 0.10 \times 90 + 0.50 \times 110 = 86 \end{equation*} so the mean weigh is 86 frams. \item Estimate the percentage of Fleegs that weigh more than the median. The {\em meaning} of {\em median} tells me that just 50\% of the Fleegs are heavier than the median weight. \item Estimate the percentage of Fleegs that weigh more than the mean. 86 frams is 6/20 or three tenths of the way into the 80-100 fram range, so I'd estimate that the percentage of Fleegs weighing more than that is \begin{equation*} \frac{7}{10}10\% + 50\% = 57\%. \end{equation*} \item Estimate the percentage of Fleegs that weigh more than the mode. I estimated the mode as 110 frams. Half the Fleegs in the highest range, or 25\%, weigh more than that. \end{abcd} \end{sol} \end{exx} \begin{exx}{\hassolution\artificial\sref{averagesfromhistograms} \gref{histogramaverages}} \myindex{Ruritania} Find the mean, median and mode age for male residents of Ruritania% \footnote{ Ruritania is a fictional country in central Europe which forms the setting for {\em The Prisoner of Zenda}, a fantasy novel written by Anthony Hope.) } using the histogram or the data in the spreadsheet \link{Ruritania.xlsx}. \begin{sol} The mode is easy -- it's 0-9 years, since that's the longest bar in the histogram. Adding the percentages in each category I see that the first three come to exactly 50\% (in Excel, where the numbers are rounded for display but not for computation). So the median Ruritanian age is about 30 years (half the people are younger, half older.) I used Excel to compute the weighted average of the midpoints of the categories (5, 15, \ldots) and found the mean age to be about 36 years. \end{sol} \end{exx} \begin{exx}{\artificial\hassolution\sref{percentiles}\gref{percentiles}} Wing Aero percentiles \begin{abcd} \item How many Wing Aero employees are in the bottom tenth percentile in salary? \item What is the salary cutoff for the bottom tenth percentile? \item Answer the same questions for the top tenth percentile. \end{abcd} \begin{sol} \begin{abcd} \item How many Wing Aero employees are in the bottom tenth percentile in salary? By definition, 10 percent of the Wing Aero employees are in the bottom tenth percentile in the salary distribution. Since there are 30 employees, 3 are in the bottom tenth percentile. \item What is the salary cutoff for the bottom tenth percentile? The salaries of the three workers in the bottom tenth percentile are \$17K, \$19K and \$21K. The next highest salary is \$25K, so any number between that and \$21K can be thought of as the cutoff point for the bottom tenth percentile. \item Answer the same questions for the top tenth percentile. The three employees in the top tenth percentile are the CEO, the CFO and one of the other two executives. The salary cutoff for the top tenth percentile is \$250K. \end{abcd} \end{sol} \end{exx} \begin{dummyexx}{\needsquestions\sref{bellcurve}} Bell curve \ldots \end{dummyexx} \begin{exx}{\untested\sref{marginoferror}\gref{normaldistribution}} Activists press officials to put sick-leave proposal to voters An article in the \emph{Orlando Sentinel} on August 6, 2012 discussed a ballot initiative that would require employers with 15 or more workers to provide paid time off for employees for illness-related issues. The article polled voters to gauge support for placing this question on the ballot for the November election, and noted that \begin{qwrap} \begin{quotation} $\ldots$ \firstline{% Results of a poll released Monday show heavy support for the} initiative among Orange County voters. The poll commissioned by Citizens for a Greater Orange County found that 67 percent of residents likely to vote in November support the measure, while 26 percent oppose it. Support is stronger among minorities, women and young voters, according to the poll, which claims a 4.4 percentage-point margin of error among 500 people surveyed by phone a month ago. \webref{% http://articles.orlandosentinel.com/2012-08-06/news/os-sick-leave-ballot-race-20120806_1_ballot-language-signatures-sick-time} \end{quotation} \sourceinfo[684]{Orlando Sentinel, Monday, August 6, 2012, http://articles.orlandosentinel.com/2012-08-06/news/os-sick-leave-ballot-race-20120806_1_ballot-language-signatures-sick-time} \end{qwrap} \begin{abcd} \item Why don't the percentages from this poll add up to 100\%? \item Explain why this statement is not true: ``67\% of the residents likely to vote in November support the measure." \item Explain what the 4.4 percentage point margin of error means for this poll. \end{abcd} \end{exx} \begin{exx}{\untested} \erdos{} numbers \index{\erdos{}, Paul} Paul \erdos{} (1913-1996) was the most prolific mathematician of the twentieth century. He was famous (in mathematical circles) for the way he worked -- he travelled from school to school, writing joint papers with the mathematicians at each.% \teachertag \begin{teacher} Your students will probably not think this topic is useful or interesting but it might be fun if introduced in class. \end{teacher} From Wikipedia: % \begin{qwrap} \begin{quotation} An \erdos{} number describes a person's degree of separation from \erdos{} himself, based on their collaboration with him, or with another who has their own \erdos{} number. \erdos{} alone was assigned the \erdos{} number of 0 (for being himself), while his immediate collaborators could claim an \erdos{} number of 1, their collaborators have \erdos{} number at most 2, and so on.% \webref{https://en.wikipedia.org/wiki/Paul_Erd\%C5\%91s} \end{quotation} \sourceinfo{https://en.wikipedia.org/wiki/Paul_Erd\%C5\%91s} \end{qwrap} You can think of the \erdos{} Number Project as a description of the social network of mathematicians. Its home page is \url{http://www.oakland.edu/enp}; there you can find out that \begin{quotation} \ldots the median \erdos{} number is 5; the mean is 4.65, and the standard deviation is 1.21. \end{quotation} Table~\ref{table:erdos} shows the raw data. \begin{table}[ht] \centering \begin{tabular}{|cr|} \hline \erdos{} number & mathematicians \\ \hline 0 & 1 \\ 1 & 504 \\ 2 & 6593 \\ 3 & 33605 \\ 4 & 83642 \\ 5 & 87760 \\ 6 & 40014 \\ 7 & 11591 \\ 8 & 3146 \\ 9 & 819 \\ 10 & 244 \\ 11 & 68 \\ 12 & 23 \\ 13 & 5 \\ \hline \end{tabular} \caption{\erdos{} numbers} \label{table:erdos} \end{table} \begin{abcd} \item Use Excel to draw a histogram for the distribution of \erdos{} numbers. \item What is the mode of this distribution? \item Verify the claims for the median and mean. \item Verify the claim for the standard deviation. \item Professor Bolker (one of the authors of this book) has an \erdos{} number of 2 (he wrote a paper with Patrick O'Neil, who wrote a paper with \erdos{}). Why is Professor Mast's \erdos{} number at most 3? How might it be less than 3? \item How many mathematicians have a finite \erdos{} number? \item There are some mathematicians whose \erdos{} number is infinite. How can that be? \end{abcd} \end{exx} \begin{ExtraExercises} \begin{exx}{\hassolution} Faculty salaries The chart in Figure~\ref{fig:uth} shows the salary distribution of tenured faculty at the University of Texas Houston. Vertical scale represents the number of faculty, the horizontal axis shows salary ranges in increments of \$20,000. \begin{figure} \centering \framebox{ \includegraphics[width=3in]{\here/UTH.png} } \caption{Faculty Salaries} \label{fig:uth} \end{figure} \begin{abcd} \item Recreate this data in Excel as both a table and a histogram. Print your results on a single page and attach it to the exam. \item What is the modal salary for UTH faculty? The mode is the highest bar -- about 60-80K. \item What is the median salary for UTH faculty? The halfway point to the 203 faculty members (I added up the heights of the bars in Excel) occurs partway through the second bar, so the median is about 70K. \item What is the mean salary for UTH faculty? The mean salary is about 81K -- computed in Excel as a weighted average. mean 81.03448276 \end{abcd} \begin{sol} \begin{abcd} \item Recreate this data in Excel as both a table and a histogram. Print your results on a single page and attach it to the exam. \item What is the modal salary for UTH faculty? The mode is the highest bar -- about 60-80K. \item What is the median salary for UTH faculty? The halfway point to the 203 faculty members (I added up the heights of the bars in Excel) occurs partway through the second bar, so the median is about 70K. \item What is the mean salary for UTH faculty? The mean salary is about 81K -- computed in Excel as a weighted average. mean 81.03448276 \end{abcd} \end{sol} \end{exx} % %\begin{figure} %\centering %\includegraphics[height=50mm]{\here/BadPieChart.png} %\caption{Excel adjusts the data} %\label{fig:badpiechart} %\figsource{Chart from an Excel spreadsheet we wrote.} %\end{figure} % \begin{exx}{\untested\sref{median}\gref{excelaverages}} Doublethink. \headline{Where The One Percent Live: The 15 Richest Counties In America} \begin{qwrap} \begin{quotation} \firstline{% Living in Arlington isn't cheap, so you'd better be making at least} the median household income to live in this county just outside Washington, D.C. \webref{% http://www.businessinsider.com/where-the-one-percent-live-the-15-richest-counties-in-america-2012-2\#5th-richest-arlington-county-va-11 } \end{quotation} \sourceinfo[105]{% www.businessinsider.com/where-the-one-percent-live-the-15-richest-counties-in-america-2012-2\#5th-richest-arlington-county-va-11 } \end{qwrap} How does this statement contradict itself? \footnote{ Thanks to David Kung for this exercise. } \end{exx} \begin{exx}{\needsquestions\sref{marginoferror}} \headline{Surgery offers no advantage for early prostate cancer, study finds} That article reported on a clinical trial involving 731 men diagnosed with prostate cancer. About half had surgery; the rest were monitored. \begin{qwrap} \begin{quotation} After 12 years, nearly 6 percent of men who had immediate surgery died of the cancer, compared with slightly more than 8 percent of those patients who were observed, which was not a great enough difference to reach statistical significance. \webref{http://bostonglobe.com/lifestyle/health-wellness/2012/07/18/surgery-offers-survival-advantage-for-older-men-with-early-stage-prostate-cancer-study-finds/T5XM7APIuoZuav6PbJzYuI/story.html} \end{quotation} \sourceinfo[1038]{http://bostonglobe.com/lifestyle/health-wellness/2012/07/18/surgery-offers-survival-advantage-for-older-men-with-early-stage-prostate-cancer-study-finds/T5XM7APIuoZuav6PbJzYuI/story.html} \end{qwrap} {\em Is there a reaonable way to tie ``not statistically significant'' to ``margin of error''?} \begin{abcd*} \item About how many men were in each category? \item About how many deaths were there in each category? \end{abcd*} \end{exx} \begin{exx}{\untested} The article on new car and truck prices that we studied in \sref*{weightsmatter} first asserts that \begin{quotation} \ldots the average price of a new vehicle in the second quarter [of 2008] fell 2.3 percent from a year earlier to \$25,632 \dots \end{quotation} and later \begin{qwrap} \begin{quotation} \firstline{The result is the average new vehicle now costs less than} 40 percent of an average household's median annual income, the analysts said, whereas from 1991 to 2007, it would cost more than half of the median income. \webref{% http://www.boston.com/business/articles/2008/09/05/new_car_prices_fall_at_fastest_rate_ever} \end{quotation} \sourceinfo[630]{From the AP, also cited in previous chapter. www.boston.com/business/articles/2008/09/05/new\_car\_prices\_fall\_at\_fastest\_rate\_ever } \end{qwrap} Verify as much of this last assertion as you can. \end{exx} Review exercises \begin{exx} Create an Excel spreadsheet and put the following numbers in the first colum. \begin{verbatim} 14 15 22 50 0 33 16 18 23 40 47 \end{verbatim} \begin{abcd*} \item Use Excel to find the mean, median and mode of these numbers \item Change the first number from 14 to 23. How do the Excel calculations change? \item Click the ``undo" button and confirm that Excel reverts back to the original set of numbers. \item Change the last four numbers to 0 (so that the data now read \begin{verbatim} 14 15 22 50 0 33 16 0 0 0 0 \end{verbatim} How do the different averages change? Explain how the data are skewed. \end{abcd*} \end{exx} \begin{exx}{\untested\artificial\gref{excelroutine}\gref{excelwhatif}} Enrollments The final enrollment report for the past year at an unnamed small college provided the following information about students: 450 students freshmen; 421 students sophomores; 400 students juniors and 511 seniors. \begin{abcd*} \item Create an Excel spreadsheet containing this data. Label the columns appropriately. \item Ask Excel to calculate the total number of students enrolled during the past year. Label this result. \item Create a properly labelled bar chart of the student data. \item A corrected enrollment report noted that there were 419 juniors. Make that adjustment in your spreadsheet and check that the other information (total number of students, bar chart) is correctly updated. \item Using this new information, ask Excel to calculate the percentage of students who are freshmen, sophomores, juniors and seniors. Copy and paste so that you type as few formulas as possible. \item Create a new bar chart displaying the percentages. \item Convert your bar chart to a pie chart. \item Arrange the data with percentages and the final bar and pie charts so that they print on one page. \end{abcd*} \end{exx} \begin{exx}{\untested\artificial\gref{percentiles}} SAT exam percentiles A student received this notification on his college entrance exam: \begin{verbatim} English Language Arts: 77th percentile Mathematics: 88th percentile \end{verbatim} Explain these this report in everyday language. \begin{hint} Your answer might begin ``More than three quarters of the students taking this test \ldots'' \end{hint} \end{exx} \begin{exx}{\untested\sref{mmm}\gref{excelaverages}} Comparing the states You can do this exercise using Excel, or with properly documented research. (Your instructor may specify one method or the other.) \item Find the mean, median and mode for the populations of the 50 states. \item Display the answers to the previous question on a properly labelled histogram. Discuss your findings -- is the distribution skewed? \item Redo parts (a) and (b) for the {\em areas} of the states. \item Redo parts (a) and (b) for the {\em population densities} (people per square mile). \end{exx} \begin{exx}{\needsquestions} Jellybean margin of error \url{http://andrewgelman.com/2011/08/that_xkcd_carto/} \end{exx} \end{ExtraExercises} \begin{ReviewExercises} \end{ReviewExercises} \begin{ScopeExercises} \begin{exx}{\untested\complex\sref{barcharts}\gref{excelchart} \gref{excelaverages}} Median wages and incomes. \theGlobe{} printed Figures~\ref{fig:medianwagechange} and \ref{fig:incomewagechange} on January 1, 2011. \begin{figure}[ht] \centering \framebox{ \includegraphics[height=60mm]{\here/MedianWageChange.jpg} } \caption{Median Wages} \figsource{Scanned from \theGlobe, 1/1/2011.} \label{fig:medianwagechange} \end{figure} \begin{figure}[ht] \centering \framebox{ \includegraphics[height=60mm]{\here/IncomeWageChange.jpg} } \caption{Wage and Income} \figsource{Scanned from \theGlobe, 1/1/2011.} \label{fig:incomewagechange} \end{figure} \begin{abcd} \item Reproduce Figure~\ref{fig:medianwagechange} in Excel. \item Compare changes in median wage with changes in median income. \item Are these data consistent with other reports of median income? \end{abcd} \end{exx} \begin{exx}{\complex\sref{mmm}\gref{excelaverages}} The \myindex{Lahey Clinic} Figure~\ref{lahey} appeared in an advertisement for the Lahey Clinic in \theGlobe{} on March 6, 2009. \begin{figure}[ht] \centering \includegraphics[width=80mm]{\here/1in9.jpg} \caption{Lahey Clinic Advertisement} \figsource{Scanned from \theGlobe, 3/6/2009.} \label{lahey} \end{figure} Our first reaction to the ad was ``That can't be true. There are thousands of hospitals, so more than nine of them must be above average.'' Then we thought about it some more. \begin{abcd} \item Use what you learned in the previous exercise to show that the assertion in the ad isn't impossible, although it is unlikely. \item Invent some reasonable numbers for hospital heart attack survival rates that would make the ad correct. \item The ad included a reference to \url{http://knowyournumbers.lahey.org/} for more information. There we found the source \url{http://www.usatoday.com/news/health/2008-08-21-hospitals-standouts_N.htm} (Note date). Describe how the author of the ad reached the conclusion she wanted. Note that the table provides information only about the exceptional hospitals. It does not tell you how many unexceptional ones that are not reported on here. Try to find out more about the missing data. %That URL provided some data, which we downloaded to %\link{hospitalData.csv}. The format is \excel{ .csv}, standing for ``comma separated %values''. It should open in Excel. % %Analyze that data to see if you can find out how the person who wrote %the ad could justify his conclusion. (We haven't tried to do that yet.) % %You may find the data confusing at first. Don't give up too %quickly. Try to sort out what you need. You might want to pay %particular attention to just the rows for which the value in the Topic %column is Heart Attack. You can get those together \end{abcd} \end{exx} \end{ScopeExercises}