Allow 2-4 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.
Cell C3 will show the year that name first appeared in any Female Rank
Cell D3 will show its rank when it first appeared.
Cell E3 will show the year that name last appeared in any Female Rank
Cell F3 will show its rank when it last appeared.
For example, Sophia first appeared in 2009
as Rank 4 and last appeared in 2016 as Rank 4. So...if you enter Sophia in B3, then C3 will show 2009, D3 will show
4, E3 will show 2016 and F3 will show 4 .
Challenge:
If cell B3 has the word "Male" show the data as above for male names.
If cell B3 has the word "Female" show the data for female names.
Check your work by changing some names, both male and female, to Sam.
Due Thursday, Oct. 26:
Complete the name histogram (column chart) we did at the end of class:
See if you can enter other characters using this approach. Can you put AŁą in a cell using only the Alt key
and the numeric keypad? You may find this
useful. Note: make sure NUM Lock on your keyboard is On.
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, 2016
2016 Population Estimates (a better version of the data we downloaded in class).
Prepare a one-page presentation in Excel for a group of investors thinking about starting a new for-profit 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.
Due Tuesday, Oct. 10:
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
Getting exact table data with the VLOOKUP function
Using the COUNTIF family of functions
Create a spreadsheet that looks like the one below:
Use a nested IF statement to calculate the appropriate Shipping Charge for each order.
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, Oct. 5:
Using Your Portfolio Workbook (see Thursday, Sept. 28 below):
Add a new Portfolio worksheet with your name... mine would be Al's Portfolio.
Put it between Bob's Portfolio
and Jane's Portfolio.
Fill in your "Number of Shares" and "Price Paid" numbers (make them up). Your sheet should now
look like "Bob's Portfolio", but with different numbers.
Make sure the numbers from your new sheet are automatically added to the totals on the Portfolio Total
worksheet. By automatically I mean that you should not have to adjust the formulas on the
total worksheet. i.e. If Bob has 200 shares of AAPL, Mary has 100, you have 100 and Jane has 100, then
the number of shares of AAPL on the Total worksheet should be 500. Hint: Make sure you are using 3D
formulas on your total sheet.
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
Use 3-D formulae to complete the Portfolio Total spreadsheet we started in class.
Create a spreadsheet that looks exactly like this:
Cells C3 thru E26 should all contain =RANDBETWEEN(50,100) Your numbers will be different from those shown above... in fact, they will all change any time you do
anything to the sheet.
Pay particular attention to alignment, cell borders and cell width.
Put your name in cell A1.
Extra credit: show the same sheet (2 digits for monthly sales) but using RANDBETWEEN(50000,100000). i.e.. Sales in Thousands.
Save your spreadsheet, print it and bring it to class. Be ready to pass in your printout and to show me
your spreadsheet at the beginning of class.
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 R-Cloud for the rest of this assignment.
The WEBSERVICE function does not work on Excel 2016 for MAC.
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.
Note: You'll have to remove NUMBERVALUE from the formula for the Name... since it's not a number.
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:
Save your spreadsheet(s), print your Totals spreadsheet and bring it to class. Be ready to pass in your printout and to show me
your spreadsheet at the beginning of class.
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
7. Charts (just: Formatting Charts and Working with axes, titles and other chart elements)
Here's an opportunity... OK it's a required opportunity... you get to review your Micro-Economics AND practice your Excel!
As you watch the videos, create a spreadsheet that has:
All the tables shown in each video... such as the Chocolate Bar and Fruit tables in the first video, . Be sure to
use a formula or function to calculate everything that can be calculated.
All the charts (graphs) shown in each video. Feel free to add more data to the tables to get the charts to look
right.
Save your spreadsheet(s), print them, and bring them to class. Be ready to pass in your printout and to show me
your spreadsheet at the beginning of class.
And, for additional inspiration, I thought you might enjoy this...
Due Thursday, Sept. 21:
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
4. Formatting
5. Adjusting Worksheet Layout and Data
6. Printing
7. Charts (just the first two items: Creating Charts and Exploring Chart Types)
Take handwritten notes in your notebook and follow along (duplicate their example) as you read the first part of
Using conditional formatting to
highlight dates (up to Highlighting weekends).
Enter 40 consecutive dates in column D starting with 8/29/17.
Use Conditional Formatting to:
Highlight dates from last month in yellow
Highlight dates in the current week in Red with White letters
Highlight today's date in Green with White letters.
It should look something like this... if today is the 14th:
Note: Don't just use the font
section of the ribbon bar to change the colors... you have to do it with conditional formatting.
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
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
As you go through the tutorials above, look for a quick way to complete the addition table we started in class.