Question

In: Statistics and Probability

1 Start Excel. Download and open the file named exploring_ecap_grader_a1.xlsx. 2 On the DC worksheet, select...

1

Start Excel. Download and open the file named exploring_ecap_grader_a1.xlsx.

2

On the DC worksheet, select the range A4:G4, wrap the text, apply Center alignment, and apply Blue, Accent 5, Lighter 60% fill color.

3

On the DC worksheet, merge and center the title in the range A1:G1. Apply Accent5 cell style and bold to the title.

4

On the DC worksheet, change the width of column A to 34.

5

On the DC worksheet, select the range C5:F10 and insert Line Sparklines in the range G5:G10.

6

On the DC worksheet, select the range G5:G10, display the high point sparkline marker, and change the color of the high point markers to Dark Blue.

7

On the DC worksheet, select the range G5:G10, apply Same for All Sparklines for both the vertical axis minimum and maximum values.

8

On the DC worksheet, select the ranges A4:A10 and C4:F10 and create a clustered bar chart. Apply the Color 16 chart color. Apply the gradient fill to the plot area. Do not change the default gradient options.

Note, depending on the version of Office used, the chart color may be named Monochromatic Palette 12.

9

Position the top-left corner of the chart in cell A13. Change the chart height to 6 inches and the chart width to 7 inches.

10

Change the chart title to Annual Visitors. Apply Blue, Accent 5, Darker 25% font color to the chart title and category axis labels. Change the value axis display units to Millions.

11

Apply data labels to the outside end of the 2015 data series. Apply Number format with 1 decimal place to the data labels.

12

Click the Places sheet tab, convert the data to a table, and apply Table Style Medium 6.

13

On the Places worksheet, sort the data by City in alphabetical order and then within City, sort by Sightseeing Locations in alphabetical order.

14

On the Places worksheet, add a total row to display the average of the Time Needed column. Apply Number format with zero decimal places to the total.

15

On the Places worksheet, select the values in the Time Needed column and apply conditional formatting to highlight cells containing values greater than 60 with Green Fill with Dark Green Text.

16

On the Places worksheet, apply a filter to display only fees that are less than or equal to $10.

17

On the Cities worksheet, click cell F4 and enter a formula that will subtract the Departure Date (B1) from the Return Date (B2) and then multiply the result by the Rental Car per Day value (F3).

18

On the Cities worksheet, click cell E13. Depending on the city, you will either take a shuttle to/from the airport or rent a car. Insert an IF function that compares to see if Yes or No is located in the Rental Car? Column for a city. If the city contains No, display the value in cell F2. If the city contains Yes, display the value in the Rental Car Total (F4). Copy the function from cell E13 and use the Paste Formulas option to copy the function to the range E14:E18 without removing the border in cell E18.

19

On the Cities worksheet, click cell F13. The lodging is based on a multiplier by City Type. Some cities are more expensive than others. Insert a VLOOKUP function that looks up the City Type (B13), compares it to the City/COL range (A7:B10), and returns the COL percentage. Then multiply the result of the lookup function by the Total Base Lodging (B5) to get the estimated lodging for the first city. Copy the function from cell F13 and use the Paste Formulas option to copy the function to the range F14:F18 without removing the border in cell F18.

20

On the Cities worksheet, click cell H13 and enter the function that calculates the total costs for the first city. Copy the function in cell H13 and use the Paste Formulas option to copy the function to the range H14:H18 without removing the border in cell H18.

21

On the Cities worksheet, select the range E14:H18 and apply Comma Style with zero decimal places. Select the range E13:H13 and apply Accounting Number format with zero decimal places.

22

On the Cities worksheet, in cell I2, enter a function that will calculate the average total cost per city. In cell I3, enter a function that will identify the lowest total cost. In cell I4 enter a function that will return the highest total cost.

23

On the Cities worksheet, select Landscape orientation, set a 1-inch top margin, and center the worksheet data horizontally on the page.

24

Ensure that the worksheets are correctly named and placed in the following order in the workbook: DC, Places, Cities. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

Solutions

Expert Solution


Related Solutions

1. Download the EXCEL file: Access Exercise Tables 2. Open a new blank database in ACCESS...
1. Download the EXCEL file: Access Exercise Tables 2. Open a new blank database in ACCESS and name it “Exercise-Your Name” where you replace Your Name with your name. 3. Import each worksheet in the EXCEL file into ACCESS as a separate table as follows: a. External Data Tab -> Import Excel icon b. In the dialog box browse for the destination of the excel file you saved in step 1, it should default to “import the source data in...
Chapter 13: Applying Excel: Excel Worksheet (Part 1 of 2) Download the Applying Excel form and...
Chapter 13: Applying Excel: Excel Worksheet (Part 1 of 2) Download the Applying Excel form and enter formulas in all cells that contain question marks. For example, in cell C22 enter the formula "= B10". Note: The present value factors could be computed using the built-in Excel function PV, but we recommend using the formulas in Appendix 13B. Verify that your worksheet matches the example in the text. Check your worksheet by changing the discount rate to 10%. The net...
Chapter 9: Applying Excel: Excel Worksheet (Part 1 of 2) Download the Applying Excel form and...
Chapter 9: Applying Excel: Excel Worksheet (Part 1 of 2) Download the Applying Excel form and enter formulas in all cells that contain question marks. For example, in cell B30 enter the formula "= B20". Notes: In the text, variances are always displayed as positive numbers. To accomplish this, you can use the ABS() function in Excel. For example, the formula in cell C31 would be "=ABS(E31?B31)". Cells D31 through D39 and G31 through G39 already contain formulas to compute...
Question 2. Go to the Blackboard and download the MS excel file, ‘stock_return.xlsx’. It contains a...
Question 2. Go to the Blackboard and download the MS excel file, ‘stock_return.xlsx’. It contains a year of monthly stock price data of Amazon, Pfizer, and S&P 500 (Market Index). Using the data, answer the following questions. (50 points) (1) Compute the monthly return of Amazon and Pfizer. You should get 12 monthly returns for each. To get a monthly return, you need to use previous month’s stock price. For example, Amazon’s stock return of 2018-01 will be [(Stock price...
Step 1: Edit SumMinMaxArgs.java Download the SumMinMaxArgs.java file, and open it in jGrasp (or a text...
Step 1: Edit SumMinMaxArgs.java Download the SumMinMaxArgs.java file, and open it in jGrasp (or a text editor of your choice). This program takes a number of command line arguments, parses them as ints, and then displays: The arithmetic sum of the arguments The smallest argument The largest argument If you're unsure how to pass command-line arguments to a program with jGrasp, see this tutorial. Example output of this program with the command-line arguments 1 2 3 4 5 is shown...
Download the AddValueNewArray.java file, and open it in jGrasp (or a text editor of your choice)....
Download the AddValueNewArray.java file, and open it in jGrasp (or a text editor of your choice). This program behaves similarly to the AddValueToArray program from before. However, instead of modifying the array in-place, it will return a new array holding the modification. The original array does not change. For simplicity, the array used is “hard-coded” in main, though the method you write should work with any array. Example output with the command-line argument 3 is shown below: Original array: 3...
Download the SwapMultidimensional.java file, and open it in jGrasp (or a text editor of your choice)....
Download the SwapMultidimensional.java file, and open it in jGrasp (or a text editor of your choice). This program contains two methods which you will need to write: swapRows: Swaps the contents of two rows, given a two-dimensional array and the indices of the rows to swap. swapCols: Swaps the contents of two columns, given a two-dimensional array and the indices of the columns to swap. main contains some code which will call swapRows and swapCols, for the purpose of informal...
Download the Take.java file, and open it in jGrasp (or a text editor of your choice)....
Download the Take.java file, and open it in jGrasp (or a text editor of your choice). This program will “take” a given number of elements from a given array, using the command-line arguments as an array, and the hard-coded value of 3 elements to take. Example output of this program with the command-line arguments foo bar baz is shown below: foo bar baz In the above example, three elements were taken. However, it's possible that we may request to take...
Download the AllEqual.java file, and open it in jGrasp (or a text editor of your choice)....
Download the AllEqual.java file, and open it in jGrasp (or a text editor of your choice). This program takes a number of command line arguments, converts them to integers, and then determines if they all have the same value. An example run of the program is below, using the command-line arguments 1 2 3: Are equal: false A further example is shown below, with the command-line arguments 2 2: Are equal: true In the event that the program is given...
Download the Compass.java file, and open it in jGrasp (or a text editor of your choice)....
Download the Compass.java file, and open it in jGrasp (or a text editor of your choice). This program will randomly print out a compass direction, given a seed value for produce a random number with java.util.Random. Compass directions are mapped to integers according to the following table: Compass Direction Integer ID North 0 Northeast 1 East 2 Southeast 3 South 4 Southwest 5 West 6 Northwest 7 Further details are in the comments of Compass.java. import java.util.Random; import java.util.Scanner; public...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT