NCC012 10 - Intermediate Health Care Spreadsheets March 13, 2017

Week 1 Overview

This week, if you were not part of the first course you'll be expected to complete the lesson over creating and formatting charts and graphs.  If you have completed the first class, you may simply attach the the spreadsheet and self reflection you completed before. We will also cover other features that will help organize a spreadsheet.  You are encouraged to read the lecture notes while watching the video tutorials and carrying out the steps simultaneously.  You will also practice these steps in the corresponding homework assignment, then view the homework video and evaluate your own performance.

Lecture Notes and Data Sets

Lecture Notes and Data Sets

I encourage you to open the data set and carryout the steps in the lesson while following the notes and watching the video (pausing as needed).

Lecture Video Tutorials

1 Charts and Graphs - Video Tutorial

I encourage you to carry out the steps in the lectures while following the notes and pausing the video as needed.
    Note: If you were in the first course, this is a repeat of the lesson over charts and graphs.  You are not required to repeat the lesson.
 
 
 
 
 
 
 
 
 
 

2 Sparklines, Filter, and Linking - Video Tutorials

I encourage you to carry out the steps in the lectures while following the notes and pausing the video as needed.
 
      Note: Sparklines did not exist until the 2010 version of Excel.  If you are working with and earlier version, just watch to see how it can be used the next time you update your software!
 
 

Assignments

1 Charts and Graphs

Note:  If you took the introduction course, you may simply use the completed documents from the last course, rather than repeating the assignment. 
- Make a scatter-plot of the hourly rate and hours worked.  Format this plot appropriately.
- In cells E17 and E18 use the COUNTIF formula to count the number of employees working overtime, and the number of employees not working overtime.  Display this data with a pie chart and a par graph.  Format these appropriately.
- Display the net pay (with overtime) with a line graph.  Format this graph appropriately. 
- After completion, save the Excel file as YourName Week1a Assignment, and submit via the appropriate dropbox located under the 'Coursework' tab located at the left of the course page. 
(.xlsx, 56K)
Download 1 Charts and Graphs Assignment
Note:  If you took the introduction course, you may simply use the completed documents from the last course, rather than repeating the assignment.  
Review the homework "solution" tutorial, complete the self evaluation, save the file as YourName Week1a Evaluation and submit via the appropriate dropbox under the 'Coursework' tab located at the left of the course page.
Note: each self evaluation is the same, so if you keep the blank file somewhere accessible, you do not need to download it after every assignment. 
(.xlsx, 41K)
Download Self Evaluation

2 Sparklines, Filter and Linking

- Insert a column after the "Weight Lost in Week 4" column and insert sparklines to display the weight each individual has lost each week - use the bar graph option.
   *If you're working with a version older than Excel 2010 sparklines do not exist, so you must skip this step. 
- Filter the data to display only those individuals who started at least 75 pounds overweight.
- Insert another worksheet.  Name the existing worksheet "Data" and the new one "Summary."
- After completion, save the excel file as YourName Week1b Assignment, and submit via the appropriate dropbox located under the 'Coursework' tab located at the left of the course page.  
(.xlsx, 44K)
Download 2 Sparklines, Filter and Linking Assignment
Review the homework "solution" tutorial, complete the self evaluation, save the file as YourName Week1b Evaluation and submit via the appropriate dropbox under the 'Coursework' tab located at the left of the course page.
Note: each self evaluation is the same, so if you keep the blank file somewhere accessible, you do not need to download it after every assignment. 
(.xlsx, 41K)
Download Self Evaluation

Homework Solutions