Problem I. 287. (February 2012)

I. 287. Before going hiking for a day, it is worth making a thorough plan. There can be many interesting details.

For the trip between Bükkszentkereszt and Eger, we select the track on a map by using data of a map making software. For different segments of the track, the source file contains tourist symbols (`` jelzés'' in the example), road materials (``burkolat''), road lengths (``hossz''), the elevation (``emelkedés'') and depression (``süllyedés'') in meters, and the planned time in minutes (``idő'') to accomplish that distance. Data in this example are taken from http://turistautak.hu/.

By using a spreadsheet application, you should solve the following tasks.

1. Load the file utterv.txt. (UTF-8 encoded, tabulator separated text file) into the spreadsheet, beginning with cell A1, and save it as i287 in the default file format.

2. By inserting columns and completing the header, create the structure similar to the example.



3. Column E should contain the distance in meters from the starting point. Column J should contain the average speeds in km/h for each section (by using the given length and time values).

4. Column H should contain the altitudes in meters after covering each section, if the original altitude (Eger) is in cell M1.

5. By using a function, cell M3 should contain the total length (in km) of the tour.

6. By using a formula, cell M4 should contain the average speed of the complete tour (using the total distance covered and the sum of planned times).

7. Cell M5 should contain the number of sections in which the speed will be greater than the total average speed.

8. We will also take rests during the excursion. Cell M6 should contain the number of hours in rest, provided that after each 5 km, we are going to pause for 10 minutes.

9. Cell M7 should contain in hours the planned time for the tour, being the sum of the section times and resting minutes.

10. By using a copyable formula, to the right and down of cell L9 you should determine the number of different road materials and their total length.

11. Now format the sheet according to the following description.

a. Cells in the first row should be centered horizontally and vertically.

b. Column names and texts in the first row should be displayed in bold face.

c. Precision in cells that may contain non-integer computed values should be set to 2 digits.

d. Computed quantities in range I2:I4 should have the units according to the example, further, 2 digits of precision. To the right of column L, each computed values should have units.



e. Cells containing data should be surrounded by a thin frame from inside. Cells in range A1:J57 should be framed by a thick line from outside. The first line should be framed by a thick line also from below. The other data should not be framed.

12. Create a PointXY diagram. The vertical axis is the altitude in meters, and the horizontal axis corresponds to the distance covered in km.

a. Axis labels should be displayed. The diagram should be scaled so that the data fills the complete area.

b. The diagram should contain no legend. The title should be ,,Altitude diagram''.

The spreadsheet (i287.xls, i287.ods, ...) together with a short documentation (i287.txt, i287.pdf, ...) also describing the name and version number of the spreadsheet application should be submitted in a compressed file i287.zip.

(10 pont)

Deadline expired on March 12, 2012.

Sorry, the solution is available only in Hungarian. Google translation

Megoldásokról:

A beküldött megoldások jól sikerültek, a feladat nem bizonyult nehéznek. Kisebb hiányosság, hogy többen a feladat kérésének - "a skálákat állítsuk be úgy, hogy az ábrázolt adatok kitöltsék a rendelkezésre álló területet" - nem tettek eleget. A magasság ábrázolását 200 méternél érdemes kezdeni.

Mintamegoldásként Fényes Balázs 9. osztályos (Budapest, Szerb Antal Gimnázium) tanuló munkáját közöljük: i287.xlsx

Statistics:

11 students sent a solution.
10 points:	Fényes Balázs, Jákli Aida Karolina, Kucsma Levente István.
9 points:	Adrián Patrik, Antal János Benjamin, Barkaszi Richárd Miklós, Gema Barnabás, Kalló Kristóf, Kocsis 789 Mátyás, Kovács Balázs Marcell, Veress Péter.

Problems in Information Technology of KöMaL, February 2012