1 / 20

Data Analysis: Spreadsheets

ICT iGCSE. Data Analysis: Spreadsheets. 14.1: Creating a data model. Absolute vs. Relative. Excel tries to second guess what you want it to do. Absolute referencing. Sometimes, you don’t want one of the operands to change:. Named Cells and Ranges.

gamba
Download Presentation

Data Analysis: Spreadsheets

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. ICT iGCSE Data Analysis: Spreadsheets

  2. 14.1: Creating a data model

  3. Absolute vs. Relative • Excel tries to second guess what you want it to do.

  4. Absolute referencing • Sometimes, you don’t want one of the operands to change:

  5. Named Cells and Ranges • When you are going to use the contents of a cell several times in different formulae of a spreadsheet, it’s a good idea to give the cell a name. • It’s easier to remember “Tax” than “Cell H2”…or was it H3….or G2….or….

  6. Named ranges • Named ranges make it easier to maintain accuracy when you are using a large table of data, for example in a LookUp.

  7. Using Functions • Functions are just formulae that are already defined: you just tell the function which cells to work with. • Functions have pre-defined names that you can use: SUM or AVERAGE, for example.

  8. Task 1 • Open Sales.csv. This is a spreadsheet used to calculate bonus payments to sales staff of a small company. • Name cell B1 ‘Unit’ • Name cells A5 to C5 ‘Rate’ • Name cells B11 to G18 ‘Sold’

  9. Easy functions • SUM –adds a range of numbers • AVERAGE – finds the average in a range of numbers • MIN – finds the lowest value in a range of numbers • MAX -finds the highest value in a range of numbers

  10. Other Functions: INT • In maths, an integer is a whole number, with NO decimals or fractions. • In Excel, the INT function takes the whole number part of a number and removes any digits after the decimal point.

  11. Task 2 • Open Pay.csv • In cells B9 to B12, enter appropriate functions • In cells C9 to C12, use the INT function to return the whole number from each of these results

  12. Other Functions: ROUND • ROUND is used to round the number to a required number of decimal places, to the nearest whole number. • Task 3: • Try the exercise in cells H2 to L9.

  13. Other Functions: COUNT • This can be used to count the number of things in a list • Task: Open ChristmasCharts.xls • In cell B57, enter a COUNT function to count the number of entries in the list. • Copy the formula across to cells C57 and D57. • What do you notice?

  14. Other Functions: COUNTA • Counts non-blanks in a list • Task: Open Project.csv. It contains a list of people who are working on a group project, and for each person lists the number of tasks they still have to complete.

  15. Think carefully! • In cells B21 and B23, use appropriate formulae (both COUNT and COUNTA) COUNTA counts cells with anything in! Remember: COUNT only counts cells with numbers in

  16. Other Functions: COUNTIF • Counts only cells which meet a given criteria. • Open Staff.csv and enter formulae to count the number of different staff.

  17. Other Functions: IF

  18. Nested Formulae and Functions • A nested formula is just a formula that has another one inside it.

  19. Adding a secondary axis

More Related