1 / 20

44221: Information Systems

44221: Information Systems. Lecture 6 (Week 8) Introduction to Spreadsheets By Ian Perry http://itsy.co.uk/ac/0506/Sem1/44221_IS/. When to use a Spreadsheet?. Whenever NUMERICAL data requires: Calculation which may be complex, repetitive, or both. Analysis of complex situations.

jescie-thompson
Download Presentation

44221: Information Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 44221: Information Systems Lecture 6 (Week 8)Introduction to Spreadsheets By Ian Perry http://itsy.co.uk/ac/0506/Sem1/44221_IS/

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

  3. Cell Reference Formula Range B6: =SUM(B3:B5) Columns A B C D 1 R&D Cost 2000 2001 2002 2 3 2360 2460 2560 Rent Text 4 800 900 Travel 5 5000 6800 Personnel 6 8160 10160 TOTAL Numbers Cell Rows A simple 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 easily be presented as a Chart

  5. The Simplest Spreadsheet Formula? • Is a reference to another cell. • NB. all formulae begin with an = sign.

  6. Simple Calculations • i.e. using a Spreadsheet like a Calculator.

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

  8. Simple Spreadsheet Functions - 2 • Average • =average(range) OR =average(cell, cell, …)

  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, …) • Now (i.e. today’s Date & Time) • =now() • Count (of cells containing numeric data) • =count(range) OR = count(cell, cell, …)

  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)

  15. Spreadsheet Development - 1 • Draft it out on paper first, considering: • Shape • does the problem suit a landscape or a portrait shaped solution? • Content • which parts of the spreadsheet content will be text, data, formulae? • Outputs • will the data be presented in tabular and/or graphical format? • is there a need to print the results or will they be viewed on screen?

  16. Spreadsheet Development - 2 • Two types of data in a spreadsheet: • Input data - entered/altered often, e.g. • sales made, hours worked, money to change, etc. • Reference data - rarely changes, e.g. • vat rates, pay rates, currency conversion rates, etc. • Always separate these data types: • Keep input data areas together (and easily accessible) • data entry is the biggest source of errors. • Keep reference data together • and protect cells where data entry is not required.

  17. Spreadsheet Development – 3 • Start simply: • Do not try to build a large complex spreadsheet model containing features/functions you have never used before. • Try out any new features/functions you want to use, by building a small controlled example. • Test each stage in the development of your spreadsheet model: • testing each new feature/function you add; • gradually incorporating all of the functionality required. • Don't get too clever, too soon!

  18. Spreadsheet Development - 4 • Save a "Template" • i.e. a complete spreadsheet model without input data; • containing text, formulae and reference data only. • use a copy of this "Template" for testing. • Test Rigorously • use data which will produce known outputs; • e.g. 1,1,1, or 50,50,50, or 100,100,100, sequences. • use someone else to test ease of data entry; • you may find your design is not as easy for others. • Don't forget to amend the "Template"; • if (when!) changes are required.

  19. Complete this fairly simple spreadsheet model; by adding the missing formulae. This Week’s Workshop • NB. • these Lecture notes contain examples of all of the spreadsheet formulae you will need in order to complete the Workshop tasks.

  20. The Completed Spreadsheet Model

More Related