Activity FAO and Excel by u1fxcA


									Activity: Food and Agricultural Organization (AP Statistics)

Materials: Laptop

Statistics is a tool that is meant to analyze and help us understand data. To this
end we will need several sources of data. The first source that we will make use
of is the Food and Agriculture Organization of the United Nations.

Please go to

Click on ‘want to register?’

Register yourself at FAO by filling in the information for the following:

      Name
      Manlius Pebble Hill for the organization
      Educational institution for the type of organization
      USA for the country
      Check the first column of boxes (and any others that interest you)
      Use your school email address
      Make up a password

We will use data from this website today and throughout the year.

Go to and find the current agricultural

Find the spread sheets for the following:

      Total and Agricultural Population (including forestry and fisheries) (A1)
      Human Development Index and Poverty (G4)

Find the definitions for total population, agricultural population, human
development index and poverty. Find the units for each of the categories.

Copy the columns for 2009 in each of the following categories into a new excel
worksheet titled Excel Practice 1 AP Statistics. Make sure the countries match
the data in each row.

      Name of Country
      total population
      agricultural population
      human development index
      Poverty Prevalence
      Year Poverty Prevalence was recorded
In a new column you are going to calculate the ratio of agricultural population to
total population. Label the column as such and then in the first row (lining up with
the first country) place an equation that looks like ‘=E11/D11’ which should
represent the ratio of agricultural population to total population of the first country.
In this formula agricultural population for Afghanistan was in column E row 11
and the agricultural population for Afghanistan was in column D row 11.

Then copy the formula in that cell and paste it to the rest of the cells in that row
all the way down to the last country. The numbers should all be different and
represent each country’s ratio.

Note: all formulas in a cell for Excel should be preceded by an equal sign.

Excel has a list of statistical functions that you can use, these are listed under
‘statistical functions’ in the help search menu. You will be using several of these
functions from excel. Some of these include the following:

      =AVERAGE( range of cells ) This produces the average of all the numbers
       that you highlighted.
      =MEDIAN( range of cells) This finds the median or middle number of the
       cells that you highlighted.
      =SUM (range of cells ) This adds up the numbers in the cells that you
      =STDEV (range of cells ) This produces the Sample Standard Deviation
       for the cells that you highlighted.
      =CORREL ( first range of cells, second range of cells ) This produces the
       Correlation between the variables represented in the given two ranges of
       cells [usually two columns or two rows].

Do the following calculations with the data that you copied from the FAO
statistical yearbook:

      Find the average agricultural population
      Find the median agricultural population
      Find the sum of the agricultural population and compare it to the world
       agricultural population. What should be true about these numbers?
      Find the standard deviation of the agricultural population.
      Find the correlation between the column labeled Human Development
       Index and the column that should represent the ratio ‘agricultural
       population : total population’
Make the following scatter plots and label the axes and each scatter plot:
   Human Development Index versus Proportion of Population that Farms
   Poverty Prevalence versus Proportion of Population that Farms

What is the definition of the term ‘human development index’?

What is the definition of the word ‘poverty’?

What do you notice about the overall trends in each scatter plot? Does it look like
there is any relationship between the different variables that you plotted?

What do the points on the x axis mean?

What would this suggest about what a nation should do to improve its human
development index?

Would your solution in the previous question automatically reduce poverty in a
given country? Why or why not?

What is considered the typical trend with respect to the percentage of people that

Why does USA’s poverty prevalence not show up in the table?
Important aspects of this activity:

      You should always be able to analyze a data set using Excel even if you
       don’t remember all the formulas. The key is that you must remember what
       the formulas mean, when you can use the formulas, what the formulas
       can (and can’t) do.
      Taking a course in statistics allows you to become statistically literate,
       which will allow you to be intelligently informed about the information that
       you see around you. You will see statistical information pretty much any
       where you go or in many informative documents that you will see.
      Often statistical information can help guide decisions that you would have
       to make in your occupation.
      The statistical information also can show how your intuition is not always
       correct. To this end knowing what a statistic means can help you make life
      This activity demonstrates the process of collecting, displaying, describing,
       analyzing and drawing conclusions from data. This process is the main
       process of statistics.
      The charts that we made and the descriptions of trends that we found in
       the charts are example of descriptive statistics.
      The column marked total population is an example of the population of
      The column called total agricultural population is an example of sample
      The question that asked you to make a decision based on the trends that
       you saw in the data is an example of inferential statistics.

To top