In excel, conditionally format numbers if they are less than or greater than another number by 10%

Please see the attached file.  I’d like to
-      conditionally format the numbers in column A such that for each group of three numbers if the lowest number is more than 10% lower than the next nearest number the low number turns yellow, and
-      conditionally format the numbers in column A such that for each group of three numbers if the highest number is greater than 10% higher than the next nearest number the high number turns yellow
-      drag the conditional formatting from the first group of three numbers to the bottom of the column

Thanks for your help.
Who is Participating?
barry houdiniCommented:
It might be better to do this with a helper column on the worksheet, because, as Aikimark says, it's tricky to make the formula apply to each range of 3 cells only.....but you can use OFFSET to do that.

I selected column A and applied this conditional format formula with yellow fill


That highlights the cells you wanted.....and if you now add more blocks of 3 it should work all the way down the column, see attached

regards, barry
can you enforce a sorting of the three items in each set?
DaveAuthor Commented:
Not for this situation.
Each group will need to have a conditional formatting formula that is based on the cell value being equal to something like this:

Open in new window

Each block of three would need to have its own conditional formatting with a different range ($A$4:$A$6, $A$7:$A$9, etc.)

There might be a way to generalize the formula, so that the ranges self-adjust to blocks of three
For your examples, column A and column C conditional formats would be:

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.