145 points
Do all of these problems in a single Excel Workbook.
(25) When done, upload your file, named Formative01.xlsx, to the D2L Assignment Folder 'Formative01'.
Solve each problem on a separate Worksheet. Your Excel File must look like this-notice the Worksheet tab names:
(50) When done, upload your file, named Formative01.xlsx, to the D2L Dropbox Folder Formative01.
Named Ranges-Chapter 1, Problem 1
1. (10) The file Stock.xlsx contains monthly stock returns for General Motors and Microsoft. Name the ranges containing the monthly returns for each stock ("GM" and "MSFT"). Use these named ranges in a forumla that computes the average monthly return for each stock.
Lookup-Chapter 2, Problem 4
2. (10) You are thinking of advertising Microsoft products on a popular TV music program. You pay one price for the first group of ads, but as you buy more ads, the price per ad drops as described in the following table:
Number of Ads
Price Per Ad
1-5
$12,000
6-10
$11,000
11-20
$10,000
More than 20
$9,000
Write a formula that yields the total cost of purchasing any number of ads. Do the calculation for the following ad quantities: 22, 3, 7 and 13
Index-Chapter 3, Problem 4
3. (10) Use the file Product.xlsx which contains monthly sales for six products. Use the INDEX function to compute the sales of product 2 in March. Use the INDEX function in a formula that computes total sales during April.
Match-Chapter 4, Problem 3
The file MatchTheMax.xlsx gives the product ID code and unit sales for 265 different products. Use the MATCH function in a formula that yields the product ID of the product with the largest unit sales.
4. (10) Write the Product ID of that product below:
Product ID:
Text-Chapter 5, Problem 3
5. (10) The workbook QuarterlyGnpData.xlsx contains quarterly GNP data for the United States (in billions of 1996 dollars) in the format shown here. Extract this data to three separate columns, where the first column contains the year, the second column contains the quarter number, and the third column contains the GNP value.
Dates-Chapter 6, Problem 2
6. (10) What is the serial format for February 14, 1950?
Serial format:
Dates-Chapter 6, Problem 8
7. (10) How many workdays, excluding Christmas, New Year's Day and the 4th of July, are there between July 10, 2005 and August 15, 2006?
Number of workdays: