\[ \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 | 1076 words | 11 min read

3.3.1. EX: Spreadsheet Foundations#

A structural engineering team is evaluating different steel beam designs for a new pedestrian bridge. To meet safety regulations, the engineers must ensure that the beams do not deflect (bend) beyond a specified limit under heavy foot traffic. As the lead data analyst, you will calculate the expected deflection for a prototype beam under varying load conditions. You will then choose the most appropriate charts to present this performance data to city planners.

Simply supported beam of length L with a center load P, above its aligned shear and moment diagrams.

Fig. 3.2 Shear and moment diagrams for a simply supported beam subjected to a concentrated load (\(P\)) at its center (American Wood Council, 2007). Upward reaction forces \(R\) act at both ends. The shear diagram is constant and positive over the left half and constant and negative over the right half, and the moment diagram is a triangle peaking directly under the center load.#

The maximum deflection (\(\delta\), in millimeters) of a simply supported beam with a center load is calculated using the following engineering formula:

(3.1)#\[\delta = \frac{P \cdot l^3}{48 \cdot E \cdot I}\]

Where:

  • \(P\) = Applied load (Newtons, N)

  • \(l\) = Span length of the bending member (mm)

  • \(E\) = Modulus of elasticity of the material (MPa)

  • \(I\) = Moment of inertia (mm\(^4\))

  • \(V\) = Shear force (lbs)

  • \(M\) = Maximum bending moment (in.-lbs.)

  • \(R\) = Reaction load at bearing point (lbs)

  • \(x\) = Horizontal distance from reaction to position on the beam (in)

Reference

American Wood Council. (2007). Beam design formulas with shear and moment diagrams. American Forest & Paper Association. https://engineering.purdue.edu/~ce474/Docs/DA6-BeamFormulas.pdf

Required Files#

  1. Download the assignment template and save it as 3_3_1_beam_username.xlsx. The template contains the beam data, including the dimensions and load values needed for the deflection calculations, as well as separate tabs for each task below.

  2. Download the report template, complete it, and save it as 3_3_1_report_username.pdf. This report is where you will place your charts and answers to the analysis questions.

Quick Guide: Completing & Submitting Your Report as PDF

To complete your technical report, you must edit the provided Word template (.docx) and convert it to a PDF for final grading. Follow this simplified workflow:

  1. Edit in Word: Download and open the .docx template in Microsoft Word. Avoid editing in web previewers or Google Docs, as they will corrupt your formulas and chart formatting.

  2. Save as PDF Only When Finished: Type your answers and copy any charts or images directly into the Word document, saving your progress frequently. Once your report is 100% complete, save or export the document as a PDF. (Do not convert to PDF early, as PDFs are static and cannot be edited!) Copy charts or images into the report and keep the originals in the workbook, since Gradescope grades the workbook.

  3. Submit to Gradescope: Upload your finalized PDF and any other required deliverables to the corresponding assignment in Gradescope. Always review your uploaded files from the Gradescope confirmation screen to verify your work is complete.

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.

Task 1: Order of Operations and Data Calculations#

Navigate to the Beam Data tab in your template and complete the following calculations:

  • Calculate the Area Moment of Inertia (\(I\)): The beam has a rectangular cross-section. In the designated cell, enter an Excel formula to calculate \(I\) using the formula \(I = \frac{b \cdot h^3}{12}\). Use absolute cell references for the base (\(b\)) and height (\(h\)) values provided in the constants table.

  • Calculate Deflection (\(\delta\)): In the Deflection (mm) column, write an Excel formula to calculate the deflection at each listed load (\(P\)).

    • Use parentheses correctly to ensure the order of operations is followed exactly (for example, by grouping the entire denominator before dividing).

    • Use absolute cell references for the constants (\(L\), \(E\), and \(I\)) and relative cell references for the load (\(P\)) so you can copy the formula down the entire column.

Answer all analysis questions in the Task 1 section of the report. Use complete sentences and reference specific data in your answers.

Task 2: Chart Selection and Formatting#

Choose graph types that match the characteristics of the data (for example, scatter plots for continuous data and bar charts for categorical data).

Part A: Continuous Data Visualization#

  • Review the data table you just completed, which contains the Applied Load (N) and Deflection (mm).

  • Select the most appropriate chart type to display how deflection changes as the weight load increases.

  • Generate the chart in Excel and format it for technical presentation by including a descriptive title, axis labels, and units.

Part B: Categorical Data Visualization#

  • Navigate to the Material Analysis tab, which lists the maximum deflection limits for four different structural materials (categorical data: Steel, Aluminum, Wood, and Concrete).

  • Select the most appropriate chart type to visually compare the deflection limits across these distinct categories.

  • Generate the chart in Excel and format it for a technical presentation by including a descriptive title, axis labels, and units.

Copy your two formatted charts into the Task 2 section of the report. Keep the original charts in the workbook, since Gradescope grades the chart in the workbook, not the copy in the report. Then answer all analysis questions in that section. Use complete sentences and reference specific data in your answers.

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.