J
JJ22
So my problem pretty much is as the title states. I basically want t
design a way to test if a patient's value for a parameter is abnorma
compared to the rest of their values (previous record, adjacent records
average of all their records). I am not sure on the best method. Fo
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 systematicall
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, eve
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 dat
and then PID.
**See zip example**
I have already tried this for SBP (you can see my example as gree
colored)
Format values where this formula is true: =J2/1.15>J3 and Applies t
=$J:$J
While this works, for some cells (marked as good), when it is compare
records from different people, or worse, blanks, it fails (WRONG).
What is the best way to go about solving this? I already hav
conditional formatting for extreme highs or lows a column, but nothin
to figure out relative to their own records.
Any help would be appreciated. I'm just really tripped up on how t
design a formula find "I want to highlight this cell based on thi
parameter, ONLY IF they have the same record.
+-------------------------------------------------------------------
|Filename: Example.ConditionalHighlight.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=690
+-------------------------------------------------------------------
design a way to test if a patient's value for a parameter is abnorma
compared to the rest of their values (previous record, adjacent records
average of all their records). I am not sure on the best method. Fo
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 systematicall
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, eve
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 dat
and then PID.
**See zip example**
I have already tried this for SBP (you can see my example as gree
colored)
Format values where this formula is true: =J2/1.15>J3 and Applies t
=$J:$J
While this works, for some cells (marked as good), when it is compare
records from different people, or worse, blanks, it fails (WRONG).
What is the best way to go about solving this? I already hav
conditional formatting for extreme highs or lows a column, but nothin
to figure out relative to their own records.
Any help would be appreciated. I'm just really tripped up on how t
design a formula find "I want to highlight this cell based on thi
parameter, ONLY IF they have the same record.
+-------------------------------------------------------------------
|Filename: Example.ConditionalHighlight.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=690
+-------------------------------------------------------------------