2.4. Part III Comparing Happiness Data across years

We have two files of happiness data, one for 2017 that you have been using, and another for 2012 so you can do some comparisons across a span of five years.

  1. Start a new workbook and get each of the csv files for the happiness data loaded into a separate sheet.

  2. Now lets create a table on a new sheet that shows the happiness rank for each country for each year. You’ll have 4 columns country name, 2012 rank, and 2017 rank. Hint: Use VLOOKUP – But wait! What is the deal with these #N/A values? Shouldn’t the happiness report have the same countries for every year? Apparently just like regular people not all countries participated in the survey every year. Lets press on and hope for best for the moment…

Now create a column where you calculate the change between the 2017 rank and the 2012 rank. Then create a new cell where you find the max value of this new column.

Oh dear, hoping for the best is rarely a good strategy. You will notice that the result of looking for the max value in column that contains one of these #N/A values results in the function returning #N/A as well! It appears we will have to find a better strategy.

The right way to handle this problem is to use the IFERROR function of Google Sheets. This is one area where Google and Excel are slightly different. In Excel the function is called ONERROR, but they behave the same in any case. The IFERROR function takes two parameters. A function or calculation, and a value to insert in the case of an error. In our case we want to adjust our subtraction so that if there is an error we will set the difference to be 0. We’ll change the calculation to look something like =IFERROR(C2-P2, 0) Now you will see that wherever we had an #N/A value before we now have a 0. You will also see that we get interesting values for max and min.

Q-1: What is the name of the country with the largest positive change in their happiness rank? What about the largest negative change? ?

  1. Next let us find the biggest changes in the happiness scores from 2012 to 2017

    Q-2: Are the countries that with the largest change in rank the same as the countries with the largest change in score?

  • False

  • Surprisingly yes.

  • True

  • No, the countries are different. If you are guessing you better complete this part to find out who they are and investigate why that might be.

Q-3: What is the name of the country with the largest positive change in their happiness score? What about the largest negative change? ?

Q-4: Give an explanation for why you think the two are different? Outline an experiment or calculation that you can do with a spreadsheet to back up your answer.

  1. For the five countries with the largest changes changes in ranking between 2012 and 2017 what are the factors that changed the most? For this part you can do this by making comparisons between sheets rather than creating a huge number of new columns on this summary sheet.

Q-5: What did you learn in the previous investigation? What were the factors that changed the most?

Challenge

  1. The choropleth gave us some insight into how happiness may be related to the continent. It was pretty clear that African nations were less happy than many others. Lets see if we can quantify that.

    1. First we need to find a file that helps us map from country to continent. Lets add this to our file as a new worksheet.

    2. How can we add a column (or a few columns) to our happiness spreadsheet from this spreadsheet? – VLOOKUP

    3. Once we have the continent name added to the spreadsheet can we find the average happiness score for each continent?

Lesson Feedback

You have attempted of activities on this page
Next Section - 2.5. Challenge: Calculating a Correlation matrix