Problem I. 344. (March 2014)
I. 344. A spectacular and popular event during each summer in Hungary is the Formula One championship organized at the Hungaroring racetrack in Mogyoród. The present exercise is based on the data of the championship in 2013. You should open the file f1.txt in your spreadsheet (the UTF-8 encoded and tabulator-separated file can be downloaded from our web page), then save it in the default file format of your spreadsheet application. You should take into account the following instructions when solving your tasks.
- Whenever possible, use a formula, function or reference.
- Auxiliary computations can be performed to the right of column R or below line 50.
1. Determine the total race time for each pilot (who did not fail to finish the race) in hours, moreover, for each car which is not lapped (that is, completed the maximal number of laps in the race) also determine the additional time in seconds elapsed after the winner won the race.
2. You should display the average speed (in km/h) of the first 3 winners in the range B30:B32.
3. Cell B33 should contain the usual abbreviation of the country where most of the racers came from.
4. For each non-lapped car, display the distance from the finish line at the final moment of the race assuming that they were racing at their average speed.
5. Cell A37 should contain the text ``Csapatpontszámok'' (= team points). Column A in the next 11 lines should display the team names in alphabetical order by using a formula. In the corresponding adjacent cells of column B, the total number of team points should be visible, or these cells should remain empty if none of the team pilots scored any points. (You can use the fact that each team has two pilots in the race.)
6. Under the column label csapat (= team), you should create the team name. You get the team name by removing the last part of the model name that is separated by a space. For example, if the model name is Lotus E21, then the team name should be ``Lotus''.
7. Upon entering a valid final position into cell A35, the team result should appear in cell B35 according to the following example.
||Fernando Alonzo (ESP), Ferrari pilot; his teammate Felipe Massa (BRA) finished the race behind him, at the position.
||Pastor Maldonado (VEN), Williams pilot; his teammate Valtteri Bottas (FIN) could not finish the race.
||Mark Webber (AUS) Red Bull pilot, outrun by his teammate Sebastian Vettel (GER) finishing the race at the position.
8. The units (h, s, km/h, m) should appear whenever a computed quantity has such physical meaning.
9. The header of the table containing the pilots' data should be formatted according to the example.
10. Create a new sheet named pepita (= chequered). Cells of this sheet should be set to be square-shaped. Upon entering two positive integers not exceeding 250 into cells A1 and B1, the legendary black-and-white chequered flag appearing at the end of a Formula One race should be displayed by using an appropriate method. This pattern should appear beginning with cell C3, and its number of columns and number of rows are determined by the values of cells A1 and B1, respectively. The rest of the cells in the sheet should remain empty: they should not contain any given or derived data.
Your spreadsheet (i344.xls, i344.ods, ...) with a short documentation (i344.txt, i344.pdf, ...) should be submitted, also containing the name and version number of the spreadsheet application used.
Some examples are found below. In the first screenshot, ``Magyar Nagydíj'' is the Hungarian Grand Prix, ``Körök száma'' is the number of laps, ``Pályahossz'' is the length of the track, ``Versenytáv'' is the total distance to be covered during the race, ``Vezető'' is pilot, ``helyezés'' is ranking, ``pontszám'' is the number of points, ``rajtszám'' is the starting position in the starting grid, ``név'' means name, ``ország'' is the country, ``csapat'' is team, while ``modell'' is the model name. In the second screenshot, ``Versenyautó'' is race car, ``motor'' is the engine type, ``Teljesített kör'' is the number of completed laps, ``Versenyidő'' is race time, ``óra'' is hours, ``perc'' is minutes, ``másodperc'' is seconds, ``ezredmásodperc'' is one-thousandths of a second, ``órában'' is the race time expressed in fractional hours, ``Lemaradás/távolság'' means the distance from the winner, and ``idő'' refers to the corresponding time behind the winner.
Downloadable file: f1.txt
Deadline expired on April 10, 2014.
Sorry, the solution is available only in Hungarian. Google translation
Mivel senki sem küldött be hibátlan megoldást, ezért a makróktól és tömbfüggvényektől mentes, magyarázatokkal tűzdelt saját megoldásomat adom közre. i344.xls