asked on

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

aikimark

can you enforce a sorting of the three items in each set?

Dave

ASKER

Not for this situation.

aikimark

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

aikimark

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

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial