Conditionally format: Only if unique identifier and exceeds % of previous record

[JJ22]

So my problem pretty much is as the title states. I basically want to design a way to test if a patient's value for a parameter is abnormal compared to the rest of their values (previous record, adjacent records, average of all their records). I am not sure on the best method. For example, I might want to be able to flag blood pressures that are 25% greater or smaller than their other records as a way to systematically find which records I need to manually check for validation.

I want to conditionally format cells only if:
A) they have the same unique patient ID (PID) found in the first column
B) The value is 15% or greater than the previous record (or, even better, greater / lower than 15% of adjacent records values, or best, 15% against their average)

Each patient has a record per day of visit. I have sorted by newest date and then PID.
Example.ConditionalHighlight.xlsx
I have already tried this for SBP (you can see my example as green colored)
Format values where this formula is true: =J2/1.15>J3 and Applies to =$J:$J
While this works, for some cells (marked as good), when it is compares records from different people, or worse, blanks, it fails (WRONG).

What is the best way to go about solving this? I already have conditional formatting for extreme highs or lows a column, but nothing to figure out relative to their own records.

Any help would be appreciated. I'm just really tripped up on how to design a formula find "I want to highlight this cell based on this parameter, ONLY IF they have the same record."

[FDibbins]

I tested this on column J, and it looks like it works, but you can judge that better than me. it did not pull out a TRUE for row 8, because both sets of data are the same, so +- 15% doesnt work for that. anyway, give it a shot and let me know...

=AND(J2<>"",OR(J2<=AVERAGEIF($A$2:$A$35,A2,$J$2:$J$35)*0.85,J2>=AVERAGEIF($A$2:$A$35,A2,$J$2:$J$35)*1.15))

[benishiryo]

hi JJ22. if you are indeed using Excel 2003 as stated, then replace the AVERAGEIF formula provided by FDibbins & use SUMIF divided by SUMPRODUCT like this:

Please Login or Register  to view this content.

and if your requirement is less/more than 15%, then remove the "=" signs beside the "<" & ">"

[FDibbins]

Thanks beni I based my assumption of 200+ on the .xlsx file the OP uploaded

[benishiryo]

sure thing~ just providing a backup.
=)

[JJ22]

I'll try this out today. Forgot my flash drive at my office with the document... doh.

P.S. I can't believe you caught that I had 2003 in profile. Talk about level of detail! I've changed it to reflect what I use.

[JJ22]

Works like a charm! It averages the values for each patient and then compares the cell to the average if it is 15%+/-. I think this will work. I'll play with it tonight and report back.

[FDibbins]

happy to help and thanks for the rep