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.
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.
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
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.