Open Marathon Report paper

Your business fraternity recently held a half-marathon to raise money for the local animal shelter. You have been asked to analyze the results from the fundraiser. The fundraising committee and executive board would like to post results showing each runner, the runner’s age group, pace, finish time, and any awards the runner has received. Awards are based on overall place, age group place, or amount of donations collected. Another member of the committee has entered some data into Excel, but formulas and functions need to be created and formatting needs to be applied.

1. Open marathon_data and save it as marathon_LastFirst. Enable the content if necessary.
2. On the Participants worksheet, create a table with headers using range A1:O75. Name the table Participants. Ensure all data and headings are visible after you complete the tasks. Apply Table Style Medium 23.
3. In Age Group, create a formula (Hint use nested IF functions) that determines which age group each runner is in based on the runner‘s age. The age groups are Senior (over 65), Master (over 45), Middler (over 35), and Junior (over 19). The results in each cell should be one the following: Senior, Master, Middler, or Junior. If the runner does not fit into a specific age group, the cell should be left blank.
4. In Donor Level, create a formula (Hint use a VLOOKUP function) with an approximate match to determine the donation level each runner has met based on the Amount Raised column and the DonationLevel named range on the Lookup Table worksheet.
5. In Place Award, create a formula (Hint use IF and OR functions) to determine whether a runner will be eligible for a place award. A place award is for any runners who place first, second, or third in their Age Group or first, second, or third in the Overall Place. If a runner qualifies for a place award, the result should be Award. If a runner does not qualify for a place award, the cell should be left blank.
6. In Donation Award, create a formula (Hint use IF and AND functions) to determine whether a runner will be eligible for a donation award. Runners who place in the top 10 of their age group place and have an amount raised of $1,000 or more are eligible for a donation award. If a runner is eligible for a donation award, the result should be Award. If a runner does not qualify for a donation award, the cell should be left blank.
7. On the Summary Report worksheet, Determine gender results on the summary report worksheet. (Hint use COUNTIF, AVERAGEIF, and SUMIF functions)
8. Create formulas that retrieve the Age Group Winners Last Name (Hint use INDEX and MATCH functions)
9. Develop a PivotTable and PivotChart for further analysis of the race results. Use the Participants table to create a PivotTable on a new worksheet named PivotTable Analysis. The PivotTable should have the Bib Number field as the report filter, Age Group as the column labels, Donor Level as the row labels, and Sum of Amount Raised in the values area. In cell A4, type Donor Level. In cell B3, type Age Group. Format Sum of Amount Raised as Currency with 0 decimals. Apply Pivot Style Dark 2 and Banded Rows style. Insert a row above row 1, and then in cell A1, type Amount Raised by Donor Level and Age Group. Merge and center in range A1:G1, and then apply the Title style to the range.
10. Add a slicer for the State field, and then choose Ohio, West Virginia, and New York. Ensure all data and headings are visible after you complete the tasks. Position the slicer to the right of the PivotTable. Create a 3-D Pie PivotChart, and then position the PivotChart below the PivotTable. Hide the field buttons, resize the chart so it covers column F, and then add Amount Raised by Donor Level and Age Group as the chart title. Remove the legend, and then add the data callout data labels.
11. Close the workbook, and then exit Excel.
12. Submit the file on the Canvas.

Is this the question you were looking for? If so, place your order here to get started!