Nov 16, 2026 | 714 words | 7 min read
3.1.2. PCA: Calculations and Charting in Excel#
Engineers frequently analyze power usage data to evaluate system performance and efficiency.
Engineers frequently analyze power usage data to evaluate system performance and efficiency.
You are given one week of electrical energy usage data for a laboratory system. Your task is to organize the data, perform calculations using built-in Excel functions, apply proper cell referencing, and create a clear chart to communicate results.
Required Files#
Download the assignment template and save it as 3_1_2_powerdata_username.xlsx.
Reminder
Rename your files with your Purdue username by replacing “template” in the filename. Then fill out any required information in the template, such as your name, email, and course details.
Instructions#
Part A - Organizing Data#
In the Laboratory Power Usage Data section of the worksheet:
Ensure headers are clear and descriptive
Include units in headers
Keep one variable per column
Do not use merged cells
Ensure the dataset has no blank rows
Your spreadsheet should be readable by another engineer.
Part B - Built-in Functions#
In the Summary Calculations section of the worksheet, use Excel built-in functions to calculate:
Total weekly energy usage
Average daily energy usage
Maximum daily energy usage
Minimum daily energy usage
Number of recorded days
Functions
Use appropriate functions such as =SUM(), =AVERAGE(), =MAX(), =MIN(), and
=COUNT(). Do not type numbers directly into your formulas.
Part C - Unit Conversion#
Engineers often convert between measurement units. In the Energy Conversion setion of the worksheet, convert the daily energy values from kWh to Joules using the built-in function:
=CONVERT(cell,"kWh","J")
Requirements:
Use cell references (do not type the values manually)
Copy the formula down the column
Ensure all values calculate correctly
Part D - Referencing and Constants#
A performance adjustment factor of 1.08 is stored in a designated cell in the worksheet. Multiply each daily energy value by the adjustment factor.
Your formula must:
Use a relative reference for the daily energy value
Use an absolute reference for the adjustment factor
When copied down the column, your formula should work correctly without editing.
Part E - Data Visualization#
Create a bar chart (clustered column chart) or line chart that displays:
Day on the horizontal (x) axis
Adjusted Energy Usage (kWh) on the vertical (y) axis. You do not have to order your values from highest to lowest.
Your chart must include:
A descriptive chart title: Laboratory Power Usage Data
Horizontal axis label: Day
Vertical axis label: Adjusted Energy Usage (kWh)
Clear, readable formatting
The chart should clearly communicate the variation in energy usage across the week. Place the chart beside the dataset without covering any data or calculations.
Expected Value Check#
After completing your calculations:
The total weekly energy should be approximately 850 kWh.
The average daily energy should be approximately 121.43 kWh.
The adjusted energy on Friday should be approximately 124.2 kWh.
If your results differ significantly, review your formulas and cell references.
Gradescope Submission#
Save Deliverables: Ensure all required assignment files are saved and named exactly as specified in the assignment instructions. If a template file name contains the word
template, replace only that word with your Purdue username. When a task lists more than one deliverable, such as a workbook and a report, submit each deliverable to its own matching Gradescope assignment instead of combining them into one upload.Navigate to Gradescope: Log in to Gradescope. From your Dashboard, open the assignment.
Upload your work: Select the Upload submission method. Drag and drop all required files (or a compressed
.zipfolder) into Gradescope, and select Upload.Confirm Submission: When your upload is successful, you will see a confirmation message on your screen and receive an automated email receipt.
Add Team Members (if applicable): If this is a team assignment, add all team members to the submission. Each teammate should confirm they can see the submission in their own Gradescope account. For help, visit Gradescope’s documentation on adding team members.
Review Autograder Results: Wait a few minutes for the autograder to evaluate the relevant files. Scroll down to view any failed test cases. If you need assistance interpreting the output or errors, ask your teaching team.
Resubmit if Necessary: You can edit your files and select Resubmit as many times as you need before the assignment deadline. Only your final, active submission is graded. (Note: If you need to resubmit, you must upload all required files for the assignment again).
For a detailed walkthrough, review the official Gradescope Submission Guide.