First of all, I have already benefited several times from Chandoo.org and I just showed my manager this site and he has already found something helpful.
Second, I consider myself an Excel Power user here at where I work, but I also know that I may only know 5 or 10% of what Excel is capable of...
Also, one of my big regrets is that I do NOT know any coding... so my first request would be help that avoids code if possible. I also know little to nothing about the use of pivot tables.
So, here is my project... We have 2 test units that measure the torque of a plastic part we mold. This part is an adjustment foot for a piece of household equipment. It basically has 3 functional threads and we capture the peak torque from clockwise turn 1, 2 and 3 and then the peak torque from counter clockwise turns 3, 2 and 1. Then we calculate an average of the CW peak values and the average of the CCW peak values. Then when we accept the test results, both machines dump their data into a comma delimted text (*.csv) file.
Below is one line of the csv file.
2,9/8/2015 16:01,9/8/2015 15:59,A1,WH571-BLK,26,3.34261,3.279764,5.042257,3.88821,2.992891,2.987271,4.587745,3.522636,Sample Test
Here are the descriptions of each cell:
2, is the tester number
9/8/2015 12:01, is the date/time molded,
9/8/2015 15:59, is the date/time inspected,
A1, is the box number the samples were from
WH571-BLK, is the mold number and color
26, is the mold cavity number
3.34261, is the peak torque from CW turn #1
3.279764, is the peak torque from CW turn #2
5.042257, is the peak torque from CW turn #3
3.88821, is the average for CW turns 1, 2 and 3
2.992891, is the peak torque from CCW turn 3
2.987271, is the peak torque from CCW turn 2
4.587745, is the peak torque from CCW turn 1
3.522636, is the average for CCW turns 1, 2 and 3
Sample Test, is a comments field
What I need to do is create a dashboard that first looks at how all 6 molds are doing in relation to their process capability. Ideally, the dashboard would show a page with a chart for each mold CW and CCW data and have a green border if the statistical capability was good. A yellow border if the statistical capability was questionable and a red border if there was a problem with either points out of control or with statistical capability.
If you would be willing to help me on this project and wanted me to send you a copy of a sample csv file or some additional information, you can e-mail me at
thenson(at)wabashplastics(dot com)
as a second phase of this, I would like to be able to select a specific mold number and either look at the data by week or by a specified date range.
You help would be greatly appreciated.
thenson
Second, I consider myself an Excel Power user here at where I work, but I also know that I may only know 5 or 10% of what Excel is capable of...
Also, one of my big regrets is that I do NOT know any coding... so my first request would be help that avoids code if possible. I also know little to nothing about the use of pivot tables.
So, here is my project... We have 2 test units that measure the torque of a plastic part we mold. This part is an adjustment foot for a piece of household equipment. It basically has 3 functional threads and we capture the peak torque from clockwise turn 1, 2 and 3 and then the peak torque from counter clockwise turns 3, 2 and 1. Then we calculate an average of the CW peak values and the average of the CCW peak values. Then when we accept the test results, both machines dump their data into a comma delimted text (*.csv) file.
Below is one line of the csv file.
2,9/8/2015 16:01,9/8/2015 15:59,A1,WH571-BLK,26,3.34261,3.279764,5.042257,3.88821,2.992891,2.987271,4.587745,3.522636,Sample Test
Here are the descriptions of each cell:
2, is the tester number
9/8/2015 12:01, is the date/time molded,
9/8/2015 15:59, is the date/time inspected,
A1, is the box number the samples were from
WH571-BLK, is the mold number and color
26, is the mold cavity number
3.34261, is the peak torque from CW turn #1
3.279764, is the peak torque from CW turn #2
5.042257, is the peak torque from CW turn #3
3.88821, is the average for CW turns 1, 2 and 3
2.992891, is the peak torque from CCW turn 3
2.987271, is the peak torque from CCW turn 2
4.587745, is the peak torque from CCW turn 1
3.522636, is the average for CCW turns 1, 2 and 3
Sample Test, is a comments field
What I need to do is create a dashboard that first looks at how all 6 molds are doing in relation to their process capability. Ideally, the dashboard would show a page with a chart for each mold CW and CCW data and have a green border if the statistical capability was good. A yellow border if the statistical capability was questionable and a red border if there was a problem with either points out of control or with statistical capability.
If you would be willing to help me on this project and wanted me to send you a copy of a sample csv file or some additional information, you can e-mail me at
thenson(at)wabashplastics(dot com)
as a second phase of this, I would like to be able to select a specific mold number and either look at the data by week or by a specified date range.
You help would be greatly appreciated.
thenson
