9 tasks | 100 points total
| # | Pts | Location | Requirement | Automated checks |
|---|---|---|---|---|
| 1 | 5 | Analysis!B2 |
In cell B2 of the Analysis sheet, calculate the total number of passenger boardings (TotalRiders) across all 80 log entries in the Operations sheet. (Skills: sum) | sheet exists: 'Analysis' formula in cell: Analysis!B2 uses SUM calculated, not typed: Analysis!B2 |
| 2 | 5 | Analysis!B3 |
In cell B3 of the Analysis sheet, calculate the average fuel consumption in gallons (FuelUsedGallons) across all 80 log entries in the Operations sheet. Round to 2 decimal places using the ROUND function. (Skills: average) | sheet exists: 'Analysis' formula in cell: Analysis!B3 uses AVERAGE, ROUND calculated, not typed: Analysis!B3 |
| 3 | 10 | Analysis!B6, B7, B8, B9 |
In cells B6 through B9 of the Analysis sheet, use SUMIF to calculate the total TotalRiders for each of the four zones. Cell B6 should hold the total for the 'North' zone, B7 for 'South', B8 for 'East', and B9 for 'West'. Use the zone labels already entered in cells A6:A9 as the criteria. Reference the Zone column (C2:C81) and TotalRiders column (K2:K81) on the Operations sheet. (Skills: sumif) | sheet exists: 'Analysis' formula in cell: Analysis!B6 uses SUMIF formula in cell: Analysis!B9 uses SUMIF calculated, not typed: Analysis!B6, B7, B8, B9 |
| 4 | 10 | Analysis!B12 |
In cell B12 of the Analysis sheet, calculate the on-time performance rate for routes where an incident was reported. The on-time performance rate is defined as the sum of OnTimeTrips divided by the sum of CompletedTrips, expressed as a percentage, but only for rows where IncidentReported equals 'Yes' on the Operations sheet. Use SUMIF functions referencing Operations!P2:P81 as the criteria range, and format the result as a percentage. (Skills: sumif, average) | sheet exists: 'Analysis' formula in cell: Analysis!B12 uses SUMIF calculated, not typed: Analysis!B12 |
| 5 | 15 | Analysis!D2 |
Create a PivotTable on the Analysis sheet starting at cell D2. Use the Operations sheet data (A1:P81) as the source. Configure the PivotTable with: RouteCode as Row labels, VehicleType as Column labels, and the Average of FuelUsedGallons as the Values field. Format the values in the PivotTable to show 2 decimal places. (Skills: pivot_table) | sheet exists: 'Analysis' pivot table present on 'Analysis' |
| 6 | 15 | Dashboard |
On the Dashboard sheet, create a Bar Chart (clustered bar) that visualises total TotalRiders by Zone. Use the zone totals you calculated in Analysis!A6:B9 as the chart data source. Title the chart 'Total Ridership by Zone'. Place the chart within the range A2:H20 on the Dashboard sheet. (Skills: charts) | sheet exists: 'Dashboard' chart present: BarChart on 'Dashboard' |
| 7 | 15 | Dashboard!A22 |
In cell A22 of the Dashboard sheet, write a Python in Excel formula that reads all Operations data and returns a cleaned summary DataFrame showing, for each RouteCode, the total CompletedTrips, total TotalRiders, and average FuelUsedGallons (rounded to 2 decimal places). The result should be a DataFrame with columns named 'RouteCode', 'CompletedTrips', 'TotalRiders', and 'AvgFuelGallons', sorted ascending by RouteCode. (Skills: py_basics, py_dataframe) | sheet exists: 'Dashboard' formula in cell: Dashboard!A22 uses PY calculated, not typed: Dashboard!A22 |
| 8 | 10 | Dashboard!A42 |
In cell A42 of the Dashboard sheet, write a Python in Excel formula that computes a correlation matrix between the following numeric columns from the Operations sheet: ScheduledTrips, CompletedTrips, TotalRiders, FuelUsedGallons, and AvgSpeedMPH. Return the correlation matrix as a DataFrame rounded to 3 decimal places. (Skills: py_aggregation, py_statistics) | sheet exists: 'Dashboard' formula in cell: Dashboard!A42 uses PY calculated, not typed: Dashboard!A42 |
| 9 | 15 | Dashboard!A55 |
In cell A55 of the Dashboard sheet, write a Python in Excel formula that produces a grouped bar chart image showing average TotalRiders by Zone and VehicleType. Use the Operations sheet data as the source. The chart should have a title of 'Avg Ridership by Zone and Vehicle Type', labelled axes (x-axis: 'Zone', y-axis: 'Average Riders'), and a legend. Return the matplotlib figure so Excel renders it as an embedded image. (Skills: py_visualization) | sheet exists: 'Dashboard' formula in cell: Dashboard!A55 uses PY calculated, not typed: Dashboard!A55 |
Checks listed as automated are verified programmatically against the submitted workbook. Requirements without automated checks are assessed by review.
=SUM(Operations!K2:K81)=ROUND(AVERAGE(Operations!N2:N81),2)=SUMIF(Operations!$C$2:$C$81,A6,Operations!$K$2:$K$81)=SUMIF(Operations!$P$2:$P$81,"Yes",Operations!$J$2:$J$81)/SUMIF(Operations!$P$2:$P$81,"Yes",Operations!$I$2:$I$81)df = xl("Operations!A1:P81", headers=True)
result = df.groupby("RouteCode", as_index=False).agg(
CompletedTrips=("CompletedTrips", "sum"),
TotalRiders=("TotalRiders", "sum"),
AvgFuelGallons=("FuelUsedGallons", "mean")
)
result["AvgFuelGallons"] = result["AvgFuelGallons"].round(2)
result.sort_values("RouteCode").reset_index(drop=True)df = xl("Operations!A1:P81", headers=True)
cols = ["ScheduledTrips", "CompletedTrips", "TotalRiders", "FuelUsedGallons", "AvgSpeedMPH"]
df[cols].corr().round(3)df = xl("Operations!A1:P81", headers=True)
pivot = df.groupby(["Zone", "VehicleType"])["TotalRiders"].mean().unstack()
fig, ax = plt.subplots(figsize=(8, 5))
pivot.plot(kind="bar", ax=ax)
ax.set_title("Avg Ridership by Zone and Vehicle Type")
ax.set_xlabel("Zone")
ax.set_ylabel("Average Riders")
ax.legend(title="VehicleType")
plt.tight_layout()
fig