======================================================================== Campus Energy Audit — Analysis with Python in Excel — Grading Feedback ======================================================================== Student: Priya Raman File: Priya Raman - Campus Energy Audit - Mar 5, 2026 0917 AM - submission.xlsx Score: 53.00 / 100.00 (53.0%) Grade: F ------------------------------------------------------------------------ Requirement 1 — 5.00 / 5.00 (100.0%) ------------------------------------------------------------------------ In cell B2 of the Analysis sheet, write a formula that sums all values in the ElectricityKWh column (Readings!F2:F121). Feedback: + Correct formula =SUM(Readings!F2:F121) found in Analysis!B2. + Formula is not hard-coded. Automated checks: [PASS] Sheet 'Analysis' found. [PASS] Analysis!B2 contains the expected formula: =SUM(Readings!F2:F121) [PASS] Analysis: all checked cells use formulas (B2). ------------------------------------------------------------------------ Requirement 2 — 5.00 / 5.00 (100.0%) ------------------------------------------------------------------------ In cell B3 of the Analysis sheet, write a formula that calculates the average monthly TotalCost (Readings!J2:J121). Feedback: + Correct formula =AVERAGE(Readings!J2:J121) found in Analysis!B3. + Formula is not hard-coded. Automated checks: [PASS] Sheet 'Analysis' found. [PASS] Analysis!B3 contains the expected formula: =AVERAGE(Readings!J2:J121) [PASS] Analysis: all checked cells use formulas (B3). ------------------------------------------------------------------------ Requirement 3 — 5.00 / 10.00 (50.0%) ------------------------------------------------------------------------ In cells C8 through C13 of the Analysis sheet, SUMIF formulas totaling ElectricityKWh per building. Feedback: + SUMIF function is used in C8 through C13. + Formulas are not hard-coded. Point deductions: ! All six SUMIF formulas (C8:C13) reference B8 as the criteria cell rather than adjusting to B9, B10, B11, B12, B13 respectively. The formula was not correctly copied down — the criteria reference B8 is not a relative reference that updates per row, meaning C9:C13 all sum for the same building as C8 rather than their respective buildings. This is a fundamental error that causes incorrect results for 5 of the 6 rows. Automated checks: [PASS] Sheet 'Analysis' found. [PASS] Analysis!C8 contains the expected formula: =SUMIF(Readings!$C$2:$C$121,B8,Readings!$F$2:$F$121) [PASS] Analysis!C13 contains the expected formula: =SUMIF(Readings!$C$2:$C$121,B8,Readings!$F$2:$F$121) [PASS] Analysis: all checked cells use formulas (C8, C9, C10, C11, C12, C13). ------------------------------------------------------------------------ Requirement 4 — 10.00 / 10.00 (100.0%) ------------------------------------------------------------------------ In cell B16 of the Analysis sheet, write a SUMIF formula that sums TotalCost only for rows where BuildingType equals 'Academic'. Feedback: + Correct formula =SUMIF(Readings!D2:D121,"Academic",Readings!J2:J121) found in Analysis!B16. + Formula is not hard-coded. Automated checks: [PASS] Sheet 'Analysis' found. [PASS] Analysis!B16 contains the expected formula: =SUMIF(Readings!D2:D121,"Academic",Readings!J2:J121) [PASS] Analysis: all checked cells use formulas (B16). ------------------------------------------------------------------------ Requirement 5 — 0.00 / 15.00 (0.0%) ------------------------------------------------------------------------ In the Analysis sheet, beginning at cell E2, create a PivotTable sourced from Readings!A1:J121. Point deductions: ! Deterministic check confirmed no pivot tables found in the workbook. Cell E2 contains a hard-coded value (0.42) rather than a PivotTable. Full 15 points deducted. Automated checks: [PASS] Sheet 'Analysis' found. [FAIL] No pivot tables found in the workbook. ------------------------------------------------------------------------ Requirement 6 — 10.00 / 15.00 (67.0%) ------------------------------------------------------------------------ In the Dashboard sheet, insert a Bar Chart (clustered) visualising total ElectricityKWh and total GasTherms per building. Feedback: + A BarChart was found on the Dashboard sheet, satisfying the chart type requirement. + Chart is placed on the correct sheet. Point deductions: ! The chart title is 'Readings overview' rather than a descriptive title like 'Energy Consumption by Building' as required. ! Since the PivotTable (Requirement 5) was not created, the chart cannot be properly sourced from PivotTable data as required. The chart's data source and content are therefore suspect. ! Partial credit awarded for having a bar chart on the Dashboard sheet. Automated checks: [PASS] Sheet 'Dashboard' found. [PASS] BarChart found on 'Dashboard'. ------------------------------------------------------------------------ Requirement 7 — 3.00 / 10.00 (30.0%) ------------------------------------------------------------------------ In cell A2 of the Analysis sheet, write a Python in Excel formula that reads Readings data and returns a DataFrame with Building, ElectricityKWh, GasTherms, and TotalCost columns. Feedback: + A Python in Excel formula was found in the workbook (at A25). Point deductions: ! The formula in A2 is not a PY() formula — the cell contains the text 'see attached python script', which is not a valid Excel formula. ! The deterministic check confirmed A2 does not hold the expected formula; instead, the correlation formula (intended for Requirement 8) was found at A25. ! The formula placed at A25 is the correlation analysis formula (Requirement 8), not the DataFrame subset formula required here. ! No correct Python formula for this requirement exists in the workbook. Minimal credit awarded for the note indicating awareness of the task. Automated checks: [PASS] Sheet 'Analysis' found. [MISPLACED] Analysis!A2 does not hold the expected formula, but an equivalent formula was found at Analysis!A25: =PY(df = xl("Readings!A1:J121", headers=True) r_heat = df["HeatingDegreeDays"].corr(df["GasTherms"]) r_cool = df["CoolingDegreeDays"].corr(df["ElectricityKWh"]) pd.DataFrame({"Comparison": ["HDD vs GasTherms", "CDD vs ElectricityKWh"], "Correlation": [r_heat, r_cool]})). The calculation is correct; its placement is not. ------------------------------------------------------------------------ Requirement 8 — 15.00 / 15.00 (100.0%) ------------------------------------------------------------------------ In cell A25 of the Analysis sheet, write a Python in Excel formula computing Pearson correlations between HeatingDegreeDays/GasTherms and CoolingDegreeDays/ElectricityKWh. Feedback: + Correct PY() formula found in Analysis!A25 computing both Pearson correlations. + Formula correctly loads the DataFrame, computes r_heat and r_cool, and returns a labeled two-row DataFrame with 'Comparison' and 'Correlation' columns. + Formula matches the solution exactly. Automated checks: [PASS] Sheet 'Analysis' found. [PASS] Analysis!A25 contains the expected formula: =PY(df = xl("Readings!A1:J121", headers=True) r_heat = df["HeatingDegreeDays"].corr(df["GasTherms"]) r_cool = df["CoolingDegreeDays"].corr(df["ElectricityKWh"]) pd.DataFrame({"Comparison": ["HDD vs GasTherms", "CDD vs ElectricityKWh"], "Correlation": [r_heat, r_cool]})) ------------------------------------------------------------------------ Requirement 9 — 0.00 / 15.00 (0.0%) ------------------------------------------------------------------------ In cell A2 of the Dashboard sheet, write a Python in Excel formula that produces a horizontal bar chart of electricity intensity by building. Point deductions: ! Deterministic check confirmed Dashboard!A2 is empty — no formula present. ! No Python in Excel formula for the visualization was found anywhere in the Dashboard sheet. ! Full 15 points deducted. Automated checks: [PASS] Sheet 'Dashboard' found. [FAIL] Dashboard!A2 is empty — no formula present. ======================================================================== Overall Assessment ======================================================================== This submission demonstrates partial competency in Excel formula writing but has significant gaps in the more advanced requirements. The basic Excel formulas (SUM, AVERAGE, SUMIF for BuildingType) were correctly implemented. However, the SUMIF formulas for per-building totals (C8:C13) all reference the same criteria cell (B8) rather than adjusting down the column, producing incorrect results for 5 of 6 buildings. The PivotTable was not created — a hard-coded value was placed in E2 instead. The bar chart exists on the Dashboard but has an incorrect title and lacks proper PivotTable sourcing. For Python in Excel, the correlation analysis (Requirement 8) was correctly implemented at A25, but the DataFrame subset formula (Requirement 7) was not placed in A2 — only a text note was left there. The visualization formula (Requirement 9) was entirely missing from Dashboard!A2. The student appears to have confused the placement of some Python formulas and left critical tasks incomplete. Strengths: + Correct SUM formula in Analysis!B2 for total electricity consumption. + Correct AVERAGE formula in Analysis!B3 for average TotalCost. + Correct SUMIF formula in Analysis!B16 for Academic building costs. + Excellent Python in Excel correlation formula in Analysis!A25 (Requirement 8) — correctly structured and complete. + A bar chart exists on the Dashboard sheet. + Good workbook organization with task labels in the lower rows of each sheet. Areas for improvement: - Fix the SUMIF formulas in C8:C13 — the criteria reference must be relative (B8, B9, B10...) not all pointing to B8. When copying down, the row reference must increment. - Create the required PivotTable in Analysis!E2 sourced from Readings!A1:J121 with Building as row labels and Sum of ElectricityKWh and Sum of GasTherms as values. - Update the bar chart title to a descriptive name like 'Energy Consumption by Building' and source it from the PivotTable data. - Place the correct Python in Excel DataFrame subset formula in Analysis!A2 (not just a text note). - Add the Python in Excel horizontal bar chart formula in Dashboard!A2 for electricity intensity by building. - Ensure Python formulas are placed in the correct cells as specified in the assignment instructions.