Spring '17 
CIS/Web 105: Data Analysis & Analytics with Excel 
Last updated on

Allow 24 hours to complete each assignment.
Start working on your assignment early enough to get some help if you get stuck. Don't
wait 'till the night before class to start.
Final Exam  Thursday, May 11 @ 7:15  9:15 PM:
Due Thursday, May 4:
Due Thursday, April 27:
Due Thursday, April 20:
Due Thursday, April 13:
NO CLASS TONIGHT  Stay tuned for next week's assignment.
 Use the Top Five Names data to create a spreadsheet that
looks like this:
Note: This is just part of the
spreadsheet.
 Use the Top Five Names data to create a spreadsheet that
looks like this:
Get as close as
you can to this chart...
 Print both spreadsheets, put your name on them and be ready to pass them in at the beginning of class.
 Be ready to show me your work at the beginning of class.
Due Thursday, April 6:
 Take notes in your notebook as you watch
Create a
distribution for categorical data in MS Excel for another take on what we did last class. He adds some
contextual information and presents some nice alternate methods. Pay particular attention to how he creates the bins.
 Take notes in your notebook as you read and study How to create a histogram in Excel 2010, 2013 and 2016
The article covers four methods to create a histogram: COUNTIFS, the Analysis Toolpak, FREQUENCY, and PivotChart.
Since we haven't covered Pivot Tables yet you can skip the PivotChart section.
Note that not all methods work with
categorical (ex. State) data. Use the information in the tutorial to:
 In class we used the COUNTIFS method to two histograms, one by state, the other by time, for a subset of the QVC data.
 Use the
Analysis Toolpak to create two additional histograms, one by state, the other by time, for the subset of the QVC data.
 Use the FREQUENCY function to create two more histograms, one by state, the other by time, for the subset of the QVC data.
 Use the information from the section on
How to customize and improve your Excel histogram to improve and customize your histograms.
 Select one of the methods from the article to create two histograms, one by state, the other by time,
for the entire QVC dataset.
 Use the information in
Create a histogram chart
to create two more histograms, one by state, the other by time, for the subset of the QVC data.
Challenge (extra
credit): See if you can get the horizontal axis on the time chart to show two hour bins starting at 0:00.
 Print the histogram for the method you liked best, put your name on it and be ready to pass it in at the beginning of class.
 Be ready to show me your other histograms at the beginning of class.
 Links for class:
Due Thursday, March 30:
 Use the 2017 tax brackets data from
http://www.bankrate.com/finance/taxes/taxbrackets.aspx
to manually create a table that you can use to find (using MATCH an INDEX) the income for a "Single Filer" to find their Tax Rate.
For example,
 if you enter $10,000 in A4, B4 should automatically show 15%.
 if you enter $450,000 in A4, B4 should automatically show 39.6%.
Note: you don't have to use cells A4 and B4... use any cells you want.
 Use the Distribution of household income data from
Household_income_in_the_United_States
to figure out the total tax for each income group.
Assume that everyone in an income range makes the mean income.
Also assume that everyone is single and has no deductions.
For example, according to the data:
 There are 6363 thousand households with income between $25,000 and 29,999.
 The mean income for this group is $27,101.
 According to the 2017 tax bracket data, a household that makes $27,101 has a tax rate of 15%
 So the total tax for all the 6363 thousand households with income between $25,000 and $29,999 would be 6,363,000 * $27,101 * 15%
Repeat this for each income range.
Use MATCH and INDEX to determine the tax rate for each group.
Sum the total tax for each group to find the total tax for all groups.
 According to National Priorities,
In fiscal year 2015, the federal budget was $3.8 trillion.
 Adjust your Tax tables in any manner you consider most "fair" so that the total tax for all groups equals about $3.8 Trillion.
 Print your spreadsheet(s). Be ready to pass in your printout and to show me your spreadsheet at the beginning of class.
Be sure to print BEFORE class. Anything printed after class starts will not be accepted.
 Links for class:
Due Thursday, March 23:
 Start a new college?
 Start with the Potential College Population workbook we created last class.
This workbook has sheets for CT, NH, NY and MA. It also has a sheet for the state Totals, the College Participation Rate
for each state, and state abbreviations. We had incorporated the participation rate only into the sheet for CT.
 Add sheets for two additional states...
 pick one that starts with the first letter in your first name. ex. My first name starts with "A" so I might pick "Alaska."
 pick another that starts with the first letter in your last name. ex. My last name starts with "C" so I might pick "California."
 If there is no state that starts with the right letter, pick the next state in the list. ex. no state
starts with "B", so pick one that starts with "C."
 Make sure that the appropriate numbers from the two new states get correctly added to the numbers in the Totals sheet.
 As Tim astutely pointed out, the College Participation Rate is for High School graduates and... the High School
graduation rate will vary from state to state.
 Find a table for the High School Graduation Rate by State on the Web. Create a new sheet in your workbook with that data.
 Use VLOOKUP to put the appropriate HS Graduation Rate and College Participation Rate at the top of each state
sheet. Make sure they are labeled appropriately.
 Incorporate both the High School Graduation Rate and the College Participation Rate into the Potential College
Population numbers for each state.
ex. NH had 16,731 people age 15 in 2015. If the HS graduation rate
for NH is 88.1% and the college participation rate for NH is 64.3% then the potential college population for NH when
those people turn 18 would be 16,731 * 88.1% * 64.3%.
 Make sure your totals and your graph reflect your new numbers.
 Print your "Totals" worksheet and your "two additional states" worksheets. Put your name on them and be
ready to pass them in at the beginning of class.
 Be ready to show me your work at the beginning of class.
 If you'd like to see more tutorials on VLOOKUP check out Contextures Excel VLOOKUP Function Videos
 Links for class:
 You may find this interesting:
Due Thursday, March 16:
 No Class  Spring Break
 Visit Iceland if possible... does that make your trip tax deductible?
Due Thursday, March 9:
 Prepare a onepage presentation in Excel for a group of investors thinking about starting a new forprofit college.
Another group started a college in 2000 and it has done very well. They want to know how the number of
potential traditional college students (those who enter college at age 18) will change over the next 10 years.
 Download Annual Estimates of the Resident Population by Single Year of Age and Sex for the United
States, States, and Puerto Rico Commonwealth: April 1, 2010 to July 1, 2015
2015 Population Estimates
 Go to
Annual Estimates of the Resident Population by Single Year of Age and Sex: April 1, 2010 to July 1, 2015
 The top of the table should look about like this:
 Select 2015 in the "Versions of this table are available for the following years:" box on the left.
 Click "Download" from the "Actions" bar at the top
 Select the "Microsoft Excel (.xls)" radio button and click OK
 Wait 'till the "Download" popup dialog box says "Your file is complete." Then click the
"Download" button in that box.
 DoubleClick on the Downloaded file (PEP_2015_PEPSYASEX.xls) to open the download in Excel.
 Based on this data, use the column labeled 2015 Both Sexes to determine how the pool of potential students entering college (the number of people
who will be 18 years old in 2018, 2019, etc.) in the United States will change over the next 10 years?
 Create a presentation quality spreadsheet in Excel to display your results and recommendations to the group of investors... and to the class.
You may want to review the following tutorials on
Office 365: Excel Essential Training with Dennis Taylor
at Lynda.com:
 Challenge Question (extra credit): The presentation above is for the entire United States.
The group would now like to ask the same question about New England.
You are to prepare another presentation for them.
The census data does not have a table for New England so you will have to aggregate the data for the individual states.
You can get the data for an individual state by using the "Geography:" dropdown on the
Census Site.
As you work, keep in mind that they may ask you to add another state, such as New York and/or New Jersey,
to the six New England states.
They might also ask you to drop a state, such as Maine, NH or VT from your analysis.
If they do, you will want to be able to respond quickly (within 15 seconds).
Note:
The numbers from the census are actually imported as text. If you add or subtract them (=X7X22) they are evaluated as numbers.
However, if you use a function such as SUM, they are not evaluated as numbers and the function will return 0.
So, before you can use the SUM function on them, you will have to convert them from text to numbers.
You can do this using the NUMBERVALUE function. ex.
If cell X7 has the characters "123" and you type =NUMBERVALUE(X7) in cell X107, then cell X107 will have the number 123...
which will work properly in a SUM function.
You'll find a more extensive explanation as well as other options in
Three Ways to Convert TextBased Numbers to Values.
 Create a spreadsheet that looks like the one below:
Use a nested IF statement to calculate the appropriate Shipping Charge for each order.
 Take handwritten notes in your notebook and follow along with the Exercise Files as you work through the following tutorial
on Office 365: Excel Essential Training with Dennis Taylor
at Lynda.com
 10. IF, VLOOKUP and Power functions
 Getting approximate table data with the VLOOKUP function
 Getting exact table data with the VLOOKUP function
 Use a VLOOKUP to calculate the Shipping Charge for each order.
Note: you may need to modify the Shipping Rate Table to get this to work.
 Save your spreadsheet(s), print them, and bring them to class. Be ready to pass in your printouts and to show me
your spreadsheet at the beginning of class. Be sure to print BEFORE class. Anything printed after
I arrive will not be accepted.
Due Thursday, March 2:
 Review the following tutorials
on Office 365: Excel Essential Training with Dennis Taylor
at Lynda.com
 9. Multiple Worksheets and Workbooks
 Using the workbook we created in class (see In Class for Feb. 23 below):
 Format all the worksheets so that their format looks exactly like the one's below. Note: your numbers should be
different.
 Create the Portfolio Total worksheet so that it's format looks exactly like the one below.
 Be sure to use formulas that will adjust automatically if you add another portfolio.
 Add a new Portfolio worksheet with your name... mine would be Al's Portfolio. Put it between Bob's Portfolio
and Jane's Portfolio. Make sure your numbers are automatically added to the totals on the Portfolio Total
worksheet.
 Take handwritten notes in your notebook and follow along with the Exercise Files as you work through the following tutorial
on Office 365: Excel Essential Training with Dennis Taylor
at Lynda.com
 10. IF, VLOOKUP and Power functions
 In Class we used a format to display "Good Job" if there was a gain or
"Needs Improvement" if there was a loss in the cell next to the number for Portfolio Percent Gain on the
Jane Portfolio worksheet.
 Obtain the same result using an =IF statement on the Bob Portfolio worksheet.
 On the Mary Portfolio worksheet display:
 "Outstanding" if the gain is over 7%
 "Good Job" if the gain is 0% up to and including 7%
 "Needs Improvement" if there is a loss.
 On the (Your Name) Portfolio worksheet display:
 "Outstanding" if the gain is over 7%
 "Good Job" if the gain is 0% up to and including 7%
 "Needs Improvement" if the loss is 0% up to and including 7%
 "See Me" if the loss is more than 7%.
 "Show Formulas" on the Portfolio Total worksheet and the (Your Name) Portfolio worksheet. Print both. Make
sure your name is on them. Be ready to show them to me and to pass them in at the beginning of class.
Due Thursday, Feb. 23:
 As announced in class, finish studying Formatting Numbers, Dates, and Times.
Review the examples we did in class. Go through all the other examples, experimenting with each as we did in class.
Take notes in your notebook... in case we have a quiz. This should take at least an hour, perhaps two.
 Take handwritten notes in your notebook and follow along with the Exercise Files as you work through the following tutorials
on Office 365: Excel Essential Training with Dennis Taylor
at Lynda.com
 9. Multiple Worksheets and Workbooks
 Note: If you are using a MAC you'll have to use Excel on the RCloud for the rest of this assignment.
The WEBSERVICE function does not work on Excel 2016 for MAC.
 In one workbook, create three spreadsheets like the one below: One for AAPL, one for MSFT and one for GOOG.
Use the information in the tutorials (chapter 9) for help copying the AAPL sheet for MSFT and GOOG.
 Save your spreadsheets, print them, and bring them to class. Be ready to pass in your printout and to show me
your spreadsheets at the beginning of class.
 In Class:
 Create three more spreadsheets like the one below in your workbook, one for Bob, one for Mary and one for Jane.
Again, you should find the tutorials (Chapter 9) helpful.
 The data in each green cell must come from the appropriate "Stock Data" spreadsheet (yellow) cell, not directly from the Web.
 Fill the orange cells with any data you like... you can make it up. Each person's numbers should
be different... Mary's numbers should not be the same as Bob's numbers.
 The numbers in each grey cell should be calculated. They must change if you change a number in an
orange cell.
 Create one more spreadsheet like the one below in your workbook.
This spreadsheet will show the totals from the three Portfolio spreadsheets (Bob, Mary and Jane).
 The numbers in dark green must be totals for the corresponding numbers for Bob, Mary and Jane. ex.
If Bob has 200 shares of GOOG, Mary has 100 shares of GOOG and Jane has 20 shares of GOOG; then the number of GOOG shares on this sheet should be 320.
 Note: Your numbers will almost certainly be different than these... because you will probably pick different
numbers for the orange cells (above) than the ones I picked.
 Add a comment to this sheet. If the author shows up as someone else, just delete it
and type in your name.
 You should now have 7 sheets in your workbook:
 Links for class:
Due Thursday, Feb. 16:
 Make sure you have completed the following tutorials
on
Office 365: Excel Essential Training
with Dennis Taylor
at Lynda.com. Take handwritten notes in your notebook and follow
along with the Exercise Files.
 4. Formatting
 5. Adjusting Worksheet Layout and Data
 6. Printing
 7. Charts
 Create an Excel worksheet that looks like this
Numbers in yellow must be calculated.
 Your calculated numbers may differ from mine. In fact, some of the math on my worksheet seems wrong (1,391.01 +
37.97 = 1,428.98 not 1,428.99).
See if you can explain why. Can you get your calculated numbers to match mine?
 Using the data on your worksheet (above), add a chart that looks exactly like this... get as close as you can.
You may find
this useful.
 Take handwritten notes in your notebook and follow along in Excel as you read and study
Find or Remove Duplicate in Excel – 3 Simple Ways
 Complete this conditional formatting problem.
 Create a new workbook
 Generate 30 random numbers between 1 and 15
 Copy them and "Paste Values" into another column... so they don't change... as we did in class.
 Use Conditional formatting to:
 Highlight all the numbers that appear exactly twice in one color
 Highlight all the numbers that appear exactly three times in another color
 Highlight all the numbers that appear 4 or more times in a third color
 Challenge problem (optional):
 Walkenbach has an interesting article on his site that describes how to
compare two lists using conditional formatting. The technique he uses is similar to the one
in the Conditional Formatting problem above. Create a spreadsheet with two columns of numbers. See if you can modify the technique Walkenbach uses, making it more like the
Conditional Formatting problem above, to compare the numbers in the first column to the numbers in the second. Your spreadsheet should look something like this.
 Save your spreadsheets, print them, and bring them to class. Be ready to pass in your printout and to show me
your spreadsheets at the beginning of class.
 Links for class:
Due Thursday, Feb. 9:
Due Thursday, Feb. 2:
 Take handwritten notes in your notebook and follow along with the Exercise Files as you work through the following tutorials
on
Office 365: Excel Essential Training
with Dennis Taylor
at Lynda.com
 Introduction
 1. Getting Started with Excel
 2. Entering Data.
 3. Creating Formulas and Functions
 Links for class:
 Check this out: