1 / 34

Form 4 Spreadsheets Revision

Form 4 Spreadsheets Revision. Instructions: Go through slides and complete all exercises. Up to exercise 5. Save it on the computer or on a flash drive. (I know the computers are lame… but you can do it.). Yes. I am sick… wanted to go to Movie Towne… but I already went last week.

buckminster-church
Télécharger la présentation

Form 4 Spreadsheets Revision

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. Form 4 Spreadsheets Revision Instructions:Go through slides and complete all exercises. Up to exercise 5.Save it on the computer or on a flash drive.(I know the computers are lame… but you can do it.) Yes. I am sick… wanted to go to Movie Towne… but I already went last week. Please try to do the work and not just talk through the whole class. Microsoft Excel

  2. Definition of Spreadsheet A spreadsheet is a program which organizes information into rows and columns so that it can be operated on easily. Advantages Ability to organize numbers and re-calculate values easily. Ability to manipulate data and turn it into usable information

  3. Spreadsheet Basics Rows– Run from left to right and they are identified by a number Columns – Run top to bottom and are identified by a letter. Cells – Cells are formed by an intersection of a row and a column. Each cell has an address that starts with the letter of the column then the number of the row

  4. Spreadsheet Basics columns rows cell

  5. Spreadsheet Basics Labels – The title given to a cell Value – A number or piece of Data used in a calculation Formula – An instruction that the computer follows to work out a mathematical equation.

  6. Spreadsheet Basics Workbook The main document we use to store and work with data. It can contain many sheets. A workbook is the actual file that contains different Worksheets. Worksheet A worksheet is also called a spreadsheet. We can work with multiple worksheets/spreadsheets that are related in one workbook

  7. Worksheets

  8. Arithmetic Formulae

  9. Basic Calculations

  10. Basic Calculations with Cell Referencing

  11. Ranges • A range is a rectangular block of cells • It is written by separating the upper left cell and the lowest right cell by a colon. • Eg. C2:F10

  12. Cell Formatting Any cell or range can be formatted by highlighting the particular cell or range and using the formatting tools on ‘font’ section of the ribbon.

  13. Exercise 1 Copy the following table and add 5 more names to the list. Change the all the information in row 1 to BOLD.Change all the names to a different colour.

  14. Using formulas • Every formula must start with an equal sign within the cell. • Eg. = A1 + A2 Addition = A1 * A2 Multiplication = A1 - A2 Subtraction = A1 / A2 Division

  15. FUNCTIONS =SUM The SUM function calculates the sum of a range of values. Eg. Calculate the sum of all the values in that range. = sum(A1:E1)

  16. FUNCTIONS =AVERAGE The AVERAGE function calculates the average of a range of values. Eg. Calculate the average of all the values in that range. = AVERAGE(A1:E1)

  17. Exercise 2 Create a grocery list that contains 10 items in one column. Create a column called price and put prices for the items. Create a column quantity called quantity and put in quantities for each item. Create a total column and calculate the total based on the quantity and prices you listed.

  18. FILL HANDLE The fill handle in a cell is a function that allows you to apply one formula or function to a specific range of cells. Eg. You can put the sum formula in 1 cell and calculate the sum for all other cells below it.

  19. FORMATTING CELLS Cells can be formatted based on the type of information they have in them. To format a cell, click the cell, go to number in the home ribbon and click the drop down arrow

  20. FUNCTIONS =NOW The NOW function inputs the date of and time Eg. If you want a spreadsheet to display the date and time whenever you open the sheet. = NOW()

  21. FUNCTIONS =TODAY The TODAY function inputs the date Eg. If you want a spreadsheet to display the date whenever you open the sheet. = TODAY()

  22. FUNCTIONS =MIN The MIN function calculated the LOWEST value in a range of cells. Eg. To calculate the lowest mark from a range of marks. = MIN(B1:B12)

  23. FUNCTIONS =MAX The MAX function calculated the HIGHEST value in a range of cells. Eg. To calculate the highest mark from a range of marks. = MAX(B1:B12)

  24. Exercise 3 Create a spreadsheet for the following table. Gross Pay = Salary + Allowance Medical is 5% of salary Tax is 33% of Gross pay Net pay = Gross pay – (medical + tax)

  25. Exercise 4 - FILL HANDLE EXERCISE • Complete the table above. • Using the fill handle: • Calculate the total each person pays • Add a column for max and min individual item • Sort the names in alphabetical order

  26. SORTING You can sort the names in your table in alphabetical order. Highlight the range you would like to sort and choose the sort function. You can sort in ascending or descending order Sort the names in Exercise 4 in ascending order.

  27. PIE CHARTS Charts can be created based on the data you have in your sheet. Selecting the right data is the first step in creating a chart. Highlighting the data above and choosing pie chart would result in the following pie chart being made.

  28. IF FUNCTION =IF(condition, true, false) The IF function checks a cell to see if a particular condition is true or false. Eg. If you want to determine if a person passed or failed an exam. = IF(B1>50, “PASS”, “FAIL”)

  29. COUNT FUNCTION =COUNT The COUNT function checks a range of cells and counts the amount of cells with value. Eg. If you want to count the number of people who paid money. = COUNT(B2:B9)

  30. COUNT EXERCISE Write a formula to calculate the amount of times a student is present

  31. Exercise 4 • TYPE B = $8,000.00 • TYPE A = $15,000.00 • Rental Fee = Cost * Amount • If rental fee is <$25,000 the car is affordable

  32. MERGE AND CENTER The merge and center function joins the cells selected and puts the text in the center. SELECT THE TWO OR MORE CELLS TO MERGE AFTER BEFORE

  33. TEXT ALIGNMENT The text in a cell can be aligned either left, center, right. It can also be aligned by top, center, bottom.

  34. Exercise 5 Key: CL - $40/hr, CLW - $45/hr Using a formula calculate the cost per hour. Calculate the Total cost for each client. Add a row to find the average amount of money for the month. Find the highest amount made, put that value in H1 If average made is greater than $500.00 print QUOTA MET in H2

More Related