Nov 16, 2026 | 650 words | 6 min read
5.1.2. PCA: Probability and Z-Scores in Excel#
Environmental engineers are responsible for ensuring that municipal wastewater effluent safely meets environmental standards before discharge. On this project, your team is monitoring the concentration of a target micro-contaminant in parts per billion [ppb] to ensure it remains below the EPA Maximum Contaminant Level of 15.0 ppb. Any daily sample exceeding 15.0 ppb represents a regulatory water quality violation.
Under normal dry-weather operating conditions, daily contaminant concentrations are highly stable and follow a symmetric normal (Gaussian) distribution with the following baseline parameters:
Process Mean (\(\mu\)): \(12.0 \text{ppb}\)
Process Standard Deviation (\(\sigma\)): \(1.5 \text{ppb}\)
Target Safety Limit (\(x\)): \(15.0 \text{ppb}\)
While a contaminant level of 12.0 ppb is safely below the EPA limit, natural process variation means some days will experience higher concentrations. Your task is to mathematically model this process to determine the exact probability of a regulatory violation.
Required Files#
Download the assignment template and save it as 5_1_2_probability_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#
Review PCMs: Probability and Uncertainty as needed.
Part A: Calculate the Z-Score#
A z-score measures how many standard deviations a target value lies from the mean. In cell F10, enter the algebraic formula for z-score.
Where:
\(x\) = data point
\(\mu\) = mean
\(\sigma\) = standard deviation
Your formula must use cell references pointing to Table 1. Do not hardcode numbers. Verify that your calculated z-score is exactly \(2.00\). If it is not, check your formulas and cell references.
Part B: Manual Z-Table Lookup#
Download and open Z-table. Locate the cumulative left-tail area for \(Z=2.00\). Hint: Locate Row 2.0 on the left margin, and track across to Column .00
The value listed should be \(0.9772\), which means there is a 97.72% probability that any given daily water sample will meet environmental standards.
Enter the value 0.9772 in cell F11.
Part C: Excel Built-in Functions#
Method 1 - Raw Values: In cell
F12, enter the function:=NORM.DIST(C12, C10, C11, TRUE)This calculates cumulative probability directly from your raw parameters without calculating the z-score yourself.Method 2 - Standardized Z: In cell
F13, enter the function:=NORM.S.DIST(F10, TRUE)This calculates cumulative probability from your standardized z-score inF10.Verify that both cells
F12andF13return0.9772, matching your manual lookup exactly.
Part D: Quantifying Violation Risk#
Standard normal tables and cumulative functions only report the compliance rate (area to the left of the safety limit). To find the violation rate, you must calculate the area in the right tail.
In cell F14, enter the formula =1-F12. Verify that the output is 0.0228, which represents a 2.28% chance of a daily water quality violation.
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.