Nov 16, 2026 | 1397 words | 14 min read
4.3.1. EX: Data Analysis and Distributions#
A pen company is designing a new ballpoint pen style, but the inkwell retraction mechanisms continue to fail quality checks. You have been assigned as the lead quality engineer to evaluate the springs used in these mechanisms. You suspect the external suppliers are producing springs with too much variability, causing the mechanisms to jam. You must analyze the tested spring constants from sample batches of 150 springs provided by three competing suppliers (Supplier A, Supplier B, and Supplier C). You will evaluate their manufacturing consistency and decide on the best supplier for future manufacturing.
Required Files#
Download the
assignment templateand save it as 4_3_1_springs_username.xlsx. The template contains the raw data with the calculated spring constants (in N/m) for the 150 tested springs from each supplier, as well as specific tabs for each task below.Download the
report template, complete it, and save it as 4_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.
Task 1: Summary Statistics#
Your first step is to analyze the raw data collected from the spring compression tests and visualize the distributions for Supplier A and Supplier B.
Summary Statistics#
Navigate to the Task 1 tab.
For each of the data sets, use Excel’s built-in functions to calculate the
minimum,
maximum,
range,
mean,
median,
variance, and
standard deviation.
Properly format these values in the Task 1: Summary Statistics table.
Answer all analysis questions in the “Both Suppliers: Summary Statistics” section of the report. Use complete sentences and reference specific data in your answers.
Supplier A: Bin-Size Comparison#
Create two different histograms for Supplier A: one with exactly 5 bins and one with exactly 15 bins.
Calculate the mathematical bin width for each histogram by dividing the range of the dataset by the required number of bins. Enter this calculated value in Table 2 with a formula. Do not round the table value.
When you set up each histogram, adjust the bin width to a whole number greater than your calculated value.
Create an underflow bin to start at a whole number above the minimum value of the dataset. This will ensure your bin edges are clean whole numbers.
Format each histogram for technical presentation by including appropriate titles, axis labels, and units.
Copy your two formatted histograms into the “Supplier A: Bin-Size Comparison” section of Task 1 on the report document. Keep the original histograms in the workbook.
Answer all analysis questions in the “Supplier A: Bin-Size Comparison” section of the report. Use complete sentences and reference specific data in your answers.
Supplier B: High Variance Analysis#
Create one histogram for Supplier B.
Choose a meaningful bin width that accurately displays the shape and spread of the data.
Create an underflow bin starting at a reasonable whole number value above your minimum to ensure your bin edges are whole numbers.
Format this histogram for a technical presentation by including an appropriate title, axis labels, and units.
Copy the formatted histogram into Task 1 on the report document.
Answer all analysis questions in the “Supplier B: High Variance Analysis” section of the report. Use complete sentences and reference specific data in your answers.
Task 2: Outlier Analysis#
To better understand the distribution of your data, you will visualize it using histograms. You will also calculate statistical outliers and use logical functions to automatically isolate defective springs.
Before Outlier Adjustment Histogram#
Navigate to the Task 2 tab.
Create a “before” histogram for Supplier C using exactly 10 bins to visually identify outliers.
Using the given values in Table 3, calculate the bin width by dividing the range by 10. Enter this calculated value in Table 3 with a formula. Do not round the table value.
When you set up the histogram, adjust the bin width to a whole number and set an underflow bin above the minimum value to make your bin edges whole numbers.
Format the histogram for a technical presentation by including an appropriate title, axis labels, and units.
Copy the formatted histogram into Task 2 on the report document.
Answer all analysis questions in the “Before Outlier Adjustment” section of the report. Use complete sentences and reference specific data in your answers.
Outlier Adjustment#
Use the provided mean and standard deviation in Table 3 to calculate your upper and lower outlier thresholds applying the standard deviation method (\(\pm 3\) standard deviations from the mean).
In “Column C” of the Raw Data table, use an
IFstatement combined with logical operators to flag a spring as “REJECT” if its constant falls outside your calculated thresholds, and “KEEP” if it falls within the acceptable range. Be sure to use appropriate absolute and relative cell referencing to lock your calculated threshold values.Copy only the acceptable data points (the rows flagged as “KEEP”) and paste them into the Updated Data table. Do not delete your original raw data.
In Table 4, use calculate the summary statistics (minimum, maximum, range, mean, median, variance, and standard deviation) using the Updated Data table. You will use this adjusted data for the rest of the assignment.
After Outlier Adjustment Histogram#
Create an “after” histogram with 10 bins for Supplier C using the Updated Data. Calculate a new bin width and set an appropriate underflow bin to make the bin edges whole numbers.
Using the updated values in Table 4, calculate a new bin width and enter it in Table 4 with a formula. Do not round the table value.
When you set up the histogram, adjust the bin width to a whole number and set an underflow to a reasonable value above your minimum.
Format the histogram for a technical presentation by including an appropriate title, axis labels, and units.
Copy the formatted histogram into Task 2 on the report document.
Answer all analysis questions in the “After Outlier Adjustment” section of the report. Use complete sentences and reference specific data in your answers.
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.