MIS 462 Summer2022 Summative Assignment

Email Address: First Name: Last Name:
Semester: Class: StarID:
Section: Assignment:  
 


1000 points

Problems, Chapters 19 to 26 and 87


Do all of these problems in a single Excel Workbook.
Solve each problem on a separate Worksheet.
Your Excel File must be setup like you did in Formative01 with appropriate Worksheet tab names.


(200) When done, upload your file, named Summative03.xlsx, to the D2L Assignment Folder 'Summative03'.

Counting Cells-Chapter 19 Problem 7

Refer to the file Rock.xlsx

(50) 1. For the cell range D4:G15, count the cells containing a numeric value.

(50) 2. Count the number of blank cells.


(50) 3. Count the number of nonblank cells.

Summing Cells-Chapter 20 Problem 2

Use the file Makeup.xlsx to answer this question:

(50) 4. Use the SUMIF function to determine the total revenue earned before December 10, 2005

Offset-Chapter 21 Problem 1

5. (50) The file C21p1.xlsx supplies data about the units sold for 11 products during the years 1999–2003.
Write a formula using the MATCH (refer to Chapter 5) and OFFSET functions that picks up the sales of a given product during a given year.

Indirect-Chapter 22 Problem 2

6. (50) The workbook P22_2.xlsx contains data for the sales of five products in four regions (East, West, North, and South).
Use the INDIRECT function to create formulas that enable you to easily add up the total sales of any combination of consecutively numbered products, such as Products 1–3, Products 2–5, and the like.
Paste the forumla below:


Conditional Formatting-Chapter 23 Problem 1

Using the data in the file SandP.xlsx, use conditional formatting in the following situations:

Format in bold each month in which the value of the S&P increased and underline each month in which the value of the S&P decreased.

Highlight in green each month in which the S&P changed by at most 2 percent.

Highlight the largest S&P value in red and the smallest value in purple.

(50) 7. Make a screen shot of Conditional Formating Rules Manager dialog box for the S&P values, save it in a convenient location as you will combine it into one pdf file when you complete this assignment.
Refer to Assignment Formative00, Exercise 7 for details.

Conditional Formatting-Chapter 23 Problems 11, 12, 13

The file Nbasalaries.xlsx contains salaries of NBA players in millions of dollars.
Set up data bars to summarize this data.
Players making less than $1 million should have the shortest data bar, and players making more than $15 million should have largest data bar.

(50) 8. Make a screen shot of Conditional Formating showing the data bars, save it in a convenient location as you will combine it into one pdf file when you complete this assignment.
Refer to Assignment Formative00, Exercise 7 for details.

Set up a three-color scale to summarize the NBA salary data.
Change the color of the bottom 10 percent of all salaries to green and the top 10 percent to red.

(50) 9. Make a screen shot of Conditional Formating showing the three color scale, save it in a convenient location as you will combine it into one pdf file when you complete this assignment.
Refer to Assignment Formative00, Exercise 7 for details.

Using the data in the file NBASalaries.xlsx, use five icons to summarize the NBA Player salary data.
Create break points at $3 million, $6 million, $9 million and $12 million.
Use five colored arrows for the icon sets.

(50) 10. Make a screen shot of Conditional Formating showing the first few cells, save it in a convenient location as you will combine it into one pdf file when you complete this assignment.
Refer to Assignment Formative00, Exercise 7 for details.

Tables-Chapter 25 Problem 7

(50) Use the data in the file NikeData.xlsx, which contains quarterly sales revenues for Nike.
Create a table, named 'NikeData'
Choose an appropriate format.
Create a chart, 2-d columnar.

Add the following data:

Year/quarter Revenue
2001 1 3000
2001 2 3100
2001 3 3200
2001 4 3800

(50) 11. Make a screen shot of Conditional Formating showing the table and the 2-d columnar graph depicting the new data entered, save it in a convenient location as you will combine it into one pdf file when you complete this assignment.
Refer to Assignment Formative00, Exercise 7 for details.

Controls-Chapter 26 Problem 1, spin buttons

Add a spin button to the car NPV example (NPVspinners.xlsx) that allows the tax rate to vary between 30 and 50 per cent.

(50) 12. Make a screen shot of the tax rate spinner control set to 34 percent, save it in a convenient location as you will combine it into one pdf file when you complete this assignment.
Refer to Assignment Formative00, Exercise 7 for details.

Chapter 87-Array Formulas

(100) 13. Use the workbook ArrayProblem.xlsx

Enter an array formula in cell D11 that will calculate the grand total wages owed. Do not create any intermediate formulas.

(100) 14. Combine your six screen shots from the previous exercises in order into one .pdf file named Summative03ScreenShots.pdf and upload this file to the D2L Dropbox folder 'Summative03 Screen Shots'