Nov 16, 2026 | 1478 words | 15 min read
5.3.1. EX: Probability and Normal Distributions#
Your firm is evaluating a specific concrete mix design for a new highway interchange. The integrity of the support columns depends entirely on the Compressive Strength of the concrete once it has cured for 28 days.
Critical Safety Threshold: The state department of transportation requires a Minimum Compressive Strength Limit of 4,000 psi. Any single specimen that falls below this limit is defined as a catastrophic structural risk.
While the arithmetic mean strength of a batch might appear sufficient, engineers must use descriptive statistics to quantify the material’s variability. A high average is insufficient if the spread of the data allows even one column to fall below the safety threshold.
Required Files#
Download the
assignment templateand save it as 5_3_1_concrete_username.xlsx. The template contains the raw data and calculated compressive strengths for the 150 concrete cylinders, along with the tab for your calculations and analysis.Download the
report templateand save it as 5_3_1_report_username.pdf. This report is where all your charts and answers to analysis questions will go.
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:
Edit in Word: Download and open the
.docxtemplate in Microsoft Word. Avoid editing in web previewers or Google Docs, as they will corrupt your formulas and chart formatting.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.
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, replacing “template” in the filename. Then fill out any required information in the template such as student name, email, etc.
Instructions#
You have been provided with a dataset containing the compressive strength test results for 150 concrete cylinders. Use it to complete the tasks below.
Part A: Descriptive Statistics#
Provide a numerical snapshot of this performance. Use Excel’s built-in functions to calculate the:
minimum,
maximum,
range,
mean,
median,
standard deviation.
Format these values in Table 1: Summary Statistics, including units.
Part B: Distribution Shape Evaluation#
Before applying a Gaussian distribution model, determine whether the concrete strength data is sufficiently symmetric and has appropriate tail weight. This check tells you whether a normal distribution is a reasonable model for predicting failure probabilities from the sample.
Calculate the following metrics in Table 1 using Excel’s built-in functions. Leave each value with 4 decimal places.
Skewness:
=SKEW()Kurtosis:
=KURT()
Use these guidelines to decide whether a normal distribution is an appropriate model for the concrete strength data and to justify that choice in your memo.
Metric |
“Normal” Range |
Interpretation |
|---|---|---|
Skewness |
-2 to 2 |
Data is sufficiently symmetrical |
Kurtosis |
-2 to 2 |
Data has appropriate peakedness and tail weight |
Part C: Construct a Histogram#
Skewness and kurtosis provide numerical evidence for whether a Gaussian model is reasonable, but a histogram gives the visual check. Use the histogram to show how many specimens fall below the safety limit.
Formatting Constraints for the Histogram
Bin Width: manually override Excel defaults to use a width of 100 psi.
Underflow Bin: set an underflow bin at 4000 psi. This isolates all specimens that fail the safety requirement into a single bar on the far left.
Chart Title and Axis Labels: use descriptive labels that are appropriate for technical presentation.
Copy your histogram into your report document.
Part D: Relative Probability Calculations#
Use the tested sample to calculate the relative probability of failure.
Calculate the Failing Specimen Count in Table 2 using the
=COUNTIF()function to count how many specimens in your dataset of 150 trials failed to meet the safety limit of 4,000 psi.Cross-reference this number with your histogram from Part C. The first bar, the underflow bin for values at or below 4000 psi, should have the same count.
Calculate the relative probability of failure by dividing your count of failing specimens by the total number of trials.
Use cell referencing.
Format the cell as a percentage with two decimal places.
Part E: Theoretical Probability Calculations#
Relative probability describes what happened in the tested sample. Engineers must also use mathematical models to predict the safety of the thousands of untested concrete columns that will be cast for the actual highway bridge. If the concrete strength is normally distributed, standardize the safety limit with a z-score to find this theoretical probability.
In Table 3, enter the algebraic formula to standardize your safety limit (\(x = 4000 \text{psi}\)).
Use cell referencing.
Format the cell with two decimal places.
Calculate the Cumulative Left-Tail Probability using your calculated z-score and the built-in function
=NORM.S.DIST(z-score,TRUE).Use cell referencing.
Format the cell as a percentage with two decimal places.
Part F: Evaluating a Lower-Cost Aggregate Option#
The concrete subcontractor has proposed switching to a cheaper coarse aggregate sourced from an uncertified local riverbed. This change is projected to save $15,000 in raw material costs.
As a Quality Control Engineer, evaluate the physical, safety, and economic consequences of this substitution.
The Physics of Variability: High-quality, graded aggregates pack uniformly and create consistent cement bonding. This produces low variability with \(\sigma = 180\text{ psi}\). The cheaper riverbed aggregate contains erratic grain strengths and inconsistent sizing, which increases batch-to-batch variation and raises the standard deviation to 300 psi. The average concrete strength remains designed at 4,350 psi.
The Regulatory Standard: The State Highway Department specifies a strict 99.9% structural reliability standard for bridge columns. This means the probability of a column’s strength falling below 4,000 psi must be \(\leq 0.1\%\), which corresponds to a minimum safety z-score of -3.09.
Use Table 4 in your template to complete the following calculations.
Calculate the new theoretical probability of failure under the cheaper mix design (\(x = 4000\), \(\mu = 4350\), \(\sigma = 300\)) using
=NORM.DIST(x,mean,sd,TRUE). Format the result as a percentage with two decimal places.Calculate the new target mean strengths required for both mixes so the failure rate is reduced to \(\leq 0.1\%\) (\(Z = -3.09\)). Use cell references in the formula: $\(\text{Target Mean} = 4000 + (3.09 \times \text{Standard Deviation})\)$
Adding extra cement to raise the average strength costs $50 per psi for the entire project. Calculate the average total cost for the premium and cheap mixes using the target mean values for each mix.
Part G: Technical Memo#
You will synthesize your calculations and engineering analysis into a formal two-paragraph technical recommendation memo addressed to the Project Manager. Use a professional, objective tone.
Paragraph 1 (Baseline Risk and Model Justification): Explain why the concrete strength data can be modeled using a standard normal distribution, explicitly quoting the skewness and kurtosis values from Part B and the histogram result from Part C. Then report the premium mix design’s baseline failure rates using the empirical result from Part D and the theoretical results from Parts E and F. Explain how these results show that the current design does not meet the State Highway Department’s strict \(99.9\%\) structural reliability standard, which corresponds to a \(0.1\%\) failure threshold.
Paragraph 2 (Economic and Safety Recommendation): Recommend whether the cheaper riverbed aggregate should be approved. Use your failure calculations for both mixes and your cost calculations from Part F to show how the increased variability of the cheaper aggregate raises the probability of failure below 4,000 psi and increases total project cost compared to the premium mix. Conclude with a clear, evidence-based recommendation on how to proceed safely.
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.