E03_grader_a1_Fitness -(solution)

Want create site? With you can do it easy.

E03_grader_a1_Fitness -(solution)

YO16_XL_BU03_GRADER_PS3_AS – Fitness 1.5

Project Description:

The hotel has started facilitating fitness classes for hotel guests. Guests can register for classes before their arrival or when they arrive at the hotel. Several instructors lead the classes, and each instructor has multiple skills. The fitness center manager would like to have a workbook developed that will track class enrollment. The workbook will provide an overview of guest enrollment with some analysis, which will be used for better decision-making for future class offerings.


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



Points Possible


Start Excel. Open the downloaded Excel file named e03_grader_a1_Fitness.xlsx. Save the file with the name e03_grader_a1_Fitness_LastFirst replacing LastFirst with your last and first name.



Copy the Enrollment worksheet to the end of the workbook to make a backup copy of the data. Rename the worksheet EnrollmentBackup



On the InputData worksheet, select range B4:H8. Create named ranges using the top row as the name for your named ranges. Each range includes fitness class details.
With B4:H8 still selected, enter ClassInfo into the Name Box to name the table.



Select range B11:H14. Create named ranges using the top row as the name for your named ranges. Each range is the list of instructors that can teach individual classes.



On the Enrollment worksheet, in cell A13, insert a table using range A13:D106 and the top row as the headers.



In cell D14, enter an HLOOKUP function that will look up the class number in cell B14 and will use the ClassInfo table to return the Fee from the fifth row of the table with an exact match.



In cell E13, create a new column header named Class_Name.

In cell E14, enter an HLOOKUP function that will look up the class number in cell B14 and use the ClassInfo table to return the Class Category name from the second row of the table with an exact match.



Select A13:E106, and then name the cell range Enrollment.
With A13:E106 still selected, create named ranges using the top row as the name for the named ranges.
Copy range A13:E13, and then paste in range A1:E1 to set up the advanced filter criteria area. Enter the following filter criteria.

In cell C2, type F
In cell E2, type Yoga



In cell B6, create a DCOUNTA function to count the Student_ID field in the Enrollment table that meet the filter criteria specified in range A1:E2.

In cell B7, create a DAVERAGE function to average the Fee field in the Enrollment table that meet the filter criteria specified in range A1:E2. This will average all yoga fees collected from females.

In cell B8, create a DSUM function to sum the Fee field in the Enrollment table that meet the filter criteria specified in range A1:E2. This will total all yoga fees collected from females.



On the Report worksheet, create calculations that will help hotel employees manage the fitness class enrollments. The user will put an “x” in range E4:E10, indicating which class to report on and an “x” in range H4:H5 if employees want a report on a specific gender.

In cell A4, use a MATCH function nested in an INDEX function to retrieve the Class that was selected in E4:E10. The MATCH should find the row where the “x” is located and would be used within the INDEX to pull the associated Class value from the same row within range F4:F10.

In cell B4, use a MATCH function nested in an INDEX function to retrieve the Gender that was selected in H4:H5, looking at the “x” in column H and returning the “F” or “M” for the Gender criteria. Using a MATCH nested in an INDEX function, retrieve the gender that was selected in H4:H5. Nest the MATCH and INDEX formula inside the IFERROR function in case the user does not select a specific gender. The IFERROR should leave the cell blank if a gender is not selected.



In cell C4, create a COUNTIF formula that counts the enrollment for the named range Class that has the class number listed in A4. The range criteria should reference the Class named range.



In cell B7, create an HLOOKUP formula that will look up the Class in A4 within the ClassInfo named range and return the maximum enrollment, which is in the third row of that table. The value should be looking for an exact match.

In cell B8, create an HLOOKUP formula that will use the Class in A4 and retrieve the Class Category type from the ClassInfo named range in row 2, also looking for an exact match.



In cell B11, create an IF function to indicate the availability of reservations. If the number enrolled in C4 is greater than or equal to the maximum enrollment in B7, then “Full or Overbooked” should display. Otherwise, “Spots Available” should display.



The instructors for each class are listed on the InputData worksheet in range B12:H14. The instructors for the class in cell B8 need to be counted.
In cell B12, create a complex function that will determine the number of instructors for the class listed in A4. Use the COUNTA, INDIRECT, INDEX, and MATCH functions.



Click cell B13. Using an HLOOKUP nested in an AND function nested in an IF function, return either Split Class or Can’t Split based on business options. Two conditions are needed to determine if a class can be split. Using the ClassInfo table, one row shows if a class can be split. That condition can be determined with a HLOOKUP. The second is if there is more than one instructor as shown in cell B12. If both conditions are met, the class can be split. Otherwise, the class cannot be split.



On the Enrollment worksheet, insert a PivotTable using the Enrollment named range. Insert the PivotTable on a new worksheet named PivotAnalysis. Complete the following to create your PivotTable.
• Add the Student_ID, Gender, and Class_Name fields to the PivotTable.
• Ensure Class_name is in the Rows area.
• Move Gender to the Columns area.
• Move Student_ID to the Values area.
• In cell B3, replace Column Labels with Gender. In cell B4, replace F with Female. In cell C4, replace M with Male. In cell A3, replace Count of Student_ID with Total Students. In cell A4, replace Row Labels with Classes.
• Resize the column widths as needed.
• Apply Pivot Style Light 10 to the PivotTable.
Note, depending upon the version of Office being used, the style name may be Light Orange, Pivot Style Light 10.



Click cell A3. Insert a Clustered Column PivotChart on the PivotAnalysis worksheet. Note, Mac users, select the range A4:C11, on the Insert tab, click Recommended Charts, and then click Clustered Column. Format the legend, and apply the data labels as specified.

Customize the PivotChart as follows.
• Reposition the PivotChart so its top left corner is in cell F3. Drag the bottom right corner so the PivotChart fills column N.
• Change the color of the PivotChart to Color 13. Note, depending upon the version of Office being used, the color name may be Monochromatic Palette 9.
• Apply Style 8 to the PivotChart.
• Add a chart title above the chart. Replace Chart Title with Enrollment by Class and Gender



Ensure that the worksheets are correctly named and placed in the following order in the workbook: PivotAnalysis, Enrollment, InputData, Report, EnrollmentBackup. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.


Total Points


Did you find apk for android? You can find new and apps.

What Will You Get?

We provide professional writing services to help you score straight A’s by submitting custom written assignments that mirror your guidelines.

Premium Quality

Get result-oriented writing and never worry about grades anymore. We follow the highest quality standards to make sure that you get perfect assignments.

Experienced Writers

Our writers have experience in dealing with papers of every educational level. You can surely rely on the expertise of our qualified professionals.

On-Time Delivery

Your deadline is our threshold for success and we take it very seriously. We make sure you receive your papers before your predefined time.

24/7 Customer Support

Someone from our customer support team is always here to respond to your questions. So, hit us up if you have got any ambiguity or concern.

Complete Confidentiality

Sit back and relax while we help you out with writing your papers. We have an ultimate policy for keeping your personal and order-related details a secret.

Authentic Sources

We assure you that your document will be thoroughly checked for plagiarism and grammatical errors as we use highly authentic and licit sources.

Moneyback Guarantee

Still reluctant about placing an order? Our 100% Moneyback Guarantee backs you up on rare occasions where you aren’t satisfied with the writing.

Order Tracking

You don’t have to wait for an update for hours; you can track the progress of your order any time you want. We share the status after each step.


Areas of Expertise

Although you can leverage our expertise for any writing task, we have a knack for creating flawless papers for the following document types.

Areas of Expertise

Although you can leverage our expertise for any writing task, we have a knack for creating flawless papers for the following document types.


Trusted Partner of 9650+ Students for Writing

From brainstorming your paper's outline to perfecting its grammar, we perform every step carefully to make your paper worthy of A grade.

Preferred Writer

Hire your preferred writer anytime. Simply specify if you want your preferred expert to write your paper and we’ll make that happen.

Grammar Check Report

Get an elaborate and authentic grammar check report with your work to have the grammar goodness sealed in your document.

One Page Summary

You can purchase this feature if you want our writers to sum up your paper in the form of a concise and well-articulated summary.

Plagiarism Report

You don’t have to worry about plagiarism anymore. Get a plagiarism report to certify the uniqueness of your work.

Free Features $66FREE

  • Most Qualified Writer $10FREE
  • Plagiarism Scan Report $10FREE
  • Unlimited Revisions $08FREE
  • Paper Formatting $05FREE
  • Cover Page $05FREE
  • Referencing & Bibliography $10FREE
  • Dedicated User Area $08FREE
  • 24/7 Order Tracking $05FREE
  • Periodic Email Alerts $05FREE

Our Services

Join us for the best experience while seeking writing assistance in your college life. A good grade is all you need to boost up your academic excellence and we are all about it.

  • On-time Delivery
  • 24/7 Order Tracking
  • Access to Authentic Sources
Academic Writing

We create perfect papers according to the guidelines.

Professional Editing

We seamlessly edit out errors from your papers.

Thorough Proofreading

We thoroughly read your final draft to identify errors.


Delegate Your Challenging Writing Tasks to Experienced Professionals

Work with ultimate peace of mind because we ensure that your academic work is our responsibility and your grades are a top concern for us!

Check Out Our Sample Work

Dedication. Quality. Commitment. Punctuality


It May Not Be Much, but It’s Honest Work!

Here is what we have achieved so far. These numbers are evidence that we go the extra mile to make your college journey successful.


Happy Clients


Words Written This Week


Ongoing Orders


Customer Satisfaction Rate

Process as Fine as Brewed Coffee

We have the most intuitive and minimalistic process so that you can easily place an order. Just follow a few steps to unlock success.

See How We Helped 9000+ Students Achieve Success


We Analyze Your Problem and Offer Customized Writing

We understand your guidelines first before delivering any writing service. You can discuss your writing needs and we will have them evaluated by our dedicated team.

  • Clear elicitation of your requirements.
  • Customized writing as per your needs.

We Mirror Your Guidelines to Deliver Quality Services

We write your papers in a standardized way. We complete your work in such a way that it turns out to be a perfect description of your guidelines.

  • Proactive analysis of your writing.
  • Active communication to understand requirements.

We Handle Your Writing Tasks to Ensure Excellent Grades

We promise you excellent grades and academic excellence that you always longed for. Our writers stay in touch with you via email.

  • Thorough research and analysis for every order.
  • Deliverance of reliable writing service to improve your grades.
Place an Order Start Chat Now