
\documentclass[11pt]{article}
\def\Z{\mathbb{Z}}
\def\Q{\mathbb{Q}}
\usepackage{latexsym,amsmath,amssymb,epsfig}
\setlength{\textwidth}{6.5in}
\setlength{\oddsidemargin}{0in}
\setlength{\textheight}{8.0in}
\begin{document}
\centerline{MONT 100N -- Modeling the Environment}
\centerline{Chapter 2 Project -- Ratios, Proportions, Percents, and Excel}
\centerline{September 22, 2017}
\vskip 10pt
\noindent
The project for this chapter has two goals:
\begin{enumerate}
\item To continue our work with ratios, percentages, and proportions on some
world population and GDP data from the U.N., and 
\item To introduce the Excel spreadsheet program\footnote{Some freely available software such as 
the LibreOffice suite or Google Spreadsheets can also be used here.} that we will be using extensively
for the rest of the year.  
\end{enumerate}
The following instructions will lead you through a first practice Excel session
working with a spreadsheet file that you will download from the 
course homepage.

\section{Getting Started with Excel}
Launch Excel and 
take a look at the overall layout of the of the window.
There are tabs, menus, etc. similar to many standard
programs, but there are some differences too.  In particular
if you are using the Windows Excel 2007 or later version, 
note the large ``Office Button'' at the upper left.  This is 
where all of the usual File options are 
located (i.e. the controls for reading in or saving files, printing, etc.)

Like all spreadsheet programs, Excel gives you a workspace
that is composed of a 2D grid of ``cells'' identified by
location -- by an {\it address}.  The columns are labeled
by capital letters, and the rows are labeled by numbers.
\begin{itemize}
\item  A single cell is referenced by the
column, followed by the row, for instance {\tt B23} is
the cell in column B and row 23.
\item  A range of cells is referenced by
giving the ``starting cell,'' a colon, and the ``final
cell'' in the range.  For instance {\tt B2:B45} indicates
the cells in column B and rows 2 through 45.  {\tt B2:F2}
indicates the cells in row 2 and columns B through F.
Similarly, {\tt B2:D10} indicates all the cells in a
\emph{rectangular block} with upper left corner at cell
{\tt B2} and lower right corner at cell {\tt D10}.
\item  The addresses seen so far are all
\emph{relative addresses}.  In other words, they are
set up so that if we perform an operation in one
cell that depends on the entries to the left in its
row, then it is possible to copy and paste that operation
to other rows and the entries in the new row will be
used.  If you want to specify a \emph{fixed} address
then put in $\$$ characters:  {\tt \$C\$5} means the
cell with fixed address in column C and row 5.
(We will see several examples of this in a while;
if it is not clear why we need this distinction,
wait until you see the examples!)
\end{itemize}

The contents of a cell
can be a text label identifying what the data in a row or
column represents, a number, or a formula indicating how
to perform a desired calculation using other information
in different cells within the spreadsheet.  When you
finish entering a formula this way and press the Enter key,
the indicated computation
is performed and the result is displayed in that cell.
One \emph{very nice} feature of spreadsheets is that
if you change the contents of a cell that is used to
compute a value this way, then the calculation is
automatically performed again to update the value displayed.
We will also see this in a moment.

\section{A First Worked Example}
Begin by reading in the spreadsheet {\tt First.xls}
from the course homepage.  (If you have not done so already,
download and save that in a location where you can have 
Excel read it in.)
\begin{itemize}
\item Press the ``Office Button'' at the upper
left of the Excel window (or use the File pull-down menu in Mac versions),
\item  then Open,
\item Find the appropriate location where you saved the {\tt First.xls}
file in the folder box at the top
of the Open window, highlight the file {\tt First.xls},
\item and press Open at the bottom.
\end{itemize}
\noindent
You should now see a rectangular block of cells filled
with names, text, and numbers at the upper left of the spreadsheet in rows
1 through 10 and columns A through E.  Think of this as
the grade book for a small class with 8 students (the rows are labeled
with their names) who
have had four assignments as in the labels for columns B through E.
Note that {\tt A12} has the text ``Average''  but there
are no numbers on that row (yet!).  We are going to use
Excel to compute the averages on each assignment.
\begin{itemize}
\item In cell {\tt B13}, enter the formula
{\tt $=$AVERAGE(B2:B9)}.  As you type, you will see
this showing up in the cell and in the input box
above the grid.  When you are done press Enter, and the average
will be computed and displayed.
\item  Now we will use the same method
to compute the average on each of the other assignments:
Highlight cell {\tt B13} by clicking 
over that cell.  Make sure the Home tab at the top
of the Excel window is active, press Copy (next to the ``Office Button''),
drag the highlighting box so that all the cells in row
13, columns B to E are highlighted, and press Paste (next to Copy).
You should now see the averages for each column.
\item  In doing the averages we
were making use of the {\it relative addressing} mentioned above.
Copying the formula in one cell and pasting it into another
also changed the addresses of the cells that the formula
was applied to.  Now, we are going to perform an operation
where we want to use contents of a fixed cell on multiple
rows.  Start by filling in new information in row 14:
Put a text label ``Weights'' in {\tt A14} and the constants
$.3$ in {\tt B14}, $.25$ in {\tt C14}, $.4$ in {\tt D14}, and $.05$ in
{\tt E14}.
\item  In cell {\tt F1} add the text label ``Course Average.''
In {\tt F2} enter the formula
\vskip 5pt
\centerline{\tt =\$B\$14*B2 + \$C\$14*C2 + \$D\$14*D2 + \$E\$14*E2}
\vskip 5pt
\noindent
You should see the weighted average displayed.
\item  You can now copy and paste that formula to the other
cells in column F and rows 3 through 9 to do the same computation
for the other students in the class.  (Note that the weights always
come from the same row, hence the fixed addresses.  Can you see what
would happen if we did not do it that way?)
\end{itemize}
Here is some other useful information:
\begin{itemize}
\item There are a number of standard mathematical functions that
can be applied to numerical contents of spreadsheet grids.  
In an Excel formula you can
square the contents of a cell by saying, for instance {\tt B13\^{}2}.
\item  If you want to take the square root of something computed from
information other cells, you 
use {\tt SQRT()}.  For instance, to compute the square root of the sum of 
{\tt B13\^{}2} and 
{\tt C13\^{}2}, you could enter\\
\vskip 5pt
\centerline{\tt = SQRT(B13\^{}2 + C13\^{}2)}
\vskip 5pt
\noindent
in another cell of the spreadsheet.  When you press Enter, the value
will be computed and displayed in that cell.
\end{itemize}
\vskip 10pt
\section{The Data}
\vskip 10pt
The country-by-country population information in Table~\ref{UNData} (on the next page) 
comes from Table 3 of the 2015 United Nations Demographic
Yearbook\footnote{{\tt https://unstats.un.org/unsd/demographic/products/dyb/dybsets/2015.pdf}, 
accessed on June 26, 2017.}.  Notes:
\begin{enumerate}
\item Only a selection of the roughly 200 nations are listed(!) The first seven nations are
located in Africa, the next seven are in North and South America (including the Caribbean), 
the seven following those are in Europe, and the last seven are in Oceania and Asia.  
\item The male and female populations are according to the most recent available national 
census (which varies by year according to the nation), and are in units of 1000s.
\item The 2010 and 2015 midyear populations are estimates given in units of 1000s
\item The surface area is in square km. 
\item Unavailable data is marked by a $*$
\item GDP is the market value of all final goods and services produced in a year. 
The given figures are estimates for 2015, in units of trillions of U.S. dollars.
\end{enumerate}

\section{Questions}
Create a spreadsheet, enter this data, then make the following computations.
Answer the questions below in a separate text file or document.
\begin{enumerate}
\item[(A)] For each of these 28 nations, compute the male population as
a percentage of the total population according to the most recent national census. 
In which nations is the male percentage greatest?  In which is it smallest?
Why does it work out this way?
\item[(B)]  For each of the 28 nations, compute the percentage difference
between the male and female populations.
\item[(C)]  Compute the \emph{percentage change} in the populations of each of these
nations between 2010 and 2015.  (You won't be able to do this in two cases because
of missing data; just ignore those nations for this question.)
How many of these nations are losing population,
and which has the largest percentage decrease?  Which has the largest percentage
increase?  
\item[(D)]  Compute the population densities in people per square kilometer
for each of these nations.  Use the 2010 mid-year estimates, since some
data is unavailable for 2015.  Which nations have the largest population
densities among the 28 listed here?  (Note: the highest population densities
actually tend to occur for smaller, especially island, nations).
\item[(E)]  What are the 5 richest and 5 poorest of these countries, and how
should you make a fair comparison?  
\item[(F)]  Recall that the GDP figures are estimates for 2015.
Assuming that the per capita GDP was unchanged from 2010 to 2015 (that is that the 
proportion of GDP to population was the same both years), estimate
the GDP figures for each of the countries in 2010.  
\item[(G)]  What proportion of the total estimated 2010 population of the 
world, $6.9$ billion, is accounted for in the 28 countries listed in Table~\ref{UNData}?
\end{enumerate}

\begin{center}
\begin{table}[ht]
\caption{Extracts from 2015 U.N. Demographic Yearbook and 2015 GDP Figures}
\label{UNData}
\renewcommand\arraystretch{1.3}
\noindent\[
\begin{array}{|c|c|c|c|c|c|c|}
\hline
{\rm Nation} & {\rm Male} & {\rm Female} & {\rm 2010\ Mid.} & {\rm 2015\ Mid.} & {\rm Area} & {\rm GDP}\\
\hline
{\rm Egypt} & 37,219 & 35,579 & 78,685 & 88,958 & 1,002,000& .282 \\
\hline
{\rm Kenya} & 19,192 & 19,418 & 40,406 & 45,509 & 591,958 & .061\\
\hline
{\rm Mozambique}& 9,747 & 10,506 & 22,417 & 25,728 & 799,380 & .017 \\
\hline
{\rm Namibia}& 1,022 & 1,091 & 2,143 & 2,281 & 824,116 & .013 \\
\hline
{\rm Niger }& 8,519 & 8,620 & 15,204 & 19,125 & 1,267,000 & .008 \\
\hline
{\rm Nigeria}& 71,345 & 69,086 & 159,619 & * & 923,768 & .568\\
\hline
{\rm Senegal} & 6,428 & 6,445 & 12,509 & 14,357 & 196,712 & .016 \\
\hline
{\rm Argentina} & 19,524 & 20,593 & 40,788 & 43,137 & 2,780,400 & .543\\
\hline
{\rm Brazil} & 93,407 & 97,349 & 195,498 & 204,451 & 8,515,767 & 1.77\\
\hline
{\rm Canada} & 16,414 & 17,062 & 34,005 & 35,849 & 9,984,670 & 1.55\\
\hline
{\rm Chile} & 7,448 & 7,669 & 17,094 & 18,006 & 756,102 & .258\\
\hline
{\rm Dominican\ Rep.} & 4,739 & 4,706 & 9,479 & 9,980 & 48,671 & .064 \\
\hline
{\rm Honduras}& 4,052 & 4,251 & 8,046 & 8,577 & 112,492 & .019 \\
\hline
{\rm Mexico } & 54,855 & 57,481 & 114,256 & 121,006 & 1,964,375 & 1.14\\
\hline
{\rm U.S.} & 151,781 & 156,964 & 309,347 & 321,419 & 9,833,517 & 18.03\\
\hline
{\rm Austria} & 4,094 & 4,308 & 8,361 & 8,576 & 83,871 & .437 \\
\hline
{\rm Croatia} & 2,066 & 2,219 & 4,295 & 4,225 & 56,594 & .057 \\
\hline
{\rm France} & 29,715 & 31,685 & 62,918 & 64,395 & 551,500 & 2.42\\
\hline
{\rm Germany} & 39,146 & 41,074 & 81,757 & 81,198 & 357,376 & 3.36 \\
\hline
{\rm Greece} & 5,303 & 5,513 & 11,121 & 10,858 & 131,957 & .238 \\
\hline
{\rm Poland} & 18,420 & 19,624 & 38,517 & 38,006 & 312,679 & .545\\
\hline
{\rm U.K.} & 31,126 & 32,254 & 62,759 & 64,875 & 242,495 & 2.86\\
\hline
{\rm Armenia} & 1,347 & 1,525 & 3,256 & 3,011 & 29,743 & .011 \\
\hline
{\rm Australia} & 10,737 & 10,990 & 22,032 & 23,778 & 7,692,024 & 1.23\\
\hline
{\rm China} & 686,853 & 652,872 & 1,337,700 & 1,371,220 & 9,600,000 & 11.16 \\
\hline
{\rm India} & 623,270 & 587,585 & 1,182,105 & * & 3,287,263 & 2.12\\
\hline
{\rm Iran} & 37,906 & 37,244 & 74,340 & 78,773 & 1,628,750 & .425\\
\hline
{\rm Japan} & 61,829 & 65,281 & 128,070 & 126,958 & 377,930 & 4.38 \\
\hline
{\rm Pakistan} & 67,840 & 62,739 & 173,510 & 191,710 & 796,095 & .251\\
\hline
{\rm Saudi\ Arabia} & 15,307 & 11,830 & 27,563 & 31,016 & 2,206,714 & .653\\
\hline
\end{array}
\]
\end{table}
\end{center}
\end{document}
