Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Conditionally format: Only if unique identifier and exceeds % of previous record
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. **See zip example** I have already tried this for SBP (you can see my example as green colored) Format values where this formula is true: =J2/1.15J3 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." |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find earliest record based on unique identifier from a list ofdata | Excel Discussion (Misc queries) | |||
Unique identifier | Excel Discussion (Misc queries) | |||
Unique identifier | Excel Worksheet Functions | |||
Unique identifier | Excel Programming | |||
Unique Identifier? | Excel Programming |