SOLVED

Identifying Outlier Score

%3CLINGO-SUB%20id%3D%22lingo-sub-1414968%22%20slang%3D%22en-US%22%3EIdentifying%20Outlier%20Score%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1414968%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone!%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20trouble%20creating%20a%20conditional%20formula%20that%20highlights%20any%20score%20with%20a%20difference%20%26gt%3B%3D%203%20when%20comparing%20it%20to%20a%20range%20of%20provided%20scores.%20See%20picture%20below%20for%20reference.%20Thank%20you!!!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22tstephen15_0-1590341296549.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194159i1E88255651C9F4E1%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22tstephen15_0-1590341296549.png%22%20alt%3D%22tstephen15_0-1590341296549.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1414968%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1415292%22%20slang%3D%22en-US%22%3ERe%3A%20Identifying%20Outlier%20Score%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415292%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F678359%22%20target%3D%22_blank%22%3E%40tstephen15%3C%2FA%3E%26nbsp%3BI%20assume%20you%20already%20have%20a%20provided%20range%20of%20scores%20in%26nbsp%3B%3CSPAN%3E%24D%242%3A%24D%248%3C%2FSPAN%3E%20against%20which%20you%20compare%20each%20score%20on%20each%20row%20of%20column%20A%20to%20see%20if%20the%20difference%20between%20the%20score%20in%20column%20A%20with%20the%20min%20(lowest%20bound)%20or%20with%20the%20max%20(highest%20bound)%20of%20the%20provided%20range%20is%20%26gt%3B%3D3%20(not%20sure%20this%20is%20what%20you%20mean%20by%20stating%20%22%3CSPAN%3Escore%20with%20a%20difference%20%26gt%3B%3D%203%20when%20comparing%20it%20to%20a%20range%20of%20provided%20scores%22).%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EYou%20then%20select%20the%20whole%20section%20in%20column%20A%20and%20apply%20conditional%20formatting%2FNew%20rules%2Fuse%20formula%20to%20highlight%20cells%20(the%20last%20one%20in%20the%20list%20of%20available%20rule%20types)%20and%20type%20the%20formula%20%3DOR(ABS(A2-MIN(%24D%242%3A%24D%248))%26gt%3B%3D3%2CABS(A2-MAX(%24D%242%3A%24D%248)%26gt%3B%3D3))%20and%20select%20the%20formatting%20you%20wish.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EPls%20see%20the%20attached%20workbook%20as%20a%20sample.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1425025%22%20slang%3D%22en-US%22%3ERe%3A%20Identifying%20Outlier%20Score%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425025%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675276%22%20target%3D%22_blank%22%3E%40hynguyen%3C%2FA%3EThank%20you%20so%20much%2C%20that%20was%20exactly%20what%20I%20was%20looking%20for!!!!!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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!!!!!