Elaborate the document according to the instructions within 60 minutes!

Maximum point score is 60.

Minimum point score: 40

Elaborate the document according to the instructions within 60 minutes!

1. Rename the Sheet 1 to Volume of prism and Sheet 2 to Households consumption.. 2 point

Sheet “Households consumption”

2. Sort the table by the date and category................................................................ 2 point

3. In the cell F58, find out how many CZK were paid (sum numbers in the column F)...... 1 point

4. In the column G, use the EUR/CZK exchange rate (C3) and count prices in Euro. Use the absolute cell reference (fixed address, $) and spread the formula in all rows............................................. 3 points

5. Change the format of cells in columns F and G so that they display numbers with two decimal places and with thousands separator............................................................................................ 2 point

6. In the column H, count how many per cents is the price out of total (F58). Use correctly the absolute cell reference and spread the formula through all rows of the table............................................ 4 points

7. Format all values in the column H so that they display as per cents with two decimal places. 2point

8. In the column I, derive the month number out of the value in the column A. Fill the formula in all rows of the column I............................................................................................................... 2 point

9. In the cell D57, calculate how many kilograms of meat were bought (use the function SUMIF()).. 6 points

10. Format the fifth row of the table with bold and italic.............................................. 2 point

11. Use the automatic filter and display only milk products with price between 5 and 25 CZK. 4 points

12. Create the pivot table from the list and place it on a new sheet. In the pivot table, display the average price in CZK for each category and for each month. Use these fields: Price in CZK, Month and Category. Display numbers with two decimal places............................................................................................... 7 points

Sheet “Volume of prism”

13. Insert a formula for the volume of the prism in the cell C13................................... 2 points

14. Adjust the formula with the absolute cell reference and spread it through the table. 3 points

15. Merge cell area C11:G11, put the centred alignment horizontal and vertical........... 2 point

16. Merge cell area A13:A17, put the centred alignment horizontal and vertical and change the text direction to vertical (90°).................................................................................................................... 3 points

17. In the area A11:G17, put All borders line and Thick box border line........................ 2point

18. Use the conditional formatting to highlight all values that are greater than or equal to 600. All matching values must be displayed in bold font and red colour............................................................... 4 points

19. Create a chart out of the table (see the picture below). Place the chart on the same sheet. Select a proper type of the chart from the category “Line with markers”. Write the title “Volume of the prism” to the chart, put the label “Height (h)” to the axis X and put the label “Volume” to the axis Y......................... 7 points

Дата добавления: 2015-11-14