Professor Patty Coleman was in her office at Ivy University contemplating a truism. Both she and her husband Rob worked at Ivy U, but they couldn’t afford to send their twin sons there. They made enough money that they could not get any aid, yet not enough to afford Ivy’s nearly $100K/yr price tag, especially times two. Fortunately, the University of Vermont, the state in which they lived, was much more affordable. Both sons were majoring in materials engineering and planning on going to graduate school. Patty marveled that most parents didn’t know that grad school in engineering or the sciences was free, and the students even got a salary.
Patty was jarred from these conflicting thoughts be the telephone ringing.
“Patty Coleman, how may I help you?” she answered cheerfully.
“Professor Patty, it’s Maggie. The Excel® software tool Wen Cao developed has been a big hit. However, we now need to compare two samples. Do you have another student that could help us?” Maggie asked.
“Maggie, it’s great to hear from you. It just so happens that Mary Fritch needs a project. Let me have her get with you,” said Patty. “By the way, was that a baby bump I saw last time we were together?” Patty teased.
“Yes,” Maggie said, “I’m turning red, you were the first to notice.”
With Patty’s coaching, Mary set up a time with Maggie’s team to go over the problem.
“Can you explain what you are trying to do? “ Mary asked the team over Zoom.
“We need to compare two samples of transfer efficiency data. We found the mean of Sample 1 to be 95.26 and the mean of Sample 2 to be 100.6,” Mike Thomas responded.
“It seems obvious that sample 2 is better, but is it statistically better? We tried to figure out a way to use the one sample tool that Wen Cao gave us, but it was awkward and we questioned its validity,” Sharon Fox explained.
“I looked up in how to do a two sample hypothesis test in a statistics book and we were able to show that sample 2 was better, but it took us forever. We used calculators and had many data entry errors,” Pete Singer added.
“OK,” Mary said, “I think I can help. Give me a few days and I should be able to make an Excel® spreadsheet to perform the calculations.”
The team sent the data to Mary, and in a matter of days, she was able to make an Excel® spreadsheet that performed a two-sample hypothesis test. The next day, Mary was in a conference room at Benson Electronics meeting with the team.
She showed the Excel® program on a computer screen. See Figure 1.
Figure 1. Mary’s Excel® Spreadsheet to Perform an Hypothesis Test on Two Data Sets.
“I took the two data sets you gave me and developed this Excel® spreadsheet to analyze them,” Mary began.
“Notice the two sets of data are entered in columns A and B, whereas the confidence level is in cell D2. I assumed we did not want the proposed difference to be other than 0, but if we wanted to test a difference, we would enter it in G2. I kept the formatting that Wen developed. A blue cell with white figures is data input, whereas gray cells with black is output. From cell I2, note that the average of Sample 1 is statistically less than Sample 2 with 95% confidence, as expected,” Mary summarized.
“Can you explain some of the other gray cells? Specifically cells D10 through D15?” Steve Baker asked.
“D10 and D11 are the standard error of the mean for each sample. D12 is the pooled standard deviation, whereas D13 is the pooled “t score”. These terms are explained in Montgomery’s Statistics and Probability for Engineers. [i]
“I see the confidence interval of the mean for each sample is shown in cells G6 to I7,” Sam Burns said.
A few more questions and comments were shared and the meeting was wrapped up.
So, the engineers at Benson Electronics now have the software to perform hypothesis tests for one or two samples. But what if they have three samples or more?
Using Analysis of Variance to Analyze Voiding Data
Ivy University Professor Patty Coleman had never been to SMTA Pan Pacific, and she had decided to take the plunge. She was surprised that airfare from the East was only in the $600 range, but admittedly it was a long trip. However, the thought of taking a break from New England’s harsh winter for a week was enticing. The conference was on Big Island, Hawaii, this year. It seemed the volcano Kilaeua was active, and the island had excellent astronomy tours. So, she was excited about the prospects. Suddenly her iPhone buzzed. It was a text from Maggie Benson.
“Professor Patty, the two hypothesis testing Excel® spreadsheets your students developed have been extremely helpful, but now we need to perform analysis on four samples. The samples come from experiments we have run to minimize voiding. I seem to remember that analysis of variance (ANOVA) is used for this type of data. Can you help us again?”
It is strange how this always seems to work out, but one of her students, Megan Williams, needed a project for her degree work, so the timing was perfect. However, it took quite a bit more time to create this Excel® software tool. After several meetings with the Benson Electronics team, Megan finally had a finished software tool.
“This took a little longer to develop than I thought it would. One of the challenges was that I needed a metric called the “Tukey Number,” which doesn’t exist online or in Excel®, so I had to create a table for it,” Megan explained. “I then took the voiding data you sent and analyzed it,” she continued. See Figure 2.
Figure 2. Megan’s ANOVA Output from Excel®.
“The data is inputted in columns A through D. The results are in the gray cells. As you can see, the averages are in cells G3 through G6. Sample 1 is decisively lower, Samples 2 and 3 are about the same, and Sample 4 is distinctly higher. You can see from cells G9 to H14 that all differences are significant, except Samples 2 and 3. Are there any questions?” Megan asked.
“What is the confidence level?” Sam Johnston asked.
“That’s a good question. It is 95%. The Tukey number, that I mentioned before, is hard to find for other confidence levels, so at this point, only 95% confidence levels can be calculated,” Megan said. “I also used Excel® to calculate box plots of the data.” See Figure 3.
Figure 3. Box Plots of the Data.
The box plots give a good visualization of the data, but do not give a sense of the statistical significance calculated.
The meeting was adjourned and the Benson Electronics team now had statistical tools to analyze data for one, two, three, four, five and six samples.
If anyone would like a copy of any of these software tools, send me a note at [email protected].
Cheers,
Dr. Ron
[i] Statistics and Probability for Engineers, Montgomery Douglas C., J Wiley and Sons, 4th edition PPF 354, 2007.
