Running Metrics

[REDesigner09]

Hi,

I'm creating a Rejects Log Metrics file. Currently in this file, there are 3 worksheets as follows:

1) 2011 Rejects Data Log

-- This is suppose to be use for all data entries


2) Manufacturing Imperfections

-- This worksheet is extracting various defects from the 2011 Rejects Data Log worksheet

3) Control Chart

-- Chart showing Control Limits, but I may change it or make another chart with a Pareto &/or Pie Charts. Not worried about this at the moment.


I could use some assistance with the Manufacturing Imperfections worksheet

Right now, I have a table for all 52 weeks with some 6 Sigma calculations. As of now, it's averaging or calculating all 52 weeks.

I would like to modify the formula so it's a running calculation, such as if the date or FW (Fiscal Week) is greater than today's date, the columns only calculate current & previous FW's.

I included a sample file for review. I'm seeking assistance to modify the formulas in the yellow box areas as described above.

Also, in columns E - M, how do I not show '0', if FW is greater than current FW?

I'm using Excel 2003.

Thanks for the assistance.

 

[REDesigner09]

Hi,

Perhaps, this will help explain what I am seeking to modify my formulas:

I have columns with several imperfections labeled & listed out across, according to Fiscal Weeks (FWs) & goes from FW 1 -to- FW 52.

I'm trying to run a weekly metrics table that will calculate from FW 1 to current FW or within specified FW durations.

If looking at the sample file, I have handful of columns to calculate Mean, Standard Deviations, LCL & UCL.

At the moment, it's calculating all 52 weeks or averaging all 52 weeks with the this fomula:

=AVERAGE($R$18:$R$69)

How can I modify this formula to calculate specified FW durations or to current FW?

Thank you.

 

[kris44]

I've modified your spreadsheet and put some formulas in Rows 18 & 19 (Also played around with some of your Defect Numbers)

You can see the formulas to use are INDIRECT & ADDRESS, hopefully with the examples given you can work out the remaining formulas you need

 

[REDesigner09]

Hi Kris44,

Thanks for your feedback. Your formulas are a bit different than what I have in mind, but if they work, that's great.

I'm not quite understanding these formulas.

In the, "Manufacturing Imperfections" worksheet, the "Imperfection" are suppose to be extracting the different types from the, "2011 Rejects Data Log" worksheet, which I have or had.


Now, I'm trying to validate my formulas in columns O through U by getting some running calculations - To be able to calculate from the first week of the year to the current fiscal week.

I did manage to figure out what was initially wrong with my formulas to calculate:

Mean (Weekly Average) "Weekly
Cum. Rolling Ave. of Defects"
Sample Mean (Average of All Means)
Standard Deviation
Sample Standard Deviation
Lower Control Limit
Upper Control Limit

These columns are calculating, but I'm not sure if my numbers are accurate, especially when looking at my Pareto (just added) & Control Charts.

In the charts, there is a big spike which may be the result of the actual data, but I want to validate this & not an issues with my formulas.

Can you & other check my formulas or suggest other formulas that will make my calculations correct & charts more reasonable.

I attached an updated Running Metrics for review.

Thanks everyone for assisting.