Problem I. 314. (February 2013)
I. 314. There is a consensus among major science academies of the world that a leading cause of global warming is the increasing emission of greenhouse gases into the atmosphere. International organizations hence closely monitor such effects of the economy of different countries. A gas is called greenhouse gas, if it is more likely to absorb outgoing infrared radiation. The impact of such gases on the global warming is different, so in order to make a comparison between them, we use carbon dioxide equivalence: comparing the effects of the gas to the effects of 1 ton of CO2.
The most important greenhouse gases our civilization has been producing include: carbon dioxide (CO2), methane (CH4), nitrous oxide, a.k.a. laughing gas (N2O), fluorinated carbohydrates (CHF3, CH2FCF3, C2H3F3), perfluorocarbons (CF4, C2F6, C3F8, C6F14), sulfur hexafluoride (SF6), saturated chlorofluorocarbons or freons (CFCl3, CF2Cl2, CF2CFCl3), haloalkanes (CBrClF2, CBrF3) and unsaturated chlorofluorocarbons (CHF2Cl, CH3CF2Cl).
The Hungarian Central Statistical Office (KSH) regularly publishes the greenhouse gas emission of the EU countries based on a EuroStat database. This database is used for creating comprehensive analyses, and your task now will be to provide data for some investigations. Pieces of data in the database refer to the period between 1990 and 2010, and should be understood in 1000 tons of carbon dioxide equivalence units.
The UTF-8 encoded and tabulator separated text file, uveghaz.txt can be downloaded from our webpage, containing the data.
During your solution you should take into account the following.
- Use formulae, functions or references whenever possible.
- Auxiliary computations can be performed to the right of column AC, or below row 250.
1. The file uveghaz.txt should be opened in the spreadsheet application such that its first piece of data is put into cell A1. Then the sheet should be saved as i314 in the default format of the application.
2. Determine for each country and year the value of ``Egyéb kibocsátás'' (other types of emission) in rows 10, 18, and so on. These values can be determined from the ``Teljes kibocsátás'' (total emission) and the emission data of the various sectors displayed below that.
3. In columns X, Y and Z, you should determine for each data series the maximal, minimal and average emission values.
4. In column AA, you should determine for each data series the year in which the maximal emission occurred.
5. In cell B243, you should compute the number of countries in which the gas emission between 2000 and 2010 in the ``Hulladékgazdálkodás ágazat'' (waste management sector) decreased gradually (``Csökken'') from year to year.
6. In cell B244, you should determine the number of years during which the total emission produced by the three ``Mezőgazdaság'' (agricultural) sectors of the Benelux countries (i.e., Belgium, the Netherlands and Luxembourg) exceeded 9% of the combined emission of these three countries.
7. You should devise an expression in cell B247 which - based on the data in cells B245 (``év'' = year) and B246 (``ágazat'' = sector) - determines the number of countries producing more emission in that year and by that sector than the corresponding Hungarian sector that year (``Mo. helye'' = the rank of Hungary in the list). If any of the cells B245 or B246 is empty, cell B247 should remain empty.
8. Create a pie chart on a new sheet and display the emission data of each sector of the Hungarian economy in 2000. You should print the name of the sectors together with the corresponding percentages. The part of the chart corresponding to ``Egyéb'' (other types of emission) should be placed in front and highlighted, as in our example. The title of the diagram should be ``Greenhouse gas emitters of Hungary''.
9. Make similar changes in the format of your sheet as in the sample (merging cells, alignment, adding borders or thickening them).
Your spreadsheet (i314.xls, i314.ods, ...) together with a short documentation (i314.txt, i314.pdf, ...) - also describing the name and version number of the spreadsheet application - should be submitted.
The tables contain some samples.
Deadline expired on 11 March 2013.
Sorry, the solution is available only in Hungarian. Google translation
Qian Lívia (Szeged, SZTE Ságvári E. Gyak. Gimn.) megoldását ajánlom a megoldók figyelmébe. i314.xlsx