Forum Discussion
Identifying Outlier Score
Hi everyone!
I am having trouble creating a conditional formula that highlights any score with a difference >= 3 when comparing it to a range of provided scores. See picture below for reference. Thank you!!!
tstephen15 I assume you already have a provided range of scores in $D$2:$D$8 against which you compare each score on each row of column A to see if the difference between the score in column A with the min (lowest bound) or with the max (highest bound) of the provided range is >=3 (not sure this is what you mean by stating "score with a difference >= 3 when comparing it to a range of provided scores").
You then select the whole section in column A and apply conditional formatting/New rules/use formula to highlight cells (the last one in the list of available rule types) and type the formula =OR(ABS(A2-MIN($D$2:$D$8))>=3,ABS(A2-MAX($D$2:$D$8)>=3)) and select the formatting you wish.
Pls see the attached workbook as a sample.
2 Replies
tstephen15 I assume you already have a provided range of scores in $D$2:$D$8 against which you compare each score on each row of column A to see if the difference between the score in column A with the min (lowest bound) or with the max (highest bound) of the provided range is >=3 (not sure this is what you mean by stating "score with a difference >= 3 when comparing it to a range of provided scores").
You then select the whole section in column A and apply conditional formatting/New rules/use formula to highlight cells (the last one in the list of available rule types) and type the formula =OR(ABS(A2-MIN($D$2:$D$8))>=3,ABS(A2-MAX($D$2:$D$8)>=3)) and select the formatting you wish.
Pls see the attached workbook as a sample.
hynguyenThank you so much, that was exactly what I was looking for!!!!!
