RPN Ranking - Put a formula in an access database or Excel .xls spreadsheet

[Spanner]

Hi, I am not sure I am in the right forum, I don’t usually venture into automotive but we have a Process FMEA system based on QS9000 which you experienced guys may be able to help me on.

We have a scoring system (severity x occurrence x detection) which gives us an RPN. QS9000 states assessment for preventive action should first be directed at high severity, high RPN. In general practice when the severity is 9 or 10 special attention must be given regardless of RPN. Once 9 and 10s have been addressed then other failure modes are addressed.

I want to be able to put a formula in an access database or Excel spreadsheet to sort the failure modes into the order that they should be addressed – this is not as simple as ranking them in the RPN order as severity should have a greater weight than the rest. Also if severity is 9 o r 10 but detection is 1 should this be at the bottom of the list as detection is certain?

When addressing the 9s and 10s I cannot always reduce the severity – we are a contract manufacturer and usually have no control over design – so these stay at the top of my list even though I have done everything I can to improve detection and reduce occurrence.

I only want my engineers to concentrate on the problems we can prevent (after once determining we can’t improve on some), I don’t want to look at the bottom end of the table where effort far outweighs benefit.

Has anybody got a formula or weighting system and cut off score they use to ensure only the important failure modes are addressed?
I want to make this foolproof for my engineers!

Thanks

[Rob Nix]

Let's start at the bottom of your post...

Originally Posted by Spanner

I want to make this foolproof for my engineers!

"Anyone who thinks something can be fool-proofed has not considered the ingenuity of a fool". 'nuf said.

Now, regarding the rest, i.e., RPNs and ranking...

(everyone else is thinking, oh boy, here goes Rob again)

Your post perfectly demonstrates the kind of complications that arise using rankings and when assuming the use of RPNs is some kind of global law, like gravity, that you cannot escape from. First, If your customer does not DEMAND that you use RPNs, don't use them!

Originally Posted by Spanner

we are a contract manufacturer and usually have no control over design

Second, high severity concerns should have arose when going through contract review.

Regarding the rest...

Originally Posted by Spanner

I only want my engineers to concentrate on the problems we can prevent

Your engineers (and other talented people) are the key here. The value of the FMEA exercise is in the brainstorming. For each line item (failure mode), simply determine - after discussion - what issues you will address and which issues you will likely leave alone. That's it!

Don't worry about "a formula or weighting system and cut off score they use to ensure only the important failure modes are addressed". That is a needless waste of effort. You will probably find that after you go through all that work, in the end you will still choose to do some things on line items that didn't make the cut, and won't do things on some that did.

Anyway, that's my humble but quirky opinion.

[Jim Wynne]

Originally Posted by Rob Nix

Anyway, that's my humble but quirky opinion.

Excellent advice. GM, to use the most obvious example, is going to he11 in the proverbial handbasket because good sense is considered "quirky."

[Spanner]

Perhaps I should have said I want to make it engineer proof!
I have introduced FMEAs here and it took a while to get the engineers on board - they needed lots of guidance and motivation. To simplify the system I based the scores on 1 - 5 for O and D and 1 - 6 for S. I then used a matrix that said if the severity is 5 or over a definite action is required to address the risk and if the severity and occurrence is over 3 you need to have a good try at addressing the risk. Anything else can be ignored. That works quite well as a first pass, but of course severity if its at a 5 or 6 can't usually be reduced at a process FMEA so the engineers get frustrated they can't get these off their list at following reviews. The rest they can usually get down below the 3s and they then consider their job is done and move onto the next exciting job. Without these guidelines the engineers can take things to the nth degree especially if the brainstorming includes coffee and chocolate biscuits (thats the motivation!) and then they start to address things that are not really problems. The engineers themselves have asked for the formula so they can focus on the highlighted problems. Engineers like formulae!
I guess I could cut out the chocolate biscuits.

[Icy Mountain]

I am an Electrical Engineer so I get it. In Excel, hit F1, or go to Help and use the phrase "conditional formatting". Any kind of sorting will require someone to press a button and actually sort.

Conditional formatting will let you shade cells with yellow or red or whatever, based on the information in the cell.

In Access, your forms or tables can be sorted according to any of the data upon opening. In Form Design, right click outside of the form, click Properties and look at the Order By property. You can design your own. Put this in as data:
(FMEAdata.Severity) DESC
FMEAdata is the table that has the raw data that you are displaying with the form. Severity is the item in the table that stores your severity numbers. DESC will display the records in the table in descending order.

You can get to the Order By property the same way in Table Design.

[Spanner]

Thanks Icy,

You are on the right track….I just need the magic formula. Does any one use any commercially available FMEA software - do these highlight the important things to address using some predefined criteria or is left up to the user?

[JRKH]

Originally Posted by Icy Mountain

I am an Electrical Engineer so I get it. In Excel, hit F1, or go to Help and use the phrase "conditional formatting". Any kind of sorting will require someone to press a button and actually sort.

Conditional formatting will let you shade cells with yellow or red or whatever, based on the information in the cell.

In Access, your forms or tables can be sorted according to any of the data upon opening. In Form Design, right click outside of the form, click Properties and look at the Order By property. You can design your own. Put this in as data:
(FMEAdata.Severity) DESC
FMEAdata is the table that has the raw data that you are displaying with the form. Severity is the item in the table that stores your severity numbers. DESC will display the records in the table in descending order.

You can get to the Order By property the same way in Table Design.

Another useful tool is to create a query in access. Then in the Criteria section for severity, put [>7] (without the brackets). Then in sort field under RPN put decending. This will return only those records with a severity of 8 or above and sort by RPN highest to lowest.
Beyond this it gets a bit complicated in Access and I tend to prefer Icy's solution in Excel.

You can create a report that will sort by highest severity and then by overall RPN too.
Go to new report wizard and select your table,
Enter the fields you want on your report. (click next)
Don't select any groupings (next)
This screen should allow you to select up to 4 items to sort by.
Enter severity in the first bix and select decending.
Enter RPN in the second box and select Decending (next)
Select your desired formatting (next)
Name the report (open)

I think I got this right.

Hope this helps

James

[Icy Mountain]

Originally Posted by Spanner

You are on the right track….I just need the magic formula. Does any one use any commercially available FMEA software - do these highlight the important things to address using some predefined criteria or is left up to the user?

Sorry, I used up all my magic formula making sure that my wife gets everything she wants for her birthday and Christmas. I can't help with commercial software. I roll my own in Access, Excel and Word.