Reference no: EM131479562
Assignment: Heavy Rollers Bowling Team
Project Description:
You've been asked by your friend, Ronnie Roundmarble, to help him track and analyze the performance of his bowling team, the Heavy Rollers, for the upcoming season of the Tenpins Winter League. You have a start, but now have to finish the workbook. A few important facts are required. The league season consists of ten matches. Every week, a bowler's average must be calculated including all prior games for the season. A bowler's handicap is calculated as his (current average subtracted from the League Basis Average) multiplied by the Handicap Percentage Factor. Team handicap is the average of all players' handicaps in a given week. The league is currently four weeks into the season, so you have real data for four weeks and test data for the last six weeks.
Instructions
1 Start Excel. Open the file named e02md02_grader_a2_HeavyRollr.xlsx. Save the file with the name e02md02_grader_a2_HeavyRollr_YourOffice.
2 On the Team Stats worksheet, format the range B5:L9 as a table with headers using the style Table Style Light 21. Sort the table by Team Member in ascending order, and then convert the table back to a range.
3 On the Team Stats worksheet, in cell C13, calculate bowler averages for each week of the season using the formula =ROUND(AVERAGE($C6:C6),0).
4 On the Team Stats worksheet, use Auto Fill to copy the formula in cell C13 through cell L13 without formatting. Use Auto Fill to copy the range C13:L13 down through row 16 without formatting.
5 On the Team Stats worksheet, calculate the Team Average for each week and the Average for each bowler in the range C17:L17. Format the cell range C17:L17 to display 0 decimal places.
6 On the Parameters worksheet tab, create the named range BasisAvg as cell B3. Create the named range PercentFactor as cell B4.
7 On the Team Stats worksheet, in cell C21, calculate the handicap for each bowler for each week using the formula =ROUND((BasisAvg-C13)*PercentFactor,0).
8 Use Auto Fill to copy the formula in cell C21 through cell L21 without formatting. Use Auto Fill to copy the range C21:L21 down through row 24 without formatting.
9 On the Team Stats worksheet, calculate the Team Handicap for each bowler for each week in the range C25:L25. Format the range C25:L25 to display 0 decimal places, if necessary.
10 On the Team Stats worksheet, extract each bowler's first name to make a VLOOKUP search easier: Unhide column A. Type Annie into cell A6. Select cell range A6:A9 and then apply Flash Fill. Copy cell range A6:A9 and paste to cells A13 and A21. Hide column A.
11 On the Team Stats worksheet, type =VLOOKUP(C27,A6:B9,2,FALSE) into cell B28. Type Sam into cell C27. The Bowler Analysis information should update with Sam Splitter's information.
12 On the Team Stats worksheet, create a chart that displays individual bowler's performance. Insert a Combo Chart, Clustered Column - Line with source cell range B28:L31 in the Team Stats worksheet. Click the Chart Title and then type = in the Formula bar. Then click cell B28, and press ENTER. Click Bold. Move the Combo Chart to a New Sheet named Bowler Performance.
13 On the Bowler Performance worksheet, on the Combo Chart, add a Secondary Axis for the Handicap line. Add a Secondary Vertical Axis Title and type Handicap as the Title.
14 On the Team Stats worksheet, delete the contents of cell range G6:L9 to clear the last six weeks of test data, be sure not to remove any cell formatting. Apply AutoFit Column Width to columns B:L. Turn off gridlines.
15 On the Documentation worksheet: Insert the date 12/31/2015 into cells A2, A4, and A16:A18. Type Your Office into cells B2, B4, and C16:C18. Type Bowler Performance into cell B16.
16 Save the workbook. Ensure the worksheets appear in the following order: Bowler Performance, Team Stats, Parameters, and Documentation. Close the workbook and then exit Excel. Submit the workbook as directed.
Attachment:- mosken_e02md02_grader_a2_heavyrollr.rar