44095: IT for Management

1. 44095: IT for Management Introduction to Spreadsheets (Basic/Complex Formulae & Worksheets) By: Ian Perry Room: C41C E-mail: i.p.perry@hull.ac.uk Tel: 01723 35 7287 http://itsy.co.uk/ac/0708/Sem2/44095_IT4M/

2. When to use a Spreadsheet? • Whenever NUMERICAL data requires: • Calculation • which may be complex, repetitive, or both. • Presentation • in tabular and/or graphical format. • Analysis • of complex situations. • Exploration • of probable outcomes.

3. Cell Reference Formula Range B6: =SUM(B3:B5) Columns A B C D 1 R&D Cost 2003 2004 2005 2 3 2360 2460 2560 Rent Text 4 800 900 Travel 5 5000 6800 Personnel 6 8160 10160 TOTAL Numbers Cell Rows A ‘typical’ Spreadsheet

4. A B C D E 1 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 2 East 20.4 27.4 90 20.4 3 West 30.6 38.6 34.6 31.6 4 North 45.9 46.9 45 43.9 5 East 90 West 80 North 70 60 50 40 30 20 10 0 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Data can be presented as a Chart Which may make it easier to see trends, or spot exceptional/unusual values.

5. The ‘simplest’ Spreadsheet Formula? • Is a reference to another cell. • NB. all formulae begin with an = sign. This is, perhaps, the MOST USEFUL spreadsheet formula; as, ideally, any data you wish to add to a spreadsheet should only be entered ONCE.

6. Simple Calculations • i.e. using a Spreadsheet like a Calculator. NB. By default * and / happen before + and - So, be careful when/if using brackets in calculations, as: C6/D6+E6*(C6-D6) will produce a VERY different result, i.e.: -137.66

7. Simple Spreadsheet Functions - 1 • Sum • =sum(range) OR =sum(cell, cell, …)

8. Simple Spreadsheet Functions - 2 • Average • =average(range) OR =average(cell, cell, …) Try to avoid calculating averages like this: =(C14+D14+E14)/3 =(Sum(C14:E14))/3 as, if the number of data values in the range to be averaged changes, then you will have to remember to alter your formula.

9. Simple Spreadsheet Functions - 3 • Standard Deviation (of population) • =stdevp(range) OR =stdevp(cell, cell, …)

10. Simple Spreadsheet Functions - 4 • Maximum • =max(range) OR = max(cell, cell, …) • Minimum • =min(range) OR = min(cell, cell, …) • Median • =median(range) OR = median(cell, cell, …) • Count(of cells containing numeric data) • =count(range) OR = count(cell, cell, …) • Now(i.e. today’s Date & Time) • =now() • Today (i.e. just today’s Date) • =today()

11. The ‘CountIf’ Function • Counts the number of values in a range that match a given criteria. • =countif(range,criteria)

12. The ‘IF’ Function • Tests a condition in another cell, and decides what to display in this one. • =if(condition,true,false)

13. Nested ‘IF’ Functions • To test for more than one condition, you could use a series of nested IF’s: • =if(condition1,true,if(condition2,true,false))

14. The ‘VLookUp’ Function • Much easier to use than nested IF’s, when there are many conditions to test for: • =vlookup(value,range,offset) NB. the first column of the lookup table (i.e. column E in this example) MUST contain single data values AND be in Ascending order. Either: ALPHABETICALLY Or: NUMERICALLY

15. Complete this fairly simple spreadsheet model; by adding the missing formulae. This Week’s Workshop – Part 1 These Lecture notes contain examples of all of the spreadsheet formulae you will need in order to complete the Workshop tasks. You MUST only enter ‘new’ formulae in the GREEN cells, then Copy & Paste to fill in the formulae for the remainder of each Column or Row.

16. The Completed Spreadsheet Model

17. Why use Multiple Worksheets? • ‘Old’ Spreadsheet Software • enabled the user to build two-dimensional models of numerical data on a single worksheet. • these two-dimensional models could be quite large (typically 256 columns by 8192 rows), and finding you way around could be difficult. • Modern Spreadsheet software has introduced the idea of the ‘Workbook’, containing a number of ‘Worksheets’, which: • enables three-dimensional numerical data models to be constructed. • can ease the organisation of, and navigation around, large numerical data models.

18. 3 Worksheets by default, but can easily add more. An Excel 2007 ‘Workbook’ Ü 16,384 Columns; ‘A’ to ‘XFD’ Þ Ý Rows; ‘1’ to ‘1,048,576’ ß

19. Press the ‘Insert Worksheet’ tab • OR ‘Shift+F11’ if you prefer to use the Keyboard To add a New Worksheet

20. ‘Right-Click’ on a ‘Sheet’ Tab • Select ‘Rename’ • Type in the New Name Always ‘Rename’ your Worksheets!

21. Now have 5 Worksheets Renamed as: Qtr1, Qtr2, Qtr3, Qtr4, & YearSum. The ‘Qtr1’ Worksheet Note. The other 3 Quarters have exactly same layout, but different Sales & Profit data.

22. Formula: =Qtr1!B8 Reference the Sales Total for Qtr1 Worksheet Reference Cell Reference

23. Formula: =Qtr1!C8 Reference the Profit Total for Qtr1 • NB. • The easiest way to do this is to copy the previous formula from B4 to C4.

24. } Repeat for other 3 Quarters. Copy for ‘Profit’ Formula: =sum(B4:B7) Complete the Yearly Summary

25. ‘Click-and-Hold’ on the Worksheet you wish to move, ‘Drag’ to new position, then let go. Re-order the Worksheets

26. This Week’s Workshop – Part 2 • Complete this spreadsheet model, by; • Adding a ‘new’ (Summary) Worksheet. • Referencing data on the other two Worksheets. • Adding some ‘simple’ calculations to the Summary Worksheet.

27. The Completed Spreadsheet Model