View the step-by-step solution to:

Office 2013 - myitlab:grader - Instructions Exploring Series Vol. 2, Chapter 8, H1 Statistical Functions Project Description: In the following...

Would you please help me with this homework? they are due tomorrow at 6 pm.

Ofce 2013 – myitlab:grader – Instructions Exploring Series Vol. 2, Chapter 8, H1 Statistical Functions Project Description: In the following project, you will use excel to perform statistical analysis of a cross section sample of an employee satisfaction survey. Instructions: For the purpose o± grading the project you are required to per±orm the ±ollowing tasks: Step Instructions Points Possible 1 Download and open the file named exploring_e08_grader_h1 , and then save the file as exploring_e08_grader_h1_LastFirst , replacing “LastFirst” with your name. 0 2 Enter a conditional function in cell I5 to calculate average satisfaction for support staff (H5). Format the results with Number format and two decimal positions. 6 3 Use the fill handle in cell I5 to copy the function down through the range I6:I9. Be sure to use the appropriate mixed or absolute referencing before copying the functions. 3 4 Enter a function in cell J5 to calculate the average salary of all support staff (H5) in the survey. 6 5 Use the fill handle in cell J5 to copy the function down through the range J6:J9. Be sure to use the appropriate mixed or absolute referencing before copying the functions. 3 6 Enter a function in cell I12 to calculate the number of Directors in the survey that have a job satisfaction level of 4 or higher. 6 7 Enter a function in cell I13 to calculate the average salary of Directors in the survey that have a job satisfaction level of 4 or higher. 6 8 Adapt the process used in the previous two steps to calculate the total number and average salary of managers that have a job satisfaction of 4 or higher in cells I16 and I17. 6 9 Enter a function in cell F4 that calculates the rank of the salary in cell D4 against the range of salaries in the data set. 6 10 Use the fill handle to copy the function down column F. Be sure to include the appropriate absolute or mixed cell references before copying the functions. 5 Updated: 07/17/2013 1 E_CH08_EXPV2_H1_Instructions.docx
Background image of page 1
Ofce 2013 – myitlab:grader – Instructions Exploring Series Vol. 2, Chapter 8, H1 Step Instructions Points Possible 11 Enter a function in cell I20 to calculate the minimum Quartile value in the list of salaries. 6 12 Use the fill handle to complete the remaining quartile values in cell range I21:I24. Be sure to include the appropriate absolute or mixed cell references before copying the functions. 5 13 Enter a function in cell H27 to calculate the correlation of column D and E. 12 14 Format the results as Number Format with two decimal positions. 5 15 Click the DATA tab and select Data Analysis. Select Descriptive Statistics and click OK. Complete the input criteria using the salary data in column D. Set the Output functions to display on a new worksheet. (Hint: be sure to output Summary Statistics). 12 16 Name the newly created worksheet Descriptive Statistics . 3 17 Click the DATA tab and select Data Analysis. Select Histogram and click OK. Use the salaries in column D as the input range. Use the quartiles in the range I20:I24 as the bin range. Output the data in cell H29. Be sure to include a chart with the output. 10 18 Move the Employee Satisfaction worksheet to display first in the workbook. Ensure that the worksheets are correctly named and placed in the following order in the workbook: Employee Satisfaction; Descriptive Statistics. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0 Total Points 100 Updated: 07/17/2013 2 E_CH08_EXPV2_H1_Instructions.docx
Background image of page 2
Ofce 2013 – myitlab:grader – Instructions Exploring Series Vol. 2 Multiple-Sheet Workbook Management Project Description: You are an accounting assistant for Downtown Theater in San Diego. The theater hosts touring Broadway plays and musicals Fve days a week, including matinee and evening performances on Saturday. You want to analyze weekly and monthly ticket sales by seating type. Instructions: For the purpose o± grading the project you are required to per±orm the ±ollowing tasks: Step Instructions Points Possible 1 Download and open the file exploring_e09_grader_h1.xlsx . Acknowledge the error, and then save the file as e09c1Theater10_LastFirst , replacing LastFirst with your name. 0 2 On the Week 1 worksheet, select the number of daily Orchestra Front tickets sold (in the range C3:G3). Create a validation rule to accept only whole numbers between 0 and the available seating limit in cell B3. Create an input message, using the seating type from cell A3 as the title text. As the input message, type Enter the number of tickets sold per day. (include the period). Create an error alert for the range using the Stop icon. Type Invalid Data as the title text and enter the error message as You entered an invalid value. Please enter a number between 0 and 86. (include the period). Repeat this procedure for each of the remaining rows in the range C4:G6. Change the maximum value in the error message based on the value in column B. 6 3 Circle invalid data entry. Change each invalid entry to the maximum number of applicable seats. 6 4 Group the four weekly worksheets. Enter a formula in cell C11 to calculate Sundays Orchestra Front revenue, which is based on the number of seats sold and the price per seat. Use relative and mixed cell references correctly. Copy the formula for the Sunday column to complete the entire range of weekdays C11:G14. 6 5 Insert formulas to calculate the weekly seating totals and the total daily revenue. Include the grand total for the week. Indent and bold the word Totals in cells A7 and A15 on the grouped worksheets. 12 6 In the Revenue per Day section of the grouped sheets, apply the Accounting number format with zero decimal places to the Orchestra Front revenue and the total revenue row. Apply the Comma Style with zero decimal places to the remaining seating revenue rows. Apply bottom double borders to the range C15:G15. 6 7 Use Format Painter to copy the formats from cells A2:H2 to cells A10:H10. Select the range A1:H15 and set the column width to auto. Ungroup the worksheets. Display the Week 4 worksheet and fill the formats of cells C1 and C9 from the Week 4 worksheet to the October worksheet without copying the content. 8 8 On the Documentation worksheet, create a hyperlink from the Week 1 label to cell A1 on the Week 1 worksheet. Create the hyperlinks from the remaining worksheet labels on the Documentation worksheet to the other worksheets. 6 Updated: 08/21/2013 1 E_CH09_EXPV2_H1_Instructions.docx
Background image of page 1
Ofce 2013 – myitlab:grader – Instructions Exploring Series Vol. 2 Step Instructions Points Possible 9 On the Week 1 worksheet, create a hyperlink from cell A1 back to cell A1 on the Documentation worksheet. Group the weekly and October worksheets, and then use the Fill Across Worksheets command to copy the link and formatting to the other weekly and summary worksheets. Ungroup the worksheets. 6 10 Insert a 3-D formula that calculates the total Sunday Orchestra Front revenue for all four weeks in cell C11 in the October worksheet. Copy the formula for the remaining seating types and weekdays. 6 11 Use the Week 4 worksheet to fill the revenue number formatting to the October revenue. 6 12 Enter a 3-D formula in cell C3 on the October worksheet that calculates the overall percentage of total Sunday Orchestra Front tickets sold based on the total available Orchestra Front seating. The 3-D formula must perform several internal calculations, avoid raw numbers, and use an appropriate mix of relative and mixed references to derive the correct percentage. Format the result with Percent Style. The result is 100%, based on the sum of the Sunday Orchestra Front tickets sold divided by the total available tickets for all four weeks. Copy the formula to the remaining cells in the range C3:G6. 10 13 In cell H3, calculate the average daily percent for each seating type. Do not use a 3-D formula. Format the results with Percent Style with one decimal place, and then copy the formula down through row 6. 10 14 Correct the circular reference in cell B7. 6 15 Create a footer on the Documentation worksheet with the sheet name code in the center and the file name code on the right side. Apply landscape orientation, and then center the worksheet horizontally on the printouts. 6 16 Save the workbook. Ensure that the workbooks are in the following order: Documentation, Week 1, Week 2, Week 3, Week 4, October. Close the workbook and exit Excel. Submit the workbook as directed. 0 Total Points 100 Updated: 08/21/2013 2 E_CH09_EXPV2_H1_Instructions.docx
Background image of page 2
Ofce 2013 – myitlab:grader – Instructions Excel Project EXP ECH08 H2 - Investment Banking 1.1 Project Description: You are an investment banker and you would like to put together a brief statistical report of the most-viewed stock quotes for the day. To complete this task, you will enter stock prices and create a report using the Analysis ToolPak. You will also track the same stocks on Day 2 Instructions: For the purpose o± grading the project you are required to per±orm the ±ollowing tasks: Step Instructions Points Possible 1 Start Excel. Open exploring_e08_grader_h2_Stocks.xlsx and save the workbook as exploring_e08_grader_h2_Stocks_LastFirst . 0 2 Type Symbol in cell B4 and type Last Sale Price in cell C4. 5 3 Adjust the width of column C to 13 (96 pixels). 5 4 Enter the following stock information beginning in cell B5: CTIC 2.88 AAPL 90.73 C 47.35 BAC 15.40 DNDN 2.31 12 5 Apply the Accounting Number Format to the stock prices in column C. 6 6 Create a descriptive statistics summary report using the range C4:C9 as the input range and cell E4 as the output range. Note that the first row of the input range contains labels. 14 7 Adjust the width of column E to 17 (124 pixels). 5 8 Type Symbol in cell H4 and type Last Sale Price in cell I4. 5 9 Adjust the width of column I to 13 (96 pixels). 5 10 Enter the following stock information beginning in cell H5: CTIC 2.85 AAPL 91.51 C 47.05 BAC 15.35 DNDN 2.21 12 Updated: 11/11/2014 1 Current_Instruction.docx
Background image of page 1
Ofce 2013 – myitlab:grader – Instructions Excel Project Step Instructions Points Possible 11 Apply the Accounting Number Format to the stock prices in column I. 6 12 Create a descriptive statistics summary report using the range I4:I9 as the input range and cell K4 as the output range. Note that the first row of the input range contains labels. 14 13 Adjust the width of column K to 17 (124 pixels). 5 14 Create a footer with the name Constance Fournier on the left side, the name of the worksheet in the middle, and the file name on the right side. 6 15 Save the workbook. Close the workbook and exit Excel. Submit the workbook as directed. 0 Total Points 100 Updated: 11/11/2014 2 Current_Instruction.docx
Background image of page 2
Ofce 2013 – myitlab:grader – Instructions Excel Project EXP ECH08 H3 - Reading Comprehension 1.2 Project Description: As an elementary school principal, you are concerned about students’ reading comprehension. After conducting a study, you want to calculate some general statistics and then conduct a one-way analysis of variance (ANOVA). Doing so will enable you to compare three sample group means and evaluate the variances within each group compared to the variances among the three groups. Instructions: For the purpose o± grading the project you are required to per±orm the ±ollowing tasks: Step Instructions Points Possible 1 Start Excel. Open exploring_e08_grader_h3_Stats.xlsx and save the workbook as exploring_e08_grader_h3_Stats_LastFirst . 0.000 2 In the range F2:H2, insert functions that will calculate the average for each of the groups. 12.000 3 In the range F3:H3, insert functions that will calculate the median for each of the groups. 12.000 4 In the range F4:H4, insert functions that will calculate the most repetitive value for each of the groups. 12.000 5 In the range F5:H5, insert functions that will calculate the highest value for each of the groups. 12.000 6 In the range F6:H6, insert functions that will calculate the lowest value for each of the groups. 12.000 7 In the range F7:H7, insert functions that will calculate the variance for each of the groups. Use the function that includes the .S descriptor. 12.000 8 In the range F8:H8, insert functions that will calculate the standard deviation for each of the groups. Use the function that includes the .S descriptor. 12.000 9 Format the values in the range F2:H8 with the Comma Style with three decimal places. 3.000 10 Use the Data Analysis tools to perform an analysis of variance using the range A2:C25 as the input range and E11 as the output range. 10.000 Updated: 02/20/2015 1 Current_Instruction.docx
Background image of page 1
Ofce 2013 – myitlab:grader – Instructions Excel Project Step Instructions Points Possible 11 Format the averages and variances in the ANOVA table with the Comma Style with three decimal places. 3.000 12 Save the workbook. Close the workbook and exit Excel. Submit the workbook as directed. 0.000 Total Points 100.000 Updated: 02/20/2015 2 Current_Instruction.docx
Background image of page 2
Ofce 2013 – myitlab:grader – Instructions Excel Project EXP ECH09 H2 - Pharmaceutical Sales Accountant 1.1 Project Description: You are an accountant for a pharmaceutical sales company. As part of your tasks, you compile an annual report that documents regional sales information into one standardized worksheet. As part of this process, you group the worksheets and insert descriptive rows and columns, apply formatting, and insert functions. Your last step is to create a sales summary worksheet and provide basic information for management to evaluate. Instructions: For the purpose o± grading the project you are required to per±orm the ±ollowing tasks: Step Instructions Points Possible 1 Open the downloaded workbook named exploring_e09_grader_h2_Sales.xlsx and save it as exploring_e09_grader_h2_Sales_LastFirst . 0 2 Group the five regional worksheets (not including the Summary worksheet). Insert a new row above row 1 of the existing data. Type Agent in cell A1 and apply the Heading 2 cell style to the cell. 11 3 With the worksheets still grouped, type Qtr1 in cell B1. Use the fill handle to add Qtr2 - Qtr4 in cells C1:E1. Apply Heading 3 format to cells B1:E1. 12 4 With the worksheets still grouped, type Total in cell F1. In cell F2, enter the following function: =SUM(B2:E2) . Use the fill handle to complete the summary information for the rest of column F, down to cell F19. 12 5 With the worksheets still grouped, type Total in cell A20. In cell B20, insert a function to total Qtr1 sales. Copy the function to the range C20:F20. 12 6 With the worksheets still grouped, format cells B2:F20 with the Accounting Number Format. Set the column width for the range B1:F20 to 20 . 12 7 With the worksheets still grouped, apply Heading 2 style to cell F1. Add Top and Double Bottom Borders to cells B20:F20. 11 8 Ungroup the worksheets, click the Summary worksheet tab, and then apply Accounting Number Format to cells B2:E6. 8 9 Select cells F2:F6 and press ALT+= to add totals to column F. Set the column width to 18 . 12 10 Apply 3 Flags Indicator Icon Set conditional formatting to F2:F6 10 Updated: 11/19/2014 1 Current_Instruction.docx
Background image of page 1
Ofce 2013 – myitlab:grader – Instructions Excel Project Step Instructions Points Possible 11 Save the workbook. Ensure that the following worksheets are present (in this order): North Region, East Region, South Region, MidWest Region, West Region, Summary, and Q&A. Close the workbook, and then submit the file as directed. 0 Total Points 100 Updated: 11/19/2014 2 Current_Instruction.docx
Background image of page 2
Ofce 2013 – myitlab:grader – Instructions Excel Project EXP ECH09 H3 - Weather Analyst 1.1 Project Description: As a weather analyst, you have been tracking daily high and low temperatures for Oklahoma City, Tulsa, and Lawton during June, with each city’s data stored in its own worksheet. You need to apply consistent formatting and enter formulas for all worksheets. You need to create the summary worksheet to identify the record high and low temperatures by day and identify the respective cities. Instructions: For the purpose o± grading the project you are required to per±orm the ±ollowing tasks: Step Instructions Points Possible 1 Open the downloaded workbook named exploring_e09_grader_h3_June.xlsx and save it as exploring_e09_grader_h3_June_LastFirst . 0 2 Group the city worksheets. Fill the formatting of cells A1, A2, and A5:C5 from the OKC worksheet to the other city worksheets. 6 3 Enter dates 6/1/2015 to 6/30/2015 and format them as 6/1 to 6/30 (no year displayed) in the Date column. Apply Orange, Accent 6, Lighter 60% fill to the dates. 12 4 Enter a function in cell B39 to calculate the highest temperature of the month. Enter a function in cell C39 to calculate the lowest temperature of the month. 9 5 Use a nested MATCH function within the INDEX function in cell B40 to identify the date for the highest temperature. The dataset may contain several identical highest temperatures, but the nested function will identify the first date containing the match. 8 6 Use a nested MATCH function within the INDEX function in cell C40 to identify the date for the lowest temperature. Ungroup the worksheets. 8 7 Use a Web browser to go to www.wunderground.com , a weather Web site. At the top of the page, click More, click Historical Weather and submit a search for OKC , Oklahoma. Copy the URL and create a hyperlink to this Web page for cell A3 in the OKC worksheet. Add a ScreenTip stating Click to see weather history for Oklahoma City (no period). 4 8 Create hyperlinks for the Tulsa (hyperlink text is Tulsa ) and Lawton (hyperlink text is Lawton-Fort Sill ) worksheets as well (in cell A3). Set the ScreenTips as Click to see weather history for Tulsa (no period) and Click to see weather history for Lawton (no period). Continue to use www.wunderground.com for the weather links. Check each hyperlink to ensure it works correctly. 7 9 Enter the following 3-D formula in cell B6 of the Summary worksheet: Calculate the highest temperature from the three cities for 6/1. Copy the formula down the High column. 10 Updated: 11/19/2014 1 Current_Instruction.docx
Background image of page 1
Ofce 2013 – myitlab:grader – Instructions Excel Project Step Instructions Points Possible 10 Enter the following 3-D formula in cell D6 of the Summary worksheet: Calculate the lowest temperature from the three cities for 6/1. Copy the formula down the Low column. 10 11 Enter a nested IF function in cell C6 to determine which city had the highest temperature. Based on the result returned in B6, enter the city name as OKC or Tulsa or Lawton in C6. Remember to enclose city names in double quotation marks in the function. Use Help if needed to help you understand a nested IF statement. Copy the formula down the High-City column. 6 12 Enter a nested IF function in cell E6 to determine which city had the lowest temperature. Based on the result returned in D6, enter the city name as OKC or Tulsa or Lawton in E6. Copy the function down the Low-City column. 6 13 Enter functions in the shaded Monthly Records section (below the daily data) on the Summary worksheet to identify the highest (in column B) and lowest temperature (in column D). 5 14 Enter nested INDEX and MATCH functions to identify the dates and cities for the respective highest and lowest temperatures. 5 15 Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of each worksheet. Select the option to center the worksheet data horizontally on each sheet. 4 16 Save and close the workbook, and submit the file as directed. 0 Total Points 100 Updated: 11/19/2014 2 Current_Instruction.docx
Background image of page 2
Employee StaTsTcs ID Number PosiTon Salary Job SaTsfacTon Salary Rank Summary InformaTon 1047 Sales Rep 45,855.00 3 PosiTon Average SaTsfacTon Average Salary 1085 Sales Rep 46,063.00 4 Support StaF 1102 Sale Rep 45,700.00 2 Sales Rep 1106 Sales Rep 47,572.00 3 Accountant 1165 Support StaF 30,176.00 4 Manager 1473 Sales Rep 45,993.00 4 Director 1486 Support StaF 32,666.00 1 1503 Support StaF 32,757.00 3 Directors with > 4 SaTsfacTon 1519 Sale Rep 45,674.00 5 Count 1529 Director 88,197.00 5 Average Salary 1601 Director 89,691.00 4 1603 Accountant 69,389.00 2 Managers with > 4 SaTsfacTon 1675 Sale Rep 49,617.00 3 Count 1828 Support StaF 35,230.00 3 Average Salary 2003 Director 83,240.00 5 2206 Sale Rep 42,383.00 3 QuarTle Salary 2250 Director 92,700.00 4 0 2291 Support StaF 35,242.00 3 1 2292 Sales Rep 44,854.00 2 2 2416 Director 77,846.00 3 3 2528 Manager 70,125.00 3 4 2624 Manager 73,564.00 5 2742 Accountant 62,263.00 4 CorrelaTon 3004 Support StaF 30,982.00 2 3083 Director 84,937.00 4 3161 Support StaF 32,709.00 4 3217 Sales Rep 40,449.00 3 3314 Sales Rep 45,983.00 5 3338 Support StaF 36,942.00 3 3402 Support StaF 33,852.00 5 3473 Support StaF 33,501.00 1 3638 Sales Rep 48,005.00 3 3652 Sales Rep 48,706.00 5 3782 Sale Rep 45,927.00 2 3808 Support StaF 31,632.00 5 3818 Sale Rep 49,575.00 3 3874 Support StaF 34,154.00 4 3877 Support StaF 34,775.00 3 3948 Support StaF 37,404.00 4 3969 Accountant 65,492.00 3 4239 Support StaF 32,107.00 4 4243 Support StaF 35,316.00 2 4269 Accountant 68,365.00 2 4398 Sales Rep 41,130.00 4 4541 Manager 72,862.00 3 4584 Support StaF 30,701.00 5 4652 Accountant 66,009.00 2 4771 Manager 74,767.00 4 4911 Director 81,082.00 4 4954 Director 97,159.00 5
Background image of page 1
DAY 1 DAY 2
Background image of page 1
Control CBT Only Hybrid General Stats Control CBT Only Hybrid 15 14 16 Average 11 12 14 Median 12 13 13 Mode 14 11 17 High 15 15 15 Low 14 16 16 Variance 12 11 11 Standard DeviaTon 10 10 17 12 9 14 9 14 15 14 13 12 13 13 15 13 11 11 10 13 14 12 13 14 13 12 13 10 16 11 11 15 12 9 12 18 11 10 13 8 12 11 15 11 11 13 10 12 12 10 14
Background image of page 1
Questons 1 Based on The daTa provided, which meThod do you feel is The mosT eFectve? 2 Why do you Think This meThod is mosT eFectve? 3 Which of The optons provides resulTs closesT To The mean?
Background image of page 2