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

Hello,
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.
Book1.xlsx
DaveAsked:
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

=OR(AND(MIN(OFFSET(A$1,FLOOR(ROWS(A$1:A1)-1,3),0,3))=A1,SMALL(OFFSET(A$1,FLOOR(ROWS(A$1:A1)-1,3),0,3),2)*0.9>A1),AND(MAX(OFFSET(A$1,FLOOR(ROWS(A$1:A1)-1,3),0,3))=A1,SMALL(OFFSET(A$1,FLOOR(ROWS(A$1:A1)-1,3),0,3),2)*1.1<A1))

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
cf-barry.xlsx
0
 
aikimarkCommented:
can you enforce a sorting of the three items in each set?
0
 
DaveAuthor Commented:
Not for this situation.
0
 
aikimarkCommented:
Each group will need to have a conditional formatting formula that is based on the cell value being equal to something like this:
=1.1*(SUM($A$1:$A$3)-MAX($A$1:$A$3)-MIN($A$1:$A$3))

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
0
 
aikimarkCommented:
For your examples, column A and column C conditional formats would be:
=1.1*(SUM(Indirect("A"&INT((ROW()-1)/3)+1&":A"&INT((ROW()-1)/3)*3+3))-MAX(indirect("A"&INT((ROW()-1)/3)+1&":A"&INT((ROW()-1)/3)*3+3))-MIN(indirect("A"&INT((ROW()-1)/3)+1&":A"&INT((ROW()-1)/3)*3+3)))
=1.1*(SUM(INDIRECT("C"&INT((ROW()-1)/3)+1&":C"&INT((ROW()-1)/3)*3+3))-MAX(INDIRECT("C"&INT((ROW()-1)/3)+1&":C"&INT((ROW()-1)/3)*3+3))-MIN(INDIRECT("C"&INT((ROW()-1)/3)+1&":C"&INT((ROW()-1)/3)*3+3)))

Open in new window

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