Problem I. 389. (December 2015)
I. 389. Women's heptathlon is a combined athletics event. This has been the topic of our database management problem I. 338. Now you should evaluate the results of a similar event by using a spreadsheet application.
The seven events of a heptathlon are the following: 100 meters hurdles, 200 meters and 800 meters track running, high jump, long jump, shot put, and javelin throw. Points are awarded based on a specific formula and on an international standard performance table according to the following.
\(\displaystyle
\text{Points obtained}=\big[A\cdot {XB}^C\big],
\)
where \(\displaystyle X\) is the athlete's performance, \(\displaystyle [~]\) denotes the integer part, and \(\displaystyle ~\) is the absolute value. The constants \(\displaystyle A\), \(\displaystyle B\) and \(\displaystyle C\) are determined by the International Association of Athletics Federations, and depend on the type of the event:
Event 
\(\displaystyle A\) 
\(\displaystyle B\) 
\(\displaystyle C\) 
200 m running 
4.990870 
42.5 
1.810 
800 m running 
0.111930 
254.0 
1.880 
100 m hurdles 
9.230760 
26.7 
1.835 
High jump 
1.845230 
75.0 
1.348 
Long jump 
0.188807 
210.0 
1.410 
Shot put 
56.02110 
1.5 
1.050 
Javelin throw 
15.98030 
3.8 
1.040 

The tabulatorseparated and UTF8encoded file hetforras.txt contains data corresponding to the heptathlon events during the 2012 Summer Olympics, and the constants from the previous table.
1. Import the text file hetforras.txt into the spreadsheet application beginning with cell A1. (The range A1:I4 contains the transpose of the above table.) The name of the sheet should be eredmények (=results). Your work should be saved as hetproba (=hepathlon) in the default file format of the spreadsheet application.
2. Create a new sheet pontszámok (=scores), and create a header in the first row according to the sample.
3. In the range A2:B40 of this sheetreferencing cells A7:B45 of the sheet eredményekdisplay the name (=Név) and nationality (=Nemzet) of the 39 athletes.
4. In cells C2:I40, according to the method described above and by using a single formula and its copied version, you should determine the points for each athlete and for each event. If the corresponding eredmények cell is empty, then the referencing pontszámok cell should also be empty.
5. In cells J2:J40, for each athlete, you should give the sum of the points obtained for the eventsprovided that they have results in all seven categories; otherwise, a message ``Nincs'' (=none) should appear.
The following tasks should be solved by using the sheet eredmények.
6. In cell M2 and by using a function, you should determine the number of athletes who completed the heptathlon (=Versenyt teljesítők száma), that is, having points in all seven categories.
7. In cells K8:N17 and by using a formula, you should list the number of points (=Összpont), the name of the athlete (=Név), and the abbreviation of their nationality (=Nemzet) for the first 10 athletes (ranked according to their points (=Helyezés), which can be assumed to be different for different athletes).
8. In cells C50:I50, determine the names of the winners for each event. If there is a tie in a particular category, it is sufficient to give only one athlete name. 9. It is possible that more athletes participate in the heptathlon (=Résztvevők) from the same nation (=Nemzet). Under the cells K20:L20, you should list the number of athletes (sorted in decreasing order) for each nation. In column K in your solution, you can collect and use the 3letter abbreviations of the nationsit is not required to use any formula. 10. Cells of the sheet eredmények should be formatted according to the sample sheet.
The spreadsheet (hetproba.xlsx, hetproba.odt) and a short documentation (also with the name and version number of the spreadsheet application) should be submitted in a compressed file i389.zip.
Downloadable file: hetforras.txt
(10 pont)
Deadline expired on 11 January 2016.
Statistics:
