Transforming a Decision Tree into Excel

M

mzeeshanz

#1
Dear All!

I am transforming a decision tree (diagram attached herewith) into an excel worksheet - but i stuck up in the third question as the formula I put there is not working. I am working over formula to have next question on the basis of previous question and its answer of that. Do somebody look into the attached excel file and give some idea "what's wrong with the formula for the third question?".

EXCEL Sheet View attachment HA-sample.xls

DIAGRAM View attachment RECOMMENDED INTERNATIONAL CODERAL PRINCIPLES OF FOOD HYGIENE 30.pdf

Prior thanks,
Regards:
Zeeshan
 
Elsmar Forum Sponsor

Sajith

Starting to get Involved
#2
Dear Zeeshan,

Tried editing your file.

I have edited the file based on the assumption that "Step is OPRP" reflects that the situation is "Not a CCP and you have to proceed to STOP" (as per your diagram).

Hope this is what you wanted.

Regards,
Sajith.
 

Attachments

M

mzeeshanz

#4
Dear Sajith and Mauri,

Thank you for you replies and efforts.

But my problem is not resolved. Actually you both guys, IMO, have missed the key point. The each succeeding question depends on the combination of two situations. The previous question and its answer. The IF formula have to evaluate two logical situations for evaluation. Lets see following example:

============================
S3 Do Control Measure(s) exist?

S4 Is control at this step necessary for safety?

S5 Is the step specifically designed to eliminate or reduce the likely
occurrence of a hazard to an acceptable level?

S6 Could contamination with identified hazard(s) occur in excess of
acceptable levels or could these increase to unacceptable levels?

S7 Will a subsequent step eliminate identified hazard(s) or reduce likely
occurrence to an acceptable level?

S8 Modify step, process or product.

S9 Step is Not a CCP

S10 Step is a CCP.

==================================
Q1 = May be Yes or No

Q2 = S5 if Q1=Yes
= S4 if Q1=No

Q3 = S8 if Q2 = S4 and its Answer is Yes
= S9 if Q2 = S4 and its Answer is No
= S10 if Q2 = S5 and its Answer is Yes
= S6 if Q2 = S5 and its Answer is No

Q4 = "" if Q3 = S8 and its Answer is Yes
= "" if Q3 = S8 and its Answer is No
= "" if Q3 = S9 and its Answer is Yes
= "" if Q3 = S9 and its Answer is No
= "" if Q3 = S10 and its Answer is Yes
= "" if Q3 = S10 and its Answer is No
= S7 if Q3 = S6 and its Answer is Yes
= S9 if Q3 = S6 and its Answer is No

Q5 = S9 if Q3 = S7 and its Answer is Yes
= S10 if Q3 = S7 and its Answer is No
= "" if Q3 = S9 and its Answer is Yes
= "" if Q3 = S9 and its Answer is No

==============================================

Hope the problem situation has been clear now. IMO, we have to use AND or OR condition or a combination thereof with the IF formula to get resolved this situation.

Regards:
Zeeshan
 
Last edited by a moderator:

Sajith

Starting to get Involved
#5
Dear Zeeshan,

In order to answer Q5 correctly, you are not alloting s7 as an aswer to Q3 in the first place.

Regards,
Sajith.
 
M

mzeeshanz

#6
Dear Sajith,

First of all, sorry for a mistake. In previous post I have written Q3 instead of Q4 in the last segment. Please read the last segment as below:
====================================
Q5 = S9 if Q4 = S7 and its Answer is Yes
= S10 if Q4 = S7 and its Answer is No
= "" if Q4 = S9 and its Answer is Yes
= "" if Q4 = S9 and its Answer is No
==============================

If the confusion persists in understanding the problem then I think I have not explained the problem well.

Let's drill into the problem from another direction:

Look at the diagram I have attached in the first post.

First do not confuse with Q1, Q2...... on the diagram with Q1, Q2.....I've written in last post.

Question No. 2 may be:
----------------
"Is control at this step necessary for safety?"
-----OR--------
" Is the step specifically designed to eliminate or reduce the likely
occurrence of a hazard to an acceptable level?"
----------------
depending upon the answer to Question No.1. "Do Control Measure(s) exist?"

It means it is not necessary that all the questions (from 1 to 5 will be asked). Probably the answer to any intermediate question leads to a decision.

Hope the remaining confusion has been removed!

Regards:
Zeeshan.
 
Last edited by a moderator:
M

mzeeshanz

#8
Dear Sajith!

It has, of course.:D

Thanks a lot for your continual and conclusive support!

Regards:
Zeeshan.
 
Thread starter Similar threads Forum Replies Date
H Minitab DOE Analysis of Data and Transforming Using Minitab Software 4
V Transforming the Attitude and Culture towards Documentation and Process Controls US Food and Drug Administration (FDA) 2
R Transforming or not Transforming - Dealing with Non-Normal Data Statistical Analysis Tools, Techniques and SPC 10
M Transforming your Data - Analyzing a DOE Six Sigma 5
C Non-Normally Distributed Data - Nonparametrics vs. Transforming data Using Minitab Software 6
J Non-Normal Data - Transforming the Data to Normal Statistical Analysis Tools, Techniques and SPC 12
dgrainger New decision by MHRA on disclaimers - addition of "manner of use as perceived by the consumer" UK Medical Device Regulations 1
M Clinical Decision Support Software Question 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 2
J Need Change Control Yes/No Decision Tree Template ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 1
Steve Prevette Informational I am presenting a webinar Thursday - "Data Driven Decision Making" - 19 November 2020 Statistical Analysis Tools, Techniques and SPC 5
J Masters Degree - Advice about a decision I"m going to be making Career and Occupation Discussions 9
A Medical Device Vigilance decision tree for Japan for class 2 devices. Japan Medical Device Regulations 1
M Informational US FDA Draft Guidance – Clinical Decision Support Software Medical Device and FDA Regulations and Standards News 0
M Informational EU – Commission Implementing Decision (EU) as regards the designation of expert panels in the field of medical devices Medical Device and FDA Regulations and Standards News 0
M Informational Commission Implementing Decision (EU) 2019/939 of 6 June 2019 designating issuing entities designated to operate a system for the assignment of Unique Medical Device and FDA Regulations and Standards News 0
M Informational US FDA – Patient-Reported Outcomes (PROs) in Medical Device Decision Making Medical Device and FDA Regulations and Standards News 0
M Informational US FDA – Patient Preference Information (PPI) in Medical Device Decision-Making Medical Device and FDA Regulations and Standards News 0
J Medical Device Vigilance decision tree FORM - Please share yours EU Medical Device Regulations 15
O Reinstating a disqualified supplier—questions to ask before making a decision Supplier Quality Assurance and other Supplier Issues 2
P Is it possible to make an educated decision using a very very small sample size? Inspection, Prints (Drawings), Testing, Sampling and Related Topics 3
M IATF 16949 Cl. 8.7.1.4 - Risk analysis for decision making about rework IATF 16949 - Automotive Quality Systems Standard 2
L A Complete Control Chart Decision Tree Statistical Analysis Tools, Techniques and SPC 5
C IATF 16949 Cl. 8.4.1.2 d) Multidisciplinary Decision Making IATF 16949 - Automotive Quality Systems Standard 2
T Policy or Good Guidance for a Traceability Decision Tree (Serial and Batch Numbers) Misc. Quality Assurance and Business Systems Related Topics 4
somashekar Uptodate.com: A clinical decision support resource - Point-of-care decisions Medical Information Technology, Medical Software and Health Informatics 1
N Decision to file a new 510(k) - ETO (Ethylene Oxide) Sterilization Other US Medical Device Regulations 4
Chennaiite Can an Auditor question Top Management decision to approve deviation? IATF 16949 - Automotive Quality Systems Standard 30
J Decision Tree using for CCP (Critical Control Points) Food Safety - ISO 22000, HACCP (21 CFR 120) 2
AnaMariaVR2 Structured Approach to Benefit-Risk Assessment in Drug Regulatory Decision-Making Pharmaceuticals (21 CFR Part 210, 21 CFR Part 211 and related Regulations) 0
S Supplier Disapproval Decision Process Federal Aviation Administration (FAA) Standards and Requirements 2
N Decision Making... (joke) Funny Stuff - Jokes and Humour 1
R Risk Analysis and Hazard Identification concerning Clinical Decision Support Systems ISO 14971 - Medical Device Risk Management 1
P CMC Decision 3 - Manufacturers Address Content Requirements EU Medical Device Regulations 4
Sidney Vianna AS91X0 Transition Deadline - New IAQG Decision - 2012 AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 54
L Decision Matrix to help Operator driven Continuous Improvement Lean in Manufacturing and Service Industries 3
F ISO 11137 - Flow Chart | Decision Tree sterilization Other Medical Device Related Standards 4
Q Exactly which are the records needed by ISO9001? or is it my decision? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 9
J Rules for Achieving IATF Recognition 3rd Edition FAQ?s - Certification Decision 5.12 IATF 16949 - Automotive Quality Systems Standard 2
K Seeking help in making a decision - Project behind schedule Quality Manager and Management Related Issues 11
S What decision rules should be applied for x bar-R chart? Statistical Analysis Tools, Techniques and SPC 10
R EU Medical Device Change Decision Tree (when to notify the NB) EU Medical Device Regulations 4
Z Is adoption of ISO 9001 a strategic decision? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 24
S Federal Register - Software Reclassification (MDDS) - Final Decision by FDA yet? Other US Medical Device Regulations 3
A Special 510k Update - Cease Sales and Distribution until a Decision is Rendered? Other US Medical Device Regulations 11
L Can one question a 510(k) decision? 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 5
Manix The Toughest Decision I have ever had to make! Director or Quality Engineer Career and Occupation Discussions 16
T Change Control Decision Tree - When medical devices that are approved with a PMA Document Control Systems, Procedures, Forms and Templates 9
D PA, CA and Risk-Based Decision Making - Need Input Preventive Action and Continuous Improvement 7
P This Is It - Tomorrow is the final decision - Finished the certification audit ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 12
D Flow Chart Decision Symbol Arrows - Which flowchart is correct? Process Maps, Process Mapping and Turtle Diagrams 32

Similar threads

Top Bottom