Let us worry about your assignment instead!

We Helped With This Excel Programming Homework: Have A Similar One?

SOLVED
CategoryProgramming
SubjectExcel
DifficultyCollege
StatusSolved
More InfoMs Excel Help Online
084011

Short Assignment Requirements

Need 4 assignments completed. Attached are files needed to complete.

Assignment Description

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 five 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 of grading the project you are required to perform the following tasks:

Step

Instructions

Points Possible

1

Download and open the file exploring_e09_grader_h3.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

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

                                                                                                                       

Assignment Description

Multiple-Sheet Workbook Management

 

Project Description:

You have volunteered to be the statistician for your high school’s football team. As part of your assignment you will organize, and present statistics to the coach. Currently the information is stored in three different Excel workbooks: offense, defense, and special teams. You will standardize the worksheets into a summary document as well as correcting errors, and safe guarding against future issue.

 

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions

Points Possible

1

Download and open the file exploring_e09_grader_a1.xlsx. Save the document as e09c2Football_LastFirst, replacing LastFirst with your name.

0

2

Group the Passing, Rushing, Receiving, and Defense worksheets. With the Passing worksheet displayed, click cell A1 and fill the contents and formatting across all of the grouped worksheets.

10

3

With the sheets still grouped, select the range A2:G2. Add borders to all cells and fill the cells with the color Olive Green, Accent 3, Lighter 60%. Ungroup the worksheets.

10

4

Click the Rushing worksheet. Trace the error in the cell that contains the #DIV/0 error. Enter an IFERROR function that returns 0 in cell D6.

10

5

Insert a formula that references the appropriate worksheets in cell A3 on the Offensive Totals worksheet to calculate total passing, rushing, and receiving touchdowns.

10

6

Insert a formula that references the appropriate worksheets in cell B3 on the Offensive Totals worksheet to calculate total passing, rushing, and receiving yards.

10

7

Insert a formula that references the appropriate worksheets in cell C3 on the Offensive Totals worksheet to calculate total completion rate based on data in the Passing worksheet. The completion rate is calculated by dividing the total completed passes by the total attempted passes.

6

8

Click the Defense worksheet and create a validation rule for cells B3:F26 that restricts entering values to whole numbers no less than 0. Create an input message that reads the following: Enter all stats as whole numbers. (include the period). Choose the Stop style and enter the following error message: Stats cannot be less than 0 (no period).

15

9

Click the Defensive Totals worksheet and enter formulas in cells A3:C3 to calculate the total tackles, forced fumbles, and interceptions.

6

10

Click the Offensive Totals worksheet and create a hyperlink in cell C2 to cell C2 on the Passing worksheet.

5

11

Click the Defensive Totals worksheet and create hyperlinks in cells A2 (Total Tackles), B2 (Forced Fumbles), and C2 (Interceptions) to their corresponding cells on the Defense worksheet.

6

12

Create a footer on all worksheets with the sheet name code in the center and the file name code on the right side.

6

13

Apply landscape orientation, and then center the worksheets horizontally on the printouts.

6

14

Save the workbook. Ensure that the workbooks are in the following order: Passing, Rushing, Receiving, Defense, Offensive Totals, and Defensive Totals. Close the workbook and exit Excel. Submit the workbook as directed.

0

 

Total Points

100

                                                                                                                       

Assignment Description

State Information

 

Project Description:

Your niece and nephew have a test on state capitals, abbreviations, and statehood dates next week and asked for your help. You downloaded a text file containing states and statehood dates. You will create a workbook with this data and then create a web query to data containing capitals. As you get the data ready to help your niece and nephew study, you will also practice your skills in data manipulation using text functions.

 

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions

Points Possible

1

Start Excel. Download the text file exploring_e10_grader_a1.txt, the XML file exploring_e10_grader_a1.xml, and the Excel file exploring_e10_grader_a1.xlsx. Open the workbook.

0

2

In the blank worksheet in cell A1, import the text file. When using the Text Import Wizard, select appropriate options for importing a tab-delimited file and that the first row contains headers. Import the date column as Text.

10

3

Rename the sheet as Statehood.

5

4

Set these column widths: 15 for column A, 20 for column B, and 7 for column C.

5

5

Insert a column between columns B and C. Set the column width to 16.  Format the new column C with the General number type. Type Statehood Year in cell C1.

10

6

Enter a text function in cell C2 that extracts just the year from cell B2. Copy the function to the range C3:C51.

10

7

Apply bold and horizontal center alignment to the range A1:D1. Center horizontally data in column C.

5

8

Insert a new sheet named Capitals.

5

9

In cell A1 of the Capitals sheet, create a web query to http://www.stateabbreviations.us/. Select the table containing the US States, Capitals, and Government Links. Select Full HTML Formatting and then import the data.

10

10

Merge cells E2:E3 and type Capital, State.

5

11

Enter a text function in cell E4 that combines the capital and postal abbreviation like this: Montgomery, AL. The second argument in the function must include the comma and space. Copy the function to the range E5:E53.

10

12

Set the width of column E to 18. Apply the All Borders border style to the range E2:E53.

10

13

Insert a new sheet named Nicknames.

5

14

In cell A1 of the Nicknames sheet, import the exploring_e10_grader_a1.xml file.

10

15

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

0

 

Total Points

100

                                                                                                                       

Assignment Description

Whisenhunt Enterprises

 

Project Description:

Whisenhunt Enterprises is located in a metropolitan area. Denis Petrillo and Omar Vincent travel frequently for business. To help plan for flight delays, you will create a workbook that provides airline data with up-to-date arrival and departure information from the Tri-State Metropolitan Airport.

 

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions

Points Possible

1

Start Excel. Download the text file exploring_e10_grader_h3.txt, the XML file exploring_e10_grader_h3.xml, and the Excel file exploring_e10_grader_h3.xlsx. Open the workbook.

0

2

In the Airlines Codes sheet, click cell A2 and create a link to the tab-delimited text file you downloaded. Select the option that the text file has headers. Accept all other defaults.

10

3

Open Notepad, edit the e10_grader_h3.txt tab-delimited file. At the end of the last line, press ENTER, type Unified, press TAB, type UNA. Save and close the text file. In Excel, refresh the connection to the text file.

5

4

In the Departures sheet, in cell A1, create a Web query to the departures table on this Web page: http://media.pearsoncmg.com/ph/bp/bp_myitlab/departure_schedule_files/departure_schedule.htm. Maintain full HTML formatting before importing the table.

10

5

In the Filtered List sheet, create a table with headers using the range A1:E29.

5

6

In the Filtered List sheet, click cell A2. Insert a VLOOKUP function to lookup the airline in the Departures sheet, compare it to the airline table in the Airline Codes sheet, and return the code for that airline. For example, Unified will display as UNA. (The function should copy to the range A3:A29 automatically.)

10

7

In the Filtered List sheet, click cell B2. Use a text function to combine the airline code from the previous step with a space and the flight number on the respective row in the Departures sheet. (The function should copy to the range B3:B29 automatically.)

10

8

In the Filtered List sheet, click cell C2. Use a text function to display the cities from the Departures sheet where the first letter is capitalized but the remaining letters are lowercase, such as Houston. (The function should copy to the range C3:C29 automatically.)

5

9

In the Filtered List sheet, click cell D2. Enter a formula to repeat the time from the Departures sheet. Format the range D2:D29 with the Time format, such as 1:30 PM.

5

10

In the Filtered List sheet, click cell E2. Enter a text function that returns the status in title case, such as On Time. (The function should copy to the range E3:E29 automatically.)

5

11

In the Filtered List sheet, set a filter for both Houston airports with a status of On Time.

10

12

In cell A1 of the Airports sheet, create a connection to the exploring_e10_grader_h3.xml file.

10

13

Open exploring_e10_grader_h3.xml in Notepad. Copy the first Airport element and paste the copy above the original first airport element. Edit the data by typing Atlanta and ATL within the proper tags. Change the Chicago airport code to ORD. Save and close the XML file. Refresh the XML connection only.

15

14

Ensure that the worksheets are correctly named and placed in the following order in the workbook: Departures, Filtered List, Airline Codes, Airports.  Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

0

 

Total Points

100

                                                                                                                       

Customer Feedback

"Thanks for explanations after the assignment was already completed... Emily is such a nice tutor! "

Order #13073

Find Us On

soc fb soc insta


Paypal supported