# 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

Book1.xlsx
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
can you enforce a sorting of the three items in each set?
0
Author Commented:
Not for this situation.
0
Commented:
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))
``````
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
Commented:
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)))
``````
0
Commented:
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

Experts Exchange Solution brought to you by