The data used for all items in this unit are the amount of forested area as a percentage of the total land area for 15 countries for the years 2005, 2010, and 2015, and those data are always in columns B, C, and D, respectively. Columns E, F, and G are always empty when the students first navigate to each item, and the default ordering of the countries is alphabetical, based on how the country names are translated in each language. Note that in the image above, the data has been manipulated already to correspond to the description of the solution that follows.
The first item in the unit asks the students to identify, in terms of percentage points, the three countries that between 2005 and 2015 had: the greatest gain in its percentage of forested area, no overall change in its percentage of forested area, and the greatest loss in its percentage of forested area. Responses are entered in each row of the table via drop-down menus that contain the name of all 15 countries.
One possible solution method, which is reflected in the image above, is to use the spreadsheet to perform the following calculation: “Column D subtract Column B,” which subtracts the percentage of forested area in 2005 from the percentage of forested area in 2015 for each country. The results of that operation are shown in column E. Next, a student may choose to sort the data in Column E to make it easier to identify each country.
The country with the greatest gain is the country with the largest positive result, which is Greece at 2.34 percentage points; the country with no overall change is the country with a difference of 0.00, which is Armenia; and the country with the greatest loss is the country with the smallest negative result, which is Panama at -2.22 percentage points.
A full-credit response is correctly identifying all three countries and scaled at Level 5, meaning it was a difficult task for students. Partial credit was given for correctly identifying any two countries, and that was still a moderately difficult task that scaled at Level 4, which is not surprising given that partial credit still requires doing the same work as a full-credit response. That is, to identify any two or three countries correctly, students need to determine what calculation(s) to perform, how to use the spreadsheet to perform them, and lastly interpret the results with respect to the context.
Also, depending on the order that the student performs the calculation, identifying the countries could be more difficult. For example, if the student calculates “Column B subtract Column D” (instead of “Column D subtract Column B”), then the sign of each result that appears in column E will be reversed (e.g., Greece = -2.34 and Panama = +2.22). However, based on these data, the percentage of forested area for Greece actually increased for each year shown, and the percentage of forested area for Panama actually decreased for each year shown.