Hi everyone. Thanks in advance.

I am trying to conditionally format a range of cells by applying Westgard rules for quality control in a clinical setting. The rules are as follows:

1. If value is within ± 2SD, then it "Pass" with green highlight
2. If value is greater than ± 3SD, then it "Fail" with red highlight
3. If value is greater than ± 2SD but less than ± 3SD, check range to see if two consecutive results (including itself) is greater than 2SD on the same side of the mean (i.e. +2SD). If so, "Fail".
4. If not, check to see if two consecutive results (including itself) in the range are greater than 2SD on opposite sides of the mean (> +2SD and < -2SD). If so, "Fail".
5. If not, check to see if the value is greater than ± 4SD from mean. If so, "Fail".
6. If not, "Pass"

Attached if a flow chart of how I would like the rules to be setup.

1-2S: Result between ± 2SD and ± 3SD From the assigned Mean
1-3SD: Result Greater than 3SD from the assigned mean
2-2S: Results between 2SD and 3SD as compared to the previous result; If 2 consecutive results are greater than 2SD on the same side of the assigned mean.
R-4S: Results between 2SD and 3Sd as compared to the previous result; If 2 consecutive results are greater than 2SD on opposites sides of the assigned mean.

Disregard 4-1S and 10X. If R-4S is No, Accept Run (Pass).

I've also attached an excel file to show how my spreadsheet is setup. I've also attached a guide that better explains the rules that I am trying to apply.

Example spreadhseet.xlsx
glb_bci_152294.pdf
Flow chart.png

Thanks again in advance. If I need to clarify anything, please let me know.