CMPS 209 Exam, Spring 2012

American University of Beirut Department of Computer Science CMPS 209- Excel Exercises 0 2000 4000 6000 8000 10000 12000 Number of visits Bandwidth in MB  OPEN “Excel - DATASET .xlsx” excel sheet and use the Save As feature to save a copy of this file in your folder on the Z:// drive. The filename should be as follows: “Yourname- LabMidterm-PART3”.  Open the "Charts" sheet. This sheet shows the number of visits and the amount of bandwidth consumed per day from Decemeber 4 till Decemeber 11.  For the existing table do the following: Insert a Stacked Cylinder chart.  Copy and paste this chart in the same sheet and do the following changes to it (as shown below) :  For the title: a) Font to: Kristen ITC and its size to 18. b) WordArt style to: gradient fill - Accent 1. c) background fill to: Moderate Effect – Accent 2  Change the chart style to Style 36  Apply the Gold gradient style to the charts background  Rotate the Chart around x by 60 degrees.  Finally do the necessary changes for the Axes, Legend and border so that the chart appears as below.  On the same sheet, copy the chart you just modified and do the following, (as shown in the figure below):  Change the chart type to 3D-Line.  Change this chart‟s style to Style 48 Number of visits Bandwidth in MB 0 5000 10000 Bandwidth vs. Number of Visits Number of visits Bandwidth in MB Exercise 4  Open Sheet1 and rename it to “Days of Month”.  This sheet shows the number of visits, pages, and hits that a user has made while surfing the internet during the month of November, it also display the consumed Bandwidth (amount of data downloaded of the internet).  Insert a new Column BEFORE Column B and fill its values with days of the week (Tuesday, Wednesday…). Note start with TUESDAY. (Keep column A empty).  Merge the cells B1 and C1, B32 and C32, B33 and C33.  Apply Filling and borders to your table as shown in the figure below.  In column H (now it became I), calculate the bandwidth in MB. In order to do so, you MUST use the number in K2 (now it is L2).  Calculate the AVERAGE and TOTAL Number of Visits, Pages, and Hits. Note that you can also use the AutoFill in your calculations.  Using the value in M3, Insert a formula in I3 that calculates the “Bandwidth in MB”. Note that you can also use the AutoFill in your calculations.  Change the cell styles of all the cells in the range from D3 to F32 to number format with Comma[0].  Apply Blue Data Bars to the values under “Hits” and “Pages” as shown above.  In the „Days of Month‟ Sheet, fill the values in the comment column according to the following:  “Low” if Number of pages is less than 100,000  “High” if Number of pages is greater than 100,000  “Really high” if BOTH the number of pages is greater than 100,000 AND the number of Hits is greater than 1,100,000.  Record a macro named “Revised” with a shortcut Ctrl + N that:  Merges cells L19 to L34.  Gives the merged cell a black fill color.  Writes in the merged cell: “Sheet Revised By: “your name.”  Changes the text‟s font color to white.  Changes this text‟s orientation to -90 degrees and its vertical and horizontal alignment to Center.  Gives the merged cell a thick red border. THEN STOP RECORDING.  Copy this sheet, and rename it to “Days of Month EXTRA”.  On the copied sheet, insert a function in L6 that gives the corresponding Bandwidth in MB for the date with 7202 visits. !!!!!! SAVE YOUR WORK !!!!!! Good Luck!