SOLVED

Identifying Outlier Score

Copper Contributor

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_0-1590341296549.png

 

2 Replies
best response confirmed by tstephen15 (Copper Contributor)
Solution

@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!!!!! 

1 best response

Accepted Solutions
best response confirmed by tstephen15 (Copper Contributor)
Solution

@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.

View solution in original post