MICe ASSIGN #3: Formulas & Charts

 

REQUIREMENTS

 

Summary
I’ll be grading your work based on what you upload to the CANVAS tool. Name the file exactly like I require (below) or you will lose points.

 

This assignment will focus on Formulas & Charts, in MS Excel 2013.

 

Format/modify one (1) worksheet in MS Excel 2013 using the text I provide in the Original Text link below as a starter. Format and personalize the page to look like the Required Outcome. DUE Thursday, February 26, by 11:55pm on the CANVAS tool. 25 points.

 

Click for ORIGINAL TEXT

 

Click for REQUIRED OUTCOME - note that YOUR colors and table may look different.

 

 

To Do

PART 1: WORKSHEET SET-UP.

  • OPEN the Required Outcome so you can see the layout of what you are aiming to complete. NOTE that YOUR colors and table may look different.
  • Download the Original Text, and save it as a new file called assign03firstinitiallastname.xlsx in your USB Flash drive's MIC102e/assignments folder. Example: assign03lbothell.xlsx.
  • If your workbook opened with more than one worksheet, Delete the extra worksheets.
  • Name the one remaining worsheet Italy Roster.
  • Set the worksheet margins to Narrow.
  • Set the worksheet orientation to Landscape.
  • Set worksheet size to Legal.
  • Change worksheet's theme to whichever theme you like.
  • Change to the Page Layout view.
  • Click "Click to add Header", and type MIC102e ASSIGN 3. Format it to be 18 point, bold, and choose a dark Grayish color.
  • Click "Click to add Footer", and type your full name, and a Dash after it. Then, using the Header & Footer Tools contextual ribbon, also Insert the Page Number. Finally, Format it to be 12 point.
  • Select Cells A5-M23 and change them into an Excel Table format. Make sure to choose "My Table Has Headers". DO NOT SELECT Rows 24 and 25.
  • Select the new table, and Use the Table Tools contextual menu/ribbon to give it the name ItalyRoster.
  • Use the Table Tools contextual menu/ribbon to change the Table Style to whatever style you like - just make it READABLE.
  • In Cells A6-A23, Add a comma after each of the last names.
  • IF any of the email addresses in Cells B6-B23 are active hyperlinks, Remove remove the "active hyperlinks".
  • Select Cells C6-C23 and change the cell format to Special, Phone Number. Be sure the Locale offered is English (United States).
  • Select Cells E6-E23 and use conditional formatting to change the cells that read NO to RED with RED TEXT.
  • Select Cells F6-F23 and change the cell format to Number, no decimals.
  • Select Cells H6-H23 and change the cell format to Currency, 2 decimals. Do the same for cells 6-23 of Columns J and K.
  • HOT!!! Select Cell I3, and right-click to choose Define Name, and type TDAYS. This is Critical!

 

 

PART 2: FORMULAS/FUNCTIONS.

  • Check the Required Outcome to verify you are on target.
  • BE SURE to use formulas I have placed/referred to in Cells B28 through B31
  • In the Cell I3, Replace the phrase CalcRange with the actual CalcRange formula you see in Cell B28. To 'Activate' the formula, Remove the quote marks around it after you paste it into Cell I3. Make sure to Format the result as Number, no decimal points.
  • In Column I, look for the cells corresponding to the Red "NO's" you already conditionally hilighted in Column E. In ONLY those corresponding cells, Replace the phrase CalcSolo with the formula you see in Cell B29. To 'Activate' the formula, Remove the quote marks around it after you paste it into Cell I6. Make sure to Format the result as Currency, 2 decimal points.
  • In any remaining empty cells in Column I, Type 0. Make sure to Format the result as Currency, 2 decimal points. IGNORE any error message you might see about an inconsistent formula.
  • In Cell L6, Use the phrase Sum with the Autosum function. Make sure to Format the result as Currency, 2 decimal points.
  • Copy the active formula in Cell L6 and paste it into Cells L7-L23. Make sure to Format the result in Cells L6-L23 as Currency, 2 decimal points.
  • NOTE THAT ALL Cells H6-L23 should now be formatted as Currency, 2 decimal points.
  • In Cell M6, Replace the phrase CalcComments with the CalcComments formula you see in Cell B30. To 'Activate' the formula, Remove the quote marks around it after you paste it into Cell M6. This forumla should populate Cells M7-M23 automatically, but if it does not, Copy the formula from Cell M6 into Cells M7-M23. You will only see the resulting comment 3 times.
  • NOTE IF Excel adjusts the formula so that it does not work right when you paste it in - such as by removing one or more of the " marks, you will need to correct the formula. The LIVE formula should look like THIS: =IF(E6="No","$35/night added charge."," ")
  • In Cell H24, Calculate the sum of Cells H6-H23. Make sure to Format the result in as Bold, Currency, 2 decimal points. Note, make sure your Sum calculation is for H6:H23, NOT for CoreCost.
  • Do the same step, respectively, for Cells I24, J24, K24, and L24. Make sure to Format all their results in as Bold, Currency, 2 decimal points.
  • In Cell H25, Calculate average amount of Core Cost fees per student. You should divide the total in Cell H24 by the actual number of students (count them). Make sure to Format the result in as Bold, Currency, 2 decimal points.
  • Copy the formula from H25 into Cells I25, J25, K25, and L25. Make sure to Format their results in as Bold, Currency, 2 decimal points.

 

 

PART 3: 1 PIVOT TABLE. MUST BE DONE IN EXCEL 2013 - MAC WON'T WORK RIGHT.

  • Check the Required Outcome to verify you are on target.
  • Insert 20 rows BETWEEN Rows 34 and 35 on the worksheet. This space will give you room to add a pivot table.
  • Select all the column headers and data in your table - Cells A5-M23.
  • Use Insert/Pivot Table. In the Pop-Up Wizard, Select Existing Worksheet, AND then Click your cursor on Cell A35, then Choose OK.
  • In the "Pivot Table Fields" Panel that opens at the right side of your screen, Choose the check boxes for Full Name and Side Fee.
  • Close the "Pivot Table Fields" Panel.
  • Select all the Side Fees and the Grand Total number, and Format their results in as Currency, 2 decimal points.

 

 

PART 4: 2 CHARTS. MUST BE DONE IN EXCEL 2013 - MAC WON'T WORK RIGHT.

  • Check the Required Outcome to verify you are on target.
  • For CHART 1: Insert 25 rows BETWEEN Rows 56 and 57 (approximately, if your worksheet is spaced like the Required Outcome). This space will give you room to add the first chart.
  • Select all the column headers and data in your table - Cells A5-M23.
  • Use Insert/Recommended Charts. In the Pop-Up Wizard, Select the "Stacked Column" recommended chart, then Choose OK.
  • Drag the chart that appears on your screen down so the upper left corner is set at Cell A57. Then, Resize the chart so that its lower right corner ends at approximately Cell J79.
  • The chart looks REALLY weird, so you need to fix it. Right-click the chart, and choose Select Data. On the left-hand pane, Uncheck ALL items except CoreCost, SoloAdd, EarlyRm, and SideFee, then Choose OK.
  • Double-click in the Chart Title, then Replace the words with Student Fees.
  • Use the Chart Tools/Design tab to change the chart color to Color 3.
  • For CHART 2: Select all of the data in your Pivot Table.
  • Use Insert/Charts and Click the little See All Charts arrow, Choose the All Charts tab, and Choose Pie/3-D Pie chart. It will look incomplete!
  • Drag the chart shell that appears on your screen down so the upper left corner is set at approximately Cell A84, below the Chart-PIE title in the spreadsheet. Then, Resize the chart so that its lower right corner ends at approximately Cell J108.
  • Right-click on the Chart, then Select Add Data Labels.
  • Use the Chart Tools/Design tab to change the chart color to Color 3.
  • To FINALIZE: Look over your spreadsheet in Page Layout view, and make sure it all fits nicely on one page width. In NORMAL view, we should be able to see the whole numbers in each of the totals columns.
  • Save regularly. Get used to using Control-S to do this.
  • Check the Required Outcome to verify you are on target.
  • Spell check the assignment.
  • Upload to CANVAS tool by the end of Thursday, February 26. At the Upload page, upload your assignment file. Verify that the correct file appears after you finish uploading.

 

 

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

 

CHECKLIST

  • Does your work include ALL the assignment requirements?
  • Have you double-checked that your assignment has all the requirements I listed in this assignment?
  • Have you uploaded your assignment before the required due date?
  • Have you emailed me at least 1 full day before the due date if you had serious problems completing the assignment?

 

 

BACK TO TOP