Formula to highlight whether two or more cells in a row have a diffence of 1.5

[darrickd805]

I have a scoresheet and would like to highlight splits in scores of more than 1.5. For example, in the example below how would I get the numbers 7, 9 and 9.5 to highlight because there is a difference of at least 1.5 or greater between them?

[AliGW]

Welcome to the forum.

Instructions about uploading your workbook are at the top of the page.

[darrickd805]

Thank you. I don't suppose you know a simple formula to use for the attached spreadsheet and question?

[Gregb11]

While having D4:H4 selected, go to Conditional Formatting and select "New Rule" and enter this formula:
=OR(MAX($D$4:$H$4)-D$4>1.5,MIN($D$4:$H$4)-D$4<-1.5)
Hopefully this works for you.
I've attached the file.

[darrickd805]

Thank you so much I really appreciate the help, I'm not great with more complex formulas. The only other feature I can think of would be if there a way to automate the highlights to only highlight the individual cells that have a split of 1.5 or greater. Then un-highlight if the split between the individual cells is resolved (i.e. less than 1.5 difference)? The current formula highlights the entire row when there is a split of 1.5 or greater, which is a great start. But ideally it would highlight just the individual numbers that met the split criteria. Thank you again for your help.

[Gregb11]

only highlight the individual cells that have a split of 1.5 or greater. Then un-highlight if the split between the individual cells is resolved

That's exactly what it does. When you open the file I attached, it only have 3 cells highlighted. If you change the values so that all cells are within 1.5 then the highlighting goes away.

[BMV]

Please Login or Register  to view this content.

[darrickd805]

Thank you all for your help. I really appreciate all of your examples.

[darrickd805]

Ahh I see, that works perfectly. Thank you again for your help. You are a lifesaver.