\[ \begin{align}\begin{aligned}\newcommand\blank{~\underline{\hspace{1.2cm}}~}\\% Bold symbols (vectors) \newcommand\bs[1]{\mathbf{#1}}\\% Differential \newcommand\dd[2][]{\mathrm{d}^{#1}{#2}} % use as \dd, \dd{x}, or \dd[2]{x}\\% Poor man's siunitx \newcommand\unit[1]{\mathrm{#1}} \newcommand\num[1]{#1} \newcommand\qty[2]{#1~\unit{#2}}\\\newcommand\per{/} \newcommand\squared{{}^2} \newcommand\cubed{{}^3} % % Scale \newcommand\milli{\unit{m}} \newcommand\centi{\unit{c}} \newcommand\kilo{\unit{k}} \newcommand\mega{\unit{M}} % % Percent \newcommand\percent{\unit{{\kern-4mu}\%}} % % Angle \newcommand\radian{\unit{rad}} \newcommand\degree{\unit{{\kern-4mu}^\circ}} % % Time \newcommand\second{\unit{s}} \newcommand\s{\second} \newcommand\minute{\unit{min}} \newcommand\hour{\unit{h}} % % Distance \newcommand\meter{\unit{m}} \newcommand\m{\meter} \newcommand\inch{\unit{in}} \newcommand\foot{\unit{ft}} % % Force \newcommand\newton{\unit{N}} \newcommand\kip{\unit{kip}} % kilopound in "freedom" units - edit made by Sri % % Mass \newcommand\gram{\unit{g}} \newcommand\g{\gram} \newcommand\kilogram{\unit{kg}} \newcommand\kg{\kilogram} \newcommand\grain{\unit{grain}} \newcommand\ounce{\unit{oz}} % % Temperature \newcommand\kelvin{\unit{K}} \newcommand\K{\kelvin} \newcommand\celsius{\unit{{}^\circ C}} \newcommand\C{\celsius} \newcommand\fahrenheit{\unit{{}^\circ F}} \newcommand\F{\fahrenheit} % % Area \newcommand\sqft{\unit{sq\,\foot}} % square foot % % Volume \newcommand\liter{\unit{L}} \newcommand\gallon{\unit{gal}} % % Frequency \newcommand\hertz{\unit{Hz}} \newcommand\rpm{\unit{rpm}} % % Voltage \newcommand\volt{\unit{V}} \newcommand\V{\volt} \newcommand\millivolt{\milli\volt} \newcommand\mV{\milli\volt} \newcommand\kilovolt{\kilo\volt} \newcommand\kV{\kilo\volt} % % Current \newcommand\ampere{\unit{A}} \newcommand\A{\ampere} \newcommand\milliampereA{\milli\ampere} \newcommand\mA{\milli\ampere} \newcommand\kiloampereA{\kilo\ampere} \newcommand\kA{\kilo\ampere} % % Resistance \newcommand\ohm{\Omega} \newcommand\milliohm{\milli\ohm} \newcommand\kiloohm{\kilo\ohm} % correct SI spelling \newcommand\kilohm{\kilo\ohm} % "American" spelling used in siunitx \newcommand\megaohm{\mega\ohm} % correct SI spelling \newcommand\megohm{\mega\ohm} % "American" spelling used in siunitx % % Capacitance \newcommand\farad{\unit{F}} \newcommand\F{\farad} \newcommand\microfarad{\micro\farad} \newcommand\muF{\micro\farad} % % Inductance \newcommand\henry{\unit{H}} \newcommand\H{\henry} \newcommand\millihenry{\milli\henry} \newcommand\mH{\milli\henry} % % Power \newcommand\watt{\unit{W}} \newcommand\W{\watt} \newcommand\milliwatt{\milli\watt} \newcommand\mW{\milli\watt} \newcommand\kilowatt{\kilo\watt} \newcommand\kW{\kilo\watt} % % Energy \newcommand\joule{\unit{J}} \newcommand\J{\joule} % % Composite units % % Torque \newcommand\ozin{\unit{\ounce}\,\unit{in}} \newcommand\newtonmeter{\unit{\newton\,\meter}} % % Pressure \newcommand\psf{\unit{psf}} % pounds per square foot \newcommand\pcf{\unit{pcf}} % pounds per cubic foot \newcommand\pascal{\unit{Pa}} \newcommand\Pa{\pascal} \newcommand\ksi{\unit{ksi}} % kilopound per square inch % NOTE: do NOT define \bar -- it is the built-in LaTeX accent used by \bar{x}, % \bar{y} (sample means). Redefining it made those render as the word "bar". \newcommand\barunit{\unit{bar}} \end{aligned}\end{align} \]

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:

  1. Total weekly energy usage

  2. Average daily energy usage

  3. Maximum daily energy usage

  4. Minimum daily energy usage

  5. 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#

  1. 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.

  2. Navigate to Gradescope: Log in to Gradescope. From your Dashboard, open the assignment.

  3. Upload your work: Select the Upload submission method. Drag and drop all required files (or a compressed .zip folder) into Gradescope, and select Upload.

  4. Confirm Submission: When your upload is successful, you will see a confirmation message on your screen and receive an automated email receipt.

  5. 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.

  6. 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.

  7. 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.