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

Spanner

Starting to get Involved
#1
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
 
Elsmar Forum Sponsor
R

Rob Nix

#2
Let's start at the bottom of your post...
Spanner said:
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!
Spanner said:
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...
Spanner said:
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

Staff member
Admin
#3
Rob Nix said:
Anyway, that's my humble but quirky opinion.
:agree1: Excellent advice. GM, to use the most obvious example, is going to he11 in the proverbial handbasket because good sense is considered "quirky."
 

Spanner

Starting to get Involved
#4
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.
 
#5
Technical Assistance Rather than Philosophy

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

Starting to get Involved
#6
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?
 
J
#7
In Access you can also use the Query

Icy Mountain said:
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
 
Last edited by a moderator:
#8
Spanner said:
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.
 
F

FlavioLima

#9
Hi Spanner,

If your concern is the formula and if you're going to use EXCEL conditional formatting only reservation is it's limited to 3 check conditions.
To use it, select cells you'll apply conditions, click menu <FORMAT>, <Conditional Formatting> and in new opened window choose pre-options according your needs.
Other way is to use cascading IF functions or a macro in EXCEL.
Best regards.

Flávio Lima
 

Spanner

Starting to get Involved
#10
Thanks for your input. I think I will leave it up to the common sense of my engineers whilst I rack my brains for a way of automating the FMEA. Still it will give me something to think about over Christmas.

I wish I had had some of your magic formula for my wife this Christmas - I have not got her anything yet and I hate shopping - that will be something else to think about over Christmas if I fail to magic up the goods.
 
Thread starter Similar threads Forum Replies Date
D Ranking Audit Effectiveness with RPN General Auditing Discussions 3
T RPN Thresholds and How Detection Fits In ISO 14971 - Medical Device Risk Management 5
W Is the RPN (risk priority number) in the PFMEA really a RPN without the detectability ISO 14971 - Medical Device Risk Management 4
N Timing for Closing High FMEA RPN Items FMEA and Control Plans 4
Moncia Recalculating RPN (FMEA Risk Priority Number) - Customer Request APQP and PPAP 10
M Requiring action on factors alone of RPN in FMEA APQP and PPAP 7
C PFMEA RPN - Feedback regarding adjusted RPN FMEA and Control Plans 3
N How to reduce RPN of Visual Inspection Process FMEA and Control Plans 6
S PFMEA RPN - At what RPN level do you take actions in the FMEA? APQP and PPAP 4
C How to handle Action Results in a new FMEA Review once we have a new RPN? FMEA and Control Plans 1
L FMEA - Calculating RPN = S*O*D FMEA and Control Plans 3
I RPN without Recommendation neither corrective actions FMEA and Control Plans 2
Q Why is the FMEA RPN = S*O*D? FMEA and Control Plans 10
G Determining the "Detection" Number Component of the FMEA RPN FMEA and Control Plans 5
K How do you determine what is acceptable base on an FMEA RPN FMEA and Control Plans 6
M Changing PFMEA (Process FMEA) RPN Values based on a Rejection FMEA and Control Plans 5
I PFMEA RPN Numbers Medical Device Risk Management FMEA and Control Plans 1
A PFMEA (Process FMEA) Detection Rating - Actions to Reduce RPN FMEA and Control Plans 3
K Do you have to use RPN in Medical Device Risk Analysis? Identification of Hazards ISO 14971 - Medical Device Risk Management 6
R When to take action if an FMEA RPN number is high? FMEA and Control Plans 43
G Relation b/n Process Failure Mode RPN Value and the associated Process Failure Cost FMEA and Control Plans 3
B Formula to calculate the Process Sigma from an RPN Six Sigma 17
E FMEA Action Plan Threshold (RPN) - Auditor says Action Plan for an RPN > 84 IATF 16949 - Automotive Quality Systems Standard 21
Howard Atkins FMEA RPN Reduction Process FMEA and Control Plans 4
M FMEA RPN Recalculation after Actions are (or are not) Taken FMEA and Control Plans 6
C PFMEA RPN rankings - Preventative action must be opened if RPN over 100 ISO 14971 - Medical Device Risk Management 9
R If two processes have same RPN NUMBER - which should have priority over the other? FMEA and Control Plans 3
T RPN of Action Results per the AIAG FMEA manual FMEA and Control Plans 5
N How to Calculate RPN in Design FMEA (DFMEA) vs. Process FMEA (PFMEA) FMEA and Control Plans 9
F What is a usual RPN level for requiring actions? FMEA and Control Plans 4
V Emphasizing Severity more than Occurrence and Detection vs. Overall RPN FMEA and Control Plans 20
L PFMEA (Process FMEA) issue about lower RPN - AIAG 3rd edition page 55 FMEA and Control Plans 13
B Corrective Actions Priority - Process FMEA (PFMEA) RPN Limits - Recommended Actions FMEA and Control Plans 20
R FMEA review, RPN reduction - Any "rule of thumb" as to what makes an RPN too high? FMEA and Control Plans 12
K RPN reduction - Occurrance Reduction - Reducing occurance of defect on line FMEA and Control Plans 1
A Does anyone actually do a Pareto of the Top 5 (highest) RPN's of PFMEA? FMEA and Control Plans 8
A Is there an established RPN number or score for a DFMEA? FMEA and Control Plans 1
G Assignation of RPN rating (Detection) on Visual Inspection where subsequent operation FMEA and Control Plans 18
R PFMEA - RPN - Severity, Occurrence and Detection - Which one does not change? FMEA and Control Plans 67
A Quick question about RPNs - Any RPN over 100 should be actioned? FMEA and Control Plans 9
J FMEA RPN Number and When to Recommend to Take Action FMEA and Control Plans 3
T Process FMEA (Failure Modes Effects Analysis) Severity Rating and RPN IATF 16949 - Automotive Quality Systems Standard 17
S FMEA - Reject a PPAP because the RPN number is not shown? QS-9000 - American Automotive Manufacturers Standard 6
S Process FMEA (PFMEA) RPN Corrective Action Plan FMEA and Control Plans 6
S FMEA RPN Corrective Action Plan QS-9000 - American Automotive Manufacturers Standard 1
R Defining the RPN cut off number - Working on the top 3- 5 high RPNs? FMEA and Control Plans 4
Kevin Mader FMEA Severity Index - Leading contributor to the RPN value FMEA and Control Plans 9
J RPN numbers in FMEA - It is not correct to go only by the RPN numbers when doing FMEA FMEA and Control Plans 12
blile Increasing PFMEA occurrence ranking after non-conformance FMEA and Control Plans 4
Ashok sunder Is it possible to reduce Risk likelihood and impact Post control Ranking after corrective action taken for risk? FMEA and Control Plans 1

Similar threads

Top Bottom