Running Metrics

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.

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.

Hi Cutter,

Thanks for the logical answer. Now, I messed something else in my formula, adding a couple of "If" statements that my LCL & UCL are not level.

I think my Mean (Daily Average) is not correct with a couple of imperfection columns being blanked. These columns are intended to add more imperfections if needed.

Hi Everyone,

Thanks for the feedback. For the most part, I think I got my metrics file working, but I'm not certain if my formulas are calculating properly, especially when adding or reviewing the Pareto & Control charts.
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
***. 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 (using Excel 2003).

Thanks everyone for assisting.

Hi,

If someone wouldn't mind, could you validate these formulas are calculating properly from my recently submitted file:

From Row 13 - Column - Mean (Weekly Average)

=IF(F13>$N$7,0,IF(F13<=$N$7,IF($O$10<>"",AVERAGE(G13:O13),IF($N$10<>"",AVERAGE(G13:N13),IF($M$10<>"",AVERAGE(G13:M13),0)))))

It's suppose to calculate the weekly Defect Averages by the amount or total defects listed

From Row 13 - Column- Weekly ***. Rolling Ave. of Defects

=IF(F13>$N$7,IF(Q13,Q13/AVERAGE($Q$11:$Q$62)/100,NA()),IF(F13<=$N$7,IF(Q13,Q13/AVERAGE($Q$11:$Q$62)/100,0)))

Suppose to provide a running cumulative average from 1st week of the year to the current. As of now, I have the formula looking at all 52 weeks, even 3/4 of year is un-accounted for. I"m not sure if this is the best method to use or if I should be using some other method / calculation to get cumulative averages

Thanks