Mathematical and Physical Journal
for High Schools
Issued by the MATFUND Foundation
Already signed up?
New to KöMaL?

Problem I. 278. (November 2011)

I. 278. Depletion of fossils fuels emphasizes the role of other sources of energy, such as water. River discharges and water levels have been measured for many decades. In this task you will analyze water levels of Danube in 2009 and 2010.

The tabulator separated text file vizallas.txt downloadable from our homepage contains data in UTF-8 encoding.

During your solution, you should, if possible,

-- use a formula, function or link

-- make auxiliary computations only below line 78, or to the right of column R.

1. Open the file vizallas.txt in a spreadsheet such that the first piece of data is displayed in cell A1. Save the sheet named i278 in the default format of the application. The first column of the sheet contains the years and days, while columns B:M contain the abbreviation of the months together with the corresponding water levels.

2. In cell E72, determine the number of months in which the average monthly levels in 2010 were higher than in 2009.

3. In cell E73, you should give the \(\displaystyle 10^{\rm th}\) highest water level in 2009. (It is possible that some measurements may have the same water levels.)

4. Cells E74:E75 should contain which year and month had the largest change in water level.

5. A certain boat can not start sailing if the water level is less than 200 cm. Cell E76 should contain the total number of such days during the sailing seasons between March 21 and October 15.

6. Cell P4 should contain an expression that determines the water level on a particular day based on the date written into cells P1, P2 and P3, or display the message ``Invalid date'' if there is no corresponding water level data. Cell P2 will contain the 3-letter abbreviation of the month similarly to the example.

For example, if P1: 2010, P2: ``FEB'' and P3: 11, then P4 should be 154.

If any of P1, P2 or P3 is empty, P4 should be empty too.

7. Create a diagram about water levels in July 2009 and 2010 according to the following:

\(\displaystyle a.\) The type should be a line diagram.

\(\displaystyle b.\) The diagram should appear on a new sheet.

\(\displaystyle c.\) Data corresponding to 2009 should have a thick red line, while that of 2010 should be in thick blue.

\(\displaystyle d.\) The monthly average water levels should be graphed as thin red (2009) and thin blue (2010) lines. If the data are changed, the diagram should follow those changes.

\(\displaystyle e.\) A legend should appear underneath, such as ``2009 July average'' or ``2010 July average''.

\(\displaystyle f.\) The title of the diagram should be ``Water levels in July''.

8. The text in cells containing years and months in the original sheet should be in bold and aligned in the center.

9. Background color for cells with computed values should be yellow.

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

(10 pont)

Deadline expired on December 12, 2011.

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

Adrián Patrik, 12. évfolyamos tanuló (Baross Gábor Középiskola, Szakiskola és Kollégium, Debrecen) mintaszerű megoldása.



9 students sent a solution.
10 points:Adrián Patrik, Kovács Balázs Marcell.
9 points:Antal János Benjamin, Barkaszi Richárd Miklós, Gema Barnabás.
8 points:2 students.
7 points:2 students.

Problems in Information Technology of KöMaL, November 2011