Spreadsheets

1. Spreadsheets Demonstration

2. Some of the slides in this demonstration have blue un-numbered text. • This text comes from the example exam which I have used for this demonstration. • Unless I have stated otherwise the following slides demonstrate how to carry out the instructions in blue. • Some instructions are similar to skills previously demonstrated so you may be asked to complete such instructions independently.

3. You work for a company called Rootrainer Trees. You are going to perform some clerical tasks for this company.All currency values should be in UK pounds (sterling) with the £ sign visible.

4. Using a suitable software package, load the file TREES.CSV • Save Target As the file TREES.CSV and CODES.CSV into a new folder. • e.g. My Documents / ICT / Spreadsheets Demo • Navigate to the folder above and double click the TREES.CSV file.

5. Click the File menu and click the SaveAs option.

6. Click the Saveastype drop down menu, move up the list and click Microsoft Excel Workbook. • Click Save.

7. In the Species column use a lookup function to show the full species name, use the Code column for the lookup value and the file CODE.CSV for the array.Replicate this formula so that the complete species name for each tree is shown.

8. Open the file CODE.CSV from the folder you made for this demonstration in step 1(you should have copied it there in step 2) e.g. My Documents / ICT / Spreadsheets Demo • Switch back to TREES using the Taskbar. • Click in cell B7 • Type = • Click the functions drop down menu and click LOOKUP(If it is not in the list then click More Functions, scroll down the list to find it and then click OK.

9. Click the lookup_value,array option and click OK. • Click in the Lookup_value box and click the cell A7.

10. Click in the Array box and switch the CODES file you opened earlier (use the Taskbar). • Select the cells A2:B15 and click OK.

11. Drag the fill handle down to the end of the list (B43) and let go.

12. Enter the following data into the model.

13. Enter the 1.27, 0.928 and 1 into the cells C2, C3 and C4 respectively.

14. Name the cell containing 1.27 acidName the cell containing 0.928 alkalineName the cell containing 1 neutral

15. Click the cell C2. • Double click to select the cell reference. • Type acid and press enter. • Do the same for the cells C3 and C4 but name them alkaline and neutral respectively.

16. Format these three cells to 1 decimal place.

17. Select the cells C2:C4 • Click the Format menu and click the Cells option.

18. Click the Number tab. • Click the Number option. • Click the down arrow so the Decimal places reads 1. • Click OK.

19. Use a Countif function in cell B2, to count the number of Acid entries in the PH column.Use a Countif function in cell B3, to count the number of Alkaline entries in the PH column.Use a Countif function in cell B4, to count the number of Neutral entries in the PH column.

20. Click the cell B2. • Type = • Click the functions drop down menu and choose COUNTIF.(If it is not in the list then click More Functions, scroll down the list to find it and then click OK. • Click in the Range box.

21. Select the cells D7:D42 and let go.

22. Select the cell range in the Range box and press F4.

23. Click in the Criteria box, click cell A2 and click OK. • Use the Fill handle to replicate this formula in cells B3 and B4.

24. The next 3 slides contain instructions similar to those already demonstrated. • Please complete them using what I have shown so far.

25. Enter the following data into the model.

26. Format these three cells as currency in pounds sterling to 2 decimal places.Name the following cells as the named range PotPrice Hints on the next slide.

27. Formatting cells as currency in pounds sterling to 2 decimal places. • As before but click the Currency option and choose £ from Symbol list.

28. In the Pot column use a lookup function to show the Rate, use the Size column for the lookup value and the named range PotPrice for the array.Replicate this formula so that the pot price for each tree is shown.

29. The next slide has not been covered so please follow the numbered instructions on the slides after it.

30. In the Soil column (Cell H7) use an IF function to calculate the cost of the soil used.If the PH is Acid then multiply the named cell acid by the Bare rooted price.If the PH is Alkaline then multiply the named cell alkaline by the Bare rooted price.If the PH is Neutral then multiply the named cell neutral by the Bare rooted price.Replicate this formula so that the soil price for each tree is shown.

31. Click the cell H7. • Type = • Click the functions drop down menu and choose IF.(If it is not in the list then click More Functions, scroll down the list to find it and then click OK. • Click in the Logical_test box.

32. Click the cell D7 and then type =“Acid”

33. Click in the Value_if_true box. • Click the cell acid, type * and click the cell F7.

34. Click in the Value_if_False box. • Click the functions drop down menu and click If.

35. Click in the Logical_test box, click cell D7 and type =“Alkaline” • Click in the Value_if_true box, click the alkaline cell, type * and click cell F7. • Click in the Value_if_false box, click the functions drop down menu and click If again.

36. Click in the Logical_test box, click cell D7 and type =“Neutral” • Click in the Value_if_true box, click the neutral cell, type * and click cell F7. • Click in the Value_if_false box, type 0. • Click OK. • Drag the fill handle down to cell H42 and let go.

37. In the Cost column (Cell I7) add together the Pot and Soil costs.Replicate this formula so that the Cost for each tree is shown.

38. Click cell I7 • Type = • Click cell G7 • Type + • Click cell H7 and press enter.

39. Drag the fill handle down to cell I42 and let go.

40. Format the Bare, Pot, Soil and Cost columns as currency in pounds sterling to 2 decimal places.

41. Select columns F to I by click the column header of F and dragging over to I.

42. Format the cells as currency in pounds sterling to 2 decimal places.

43. Set the page orientation to landscape.Save the data model and print a copy of the sheet showing the formulae used. Make sure that the contents of all cells are visible and that the printout fits on a single page.Make sure that your name, candidate number and centre number are printed in the header of the page.

44. Click the File menu and click the Page Setup option. • Click Landscape. • Click Fit to: 1 page wide by 1 page tall. • Click OK

45. Click any cell so deselect any selected cells.Press Ctrl + ` to show formulas. • Double click each column header to auto size each column.The dotted line shows it will not fit on one page.

46. Click the View menu and click the Header and Footer option.

47. Click Custom Header. • Click in any section and type your name, candidate number and centre number. • Click OK x 2. • Print the sheet.

48. The next slide has already been shown please follow its instructions (press Ctrl + ` to show the data rather than formulas).

49. Set the page orientation to portrait.Save the data model and print a copy of the sheet showing the values. Make sure that the contents of all cells are visible and that the printout fits on a single page. Make sure that your name, candidate number and centre number are printed in the header of the page.

50. Hide rows 1 to 5 inclusive.Hide columns A, C, F, G and H.