×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Running Metrics

Running Metrics

Running Metrics

(OP)
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.

RE: Running Metrics

(OP)
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.

RE: Running Metrics

(OP)
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.



 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources