======================================================================== Riverbend Transit — Ridership Analysis with Python in Excel — Grading Feedback ======================================================================== Student: Priya Raman File: Priya Raman - Riverbend Transit Analysis - Mar 5, 2026 0917 AM - submission.xlsx Score: 47.00 / 100.00 (47.0%) Grade: F ------------------------------------------------------------------------ Requirement 1 — 5.00 / 5.00 (100.0%) ------------------------------------------------------------------------ In cell B2 of the Analysis sheet, calculate the total number of passenger boardings (TotalRiders) across all 80 log entries in the Operations sheet. Feedback: + Correct formula =SUM(Operations!K2:K81) found in Analysis!B2. + Formula is not hard-coded. Automated checks: [PASS] Sheet 'Analysis' found. [PASS] Analysis!B2 contains the expected formula: =SUM(Operations!K2:K81) [PASS] Analysis: all checked cells use formulas (B2). ------------------------------------------------------------------------ Requirement 2 — 5.00 / 5.00 (100.0%) ------------------------------------------------------------------------ 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. Feedback: + Correct formula =ROUND(AVERAGE(Operations!N2:N81),2) found in Analysis!B3. + Both ROUND and AVERAGE functions used as required. Automated checks: [PASS] Sheet 'Analysis' found. [PASS] Analysis!B3 contains the expected formula: =ROUND(AVERAGE(Operations!N2:N81),2) [PASS] Analysis: all checked cells use formulas (B3). ------------------------------------------------------------------------ Requirement 3 — 7.00 / 10.00 (70.0%) ------------------------------------------------------------------------ In cells B6 through B9 of the Analysis sheet, use SUMIF to calculate the total TotalRiders for each of the four zones. Feedback: + SUMIF formulas are present in B6 through B9. + Correct column references used (Operations!$C$2:$C$81 and Operations!$K$2:$K$81). + Formulas are not hard-coded. Point deductions: ! All four cells (B6, B7, B8, B9) contain the identical formula referencing A6 as the criteria cell. B7, B8, and B9 should reference A7, A8, and A9 respectively. This means B7, B8, and B9 all return the North zone total instead of South, East, and West. Only B6 is correct. Deducting 3 points for the three incorrect criteria references. Automated checks: [PASS] Sheet 'Analysis' found. [PASS] Analysis!B6 contains the expected formula: =SUMIF(Operations!$C$2:$C$81,A6,Operations!$K$2:$K$81) [PASS] Analysis!B9 contains the expected formula: =SUMIF(Operations!$C$2:$C$81,A6,Operations!$K$2:$K$81) [PASS] Analysis: all checked cells use formulas (B6, B7, B8, B9). ------------------------------------------------------------------------ Requirement 4 — 10.00 / 10.00 (100.0%) ------------------------------------------------------------------------ In cell B12 of the Analysis sheet, calculate the on-time performance rate for routes where an incident was reported. Feedback: + Correct formula found in Analysis!B12 using SUMIF to divide OnTimeTrips by CompletedTrips filtered by IncidentReported='Yes'. + Formula references correct columns (P for criteria, J for OnTimeTrips, I for CompletedTrips). Automated checks: [PASS] Sheet 'Analysis' found. [PASS] Analysis!B12 contains the expected formula: =SUMIF(Operations!$P$2:$P$81,"Yes",Operations!$J$2:$J$81)/SUMIF(Operations!$P$2:$P$81,"Yes",Operations!$I$2:$I$81) [PASS] Analysis: all checked cells use formulas (B12). ------------------------------------------------------------------------ Requirement 5 — 0.00 / 15.00 (0.0%) ------------------------------------------------------------------------ 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. Point deductions: ! Deterministic check confirmed no pivot tables found anywhere in the workbook. ! Cell D2 on the Analysis sheet contains a hard-coded value (0.42) rather than a PivotTable. ! Full 15 points deducted as the requirement was not completed. Automated checks: [PASS] Sheet 'Analysis' found. [FAIL] No pivot tables found in the workbook. ------------------------------------------------------------------------ Requirement 6 — 10.00 / 15.00 (67.0%) ------------------------------------------------------------------------ On the Dashboard sheet, create a Bar Chart (clustered bar) that visualises total TotalRiders by Zone. Title the chart 'Total Ridership by Zone'. Place the chart within the range A2:H20 on the Dashboard sheet. Feedback: + A BarChart was found on the Dashboard sheet, satisfying the chart type requirement. Point deductions: ! The chart title is 'Operations overview' rather than the required 'Total Ridership by Zone'. Deducting 3 points. ! The chart is anchored at D4, not within A2:H20 as specified. Deducting 2 points for incorrect placement. Automated checks: [PASS] Sheet 'Dashboard' found. [PASS] BarChart found on 'Dashboard'. ------------------------------------------------------------------------ Requirement 7 — 0.00 / 15.00 (0.0%) ------------------------------------------------------------------------ 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. Point deductions: ! Cell A22 contains the hard-coded text 'see attached python script' rather than a Python in Excel formula. ! The deterministic check confirmed this as a hard-coded value failure. ! The formula found at A42 is the correlation matrix (Requirement 8), not the RouteCode summary DataFrame required here. ! No credit awarded as the required Python formula is entirely absent from A22. Automated checks: [PASS] Sheet 'Dashboard' found. [MISPLACED] Dashboard!A22 does not hold the expected formula, but an equivalent formula was found at Dashboard!A42: =PY(df = xl("Operations!A1:P81", headers=True) cols = ["ScheduledTrips", "CompletedTrips", "TotalRiders", "FuelUsedGallons", "AvgSpeedMPH"] df[cols].corr().round(3)). The calculation is correct; its placement is not. [HARDCODED] Dashboard: hard-coded values where formulas were required — A22='see attached python script'. ------------------------------------------------------------------------ Requirement 8 — 10.00 / 10.00 (100.0%) ------------------------------------------------------------------------ In cell A42 of the Dashboard sheet, write a Python in Excel formula that computes a correlation matrix between ScheduledTrips, CompletedTrips, TotalRiders, FuelUsedGallons, and AvgSpeedMPH. Feedback: + Correct Python in Excel formula found in Dashboard!A42. + All five required columns included: ScheduledTrips, CompletedTrips, TotalRiders, FuelUsedGallons, AvgSpeedMPH. + Result is rounded to 3 decimal places as required. + Formula uses xl() to read Operations data with headers. Automated checks: [PASS] Sheet 'Dashboard' found. [PASS] Dashboard!A42 contains the expected formula: =PY(df = xl("Operations!A1:P81", headers=True) cols = ["ScheduledTrips", "CompletedTrips", "TotalRiders", "FuelUsedGallons", "AvgSpeedMPH"] df[cols].corr().round(3)) [PASS] Dashboard: all checked cells use formulas (A42). ------------------------------------------------------------------------ Requirement 9 — 0.00 / 15.00 (0.0%) ------------------------------------------------------------------------ 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. Point deductions: ! Cell A55 is empty — no Python formula for the visualization was found there. ! The deterministic check confirmed A55 is empty. ! The formula found at A42 is the correlation matrix, not the matplotlib visualization. ! No credit awarded as the required Python visualization formula is entirely absent. Automated checks: [PASS] Sheet 'Dashboard' found. [MISPLACED] Dashboard!A55 does not hold the expected formula, but an equivalent formula was found at Dashboard!A42: =PY(df = xl("Operations!A1:P81", headers=True) cols = ["ScheduledTrips", "CompletedTrips", "TotalRiders", "FuelUsedGallons", "AvgSpeedMPH"] df[cols].corr().round(3)). The calculation is correct; its placement is not. [FAIL] Dashboard: expected cells are empty — A55. ======================================================================== Overall Assessment ======================================================================== This submission demonstrates competence with basic Excel formulas (SUM, AVERAGE, ROUND, SUMIF) and successfully completed Requirements 1, 2, and 4 with correct formulas. Requirement 8 (correlation matrix in Python in Excel) was also completed correctly. However, significant portions of the assignment are missing or incorrect: the PivotTable (Req 5) was not created at all; the RouteCode summary Python formula (Req 7) was replaced with a text note referencing an external script; the matplotlib visualization (Req 9) is entirely absent; the SUMIF formulas for zones (Req 3) all reference the same criteria cell (A6) instead of A6-A9 respectively; and the bar chart (Req 6) has the wrong title and incorrect placement. The Python in Excel requirements were largely not completed within the workbook itself. Strengths: + Correct SUM formula in B2 for total ridership + Correct ROUND(AVERAGE(...)) formula in B3 for average fuel consumption + Correct SUMIF formula structure in B12 for on-time performance rate with incident filter + Correct Python in Excel correlation matrix formula in A42 (Req 8) + Good sheet organization and formatting of the Operations and Routes data + A BarChart was created on the Dashboard sheet Areas for improvement: - SUMIF formulas in B6:B9 must reference the corresponding criteria cells (A6, A7, A8, A9) — not all the same cell A6 - A PivotTable must be created on the Analysis sheet with RouteCode rows, VehicleType columns, and Average FuelUsedGallons values - The Python in Excel RouteCode summary DataFrame formula must be placed in Dashboard!A22 as a =PY() formula, not a text note - The matplotlib grouped bar chart Python formula must be placed in Dashboard!A55 - The bar chart title must match the required 'Total Ridership by Zone' exactly - The bar chart should be positioned within the A2:H20 range on the Dashboard sheet - All Python in Excel work must be embedded directly in the workbook cells, not referenced as external scripts