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'