8: Spreadsheet Modeling
- Page ID
- 131333
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\( \newcommand{\dsum}{\displaystyle\sum\limits} \)
\( \newcommand{\dint}{\displaystyle\int\limits} \)
\( \newcommand{\dlim}{\displaystyle\lim\limits} \)
\( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)
( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\id}{\mathrm{id}}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\kernel}{\mathrm{null}\,}\)
\( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\)
\( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\)
\( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)
\( \newcommand{\vectorA}[1]{\vec{#1}} % arrow\)
\( \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow\)
\( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vectorC}[1]{\textbf{#1}} \)
\( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)
\( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)
\( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\(\newcommand{\longvect}{\overrightarrow}\)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\(\newcommand{\avec}{\mathbf a}\) \(\newcommand{\bvec}{\mathbf b}\) \(\newcommand{\cvec}{\mathbf c}\) \(\newcommand{\dvec}{\mathbf d}\) \(\newcommand{\dtil}{\widetilde{\mathbf d}}\) \(\newcommand{\evec}{\mathbf e}\) \(\newcommand{\fvec}{\mathbf f}\) \(\newcommand{\nvec}{\mathbf n}\) \(\newcommand{\pvec}{\mathbf p}\) \(\newcommand{\qvec}{\mathbf q}\) \(\newcommand{\svec}{\mathbf s}\) \(\newcommand{\tvec}{\mathbf t}\) \(\newcommand{\uvec}{\mathbf u}\) \(\newcommand{\vvec}{\mathbf v}\) \(\newcommand{\wvec}{\mathbf w}\) \(\newcommand{\xvec}{\mathbf x}\) \(\newcommand{\yvec}{\mathbf y}\) \(\newcommand{\zvec}{\mathbf z}\) \(\newcommand{\rvec}{\mathbf r}\) \(\newcommand{\mvec}{\mathbf m}\) \(\newcommand{\zerovec}{\mathbf 0}\) \(\newcommand{\onevec}{\mathbf 1}\) \(\newcommand{\real}{\mathbb R}\) \(\newcommand{\twovec}[2]{\left[\begin{array}{r}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\ctwovec}[2]{\left[\begin{array}{c}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\threevec}[3]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\cthreevec}[3]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\fourvec}[4]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\cfourvec}[4]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\fivevec}[5]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\cfivevec}[5]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\mattwo}[4]{\left[\begin{array}{rr}#1 \amp #2 \\ #3 \amp #4 \\ \end{array}\right]}\) \(\newcommand{\laspan}[1]{\text{Span}\{#1\}}\) \(\newcommand{\bcal}{\cal B}\) \(\newcommand{\ccal}{\cal C}\) \(\newcommand{\scal}{\cal S}\) \(\newcommand{\wcal}{\cal W}\) \(\newcommand{\ecal}{\cal E}\) \(\newcommand{\coords}[2]{\left\{#1\right\}_{#2}}\) \(\newcommand{\gray}[1]{\color{gray}{#1}}\) \(\newcommand{\lgray}[1]{\color{lightgray}{#1}}\) \(\newcommand{\rank}{\operatorname{rank}}\) \(\newcommand{\row}{\text{Row}}\) \(\newcommand{\col}{\text{Col}}\) \(\renewcommand{\row}{\text{Row}}\) \(\newcommand{\nul}{\text{Nul}}\) \(\newcommand{\var}{\text{Var}}\) \(\newcommand{\corr}{\text{corr}}\) \(\newcommand{\len}[1]{\left|#1\right|}\) \(\newcommand{\bbar}{\overline{\bvec}}\) \(\newcommand{\bhat}{\widehat{\bvec}}\) \(\newcommand{\bperp}{\bvec^\perp}\) \(\newcommand{\xhat}{\widehat{\xvec}}\) \(\newcommand{\vhat}{\widehat{\vvec}}\) \(\newcommand{\uhat}{\widehat{\uvec}}\) \(\newcommand{\what}{\widehat{\wvec}}\) \(\newcommand{\Sighat}{\widehat{\Sigma}}\) \(\newcommand{\lt}{<}\) \(\newcommand{\gt}{>}\) \(\newcommand{\amp}{&}\) \(\definecolor{fillinmathshade}{gray}{0.9}\)Spreadsheet Modeling
A calculator gives you one answer. A spreadsheet lets you understand behavior across a range — which is what engineering design actually asks.
Learning Objectives
By the end of this chapter, you will be able to:
- Describe why spreadsheets are engineering modeling tools, not just calculators.
- Structure a spreadsheet into inputs, model equations, and outputs with proper labeling.
- Use absolute and relative cell references correctly in parameter sweeps.
- Convert angles from degrees to radians within formulas.
- Perform a parameter sweep and generate an XY scatter plot with fully labeled axes.
- Interpret a graph's shape as engineering information, not just a visual.
- Identify and fix six common spreadsheet modeling errors.
- Apply conditional formatting and IF logic for piecewise engineering models.
- 8.1: From Calculator to Model
- This page emphasizes the shift from single numerical calculations to modeling behavior across variable ranges in engineering design. It contrasts calculators, which give one answer, with spreadsheets that enable parameter definition, equation application across ranges, visual result representation, and design alternative comparison. This distinction positions spreadsheets as crucial tools for comprehending complex behaviors in engineering, rather than just supplements to traditional calculators.
- 8.2: Structuring an Engineering Spreadsheet
- This page outlines best practices for creating an engineering spreadsheet, emphasizing a structure divided into Inputs, Model Equations, and Outputs. Inputs should be clearly labeled with parameters, while Model Equations must only include formula cells to ensure transparency. Outputs need to present computed values and graphs effectively. An example demonstrates the significance of using absolute references and unit conversions to enhance the spreadsheet's audibility and overall effectiveness.
- 8.3: Absolute vs Relative References
- This page explains the distinction between absolute and relative references in spreadsheet formulas, noting that absolute references remain fixed while relative references adjust based on positioning. It highlights the utility of each type for different scenarios, such as using absolute references for constants and relative ones for variables.
- 8.4: Parameter Sweeps
- This page covers parameter sweeps, illustrating how to systematically vary inputs like angle to determine corresponding outputs such as tension. It features a worked example demonstrating the nonlinear relationship between angle and tension, where decreasing the angle leads to increased tension. The findings stress the significance of establishing a minimum acceptable angle in cable design to effectively manage load.
- 8.5: Graphical Visualization
- This page highlights the significance of effective graphical visualization in engineering, advocating for XY scatter plots. It outlines essential graph components like descriptive titles, labeled axes with units, and appropriate scaling, while avoiding decorative elements. The page covers graph interpretation, focusing on sensitivity regions and constraints.
- 8.6: Conditional Formatting and IF Logic
- This page covers the use of the IF and IFS functions alongside conditional formatting in spreadsheets, particularly for engineering models with piecewise behavior. It demonstrates how these tools can facilitate safety checks and enhance data visualization. The Excel Cable Car Project serves as a practical example, involving calculations of position and velocity related to towers, relevant formula application, and the creation of a scatter plot and technical memo.
- 8.7: Six Common Spreadsheet Errors
- This page outlines six common errors in spreadsheet modeling that can result in unnoticed inaccuracies, such as using constants, incorrect angle measurements, and misleading graphs. It stresses the need for clear labeling and accurate representation in engineering communication. An ethics check highlights the consequences of misleading visuals, questioning the line between errors and dishonesty, and underscores the engineer's duty to present data accurately.
- 8.8: Spreadsheets as Design Exploration Tools
- This page explores the benefits of using spreadsheets for design exploration, highlighting a comparison between a single calculation method and a parametric approach. The parametric method allows for a thorough analysis of various design parameters, revealing not only if a design meets requirements but also its limitations and proximity to those limits. This fosters comprehensive reasoning in the design process.
- 8.9: When Spreadsheets Are Not Enough
- This page highlights the limitations of spreadsheets in engineering modeling, particularly their difficulties with large matrices, iterative algorithms, and automation. It presents MATLAB as a superior tool for complex tasks, while viewing spreadsheets as foundational for modeling. The emphasis is on treating spreadsheets as an environment for inputs, equations, and outputs, encouraging practices that enhance model reliability and support design exploration through parameter sweeps.
- 8.10: Summary
- This page covers spreadsheet use for parametric modeling, detailing a three-region structure: inputs, equations, and outputs. It stresses absolute vs. relative references, the use of RADIANS() in trigonometric functions, and effective XY scatter plot requirements. It discusses piecewise modeling with IF/IFS and the role of conditional formatting.
- 8.11: End-of-Chapter Problem Set
- This page covers practical Excel exercises focused on engineering calculations and data analysis. It involves formulating equations for current and cable tension, identifying errors in formulas, and setting up parameter sweeps for tension and power across various angles and resistances. Additionally, it emphasizes graph interpretation, especially tension vs. angle graphs for design decisions, and discusses issues with power graph scaling.