SOLVED

Conditional Format Help. Number +/- 10%

Copper Contributor

Hi,

 

I'm a little stuck creating a conditional formatting rule. I need to highlight cells that are <> 10% of a value in another cell.

 

Thank you in advanced

4 Replies

@dmoggie 

Let's say the cells you want to highlight are in D2:D100, and the cell to compare to is the cell in the same row in E2:E100.

Select D2:D100. D2 should be the active cell in the selection.

On the Home tab of the ribbon, select Conditional Formatting >New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

 

=ABS($D2-$E2)<0.1*ABS($E2)

 

Click Format...

Specify the desired formatting.

OK your way out.

 

If you want to compare to E2 for all cells in D2:D100, change $E2 to $E$2 in the formula.

And if you wanted the opposite, change < to >.

@Hans Vogelaar Many thanks for taking the time to reply.  I understand your response and have adapted for my scenario. :) 

 

In the table below I am now trying to compare the cells in each columns with that columns Median. Highlighting any cell that is above/below the median figure by 10%. Conditional formatting keeps reverting back to absolute values and therefore I am unable to drag formula. 

 

/6305A440.641.542.144.743.837.138.6
/6307A440.541.341.944.743.836.938.5
/6310A440.140.941.544.343.33738.6
/6313A441.241.942.545.143.937.438.9
/6317A459.16160.764.562.149.551.7
/6321A440.641.241.844.343.737.138.6
Median 40.641.44244.743.837.138.6

 

 

best response confirmed by dmoggie (Copper Contributor)
Solution

@dmoggie 

See the attached sample workbook.

Thank you so much I just could not figure it out.
1 best response

Accepted Solutions
best response confirmed by dmoggie (Copper Contributor)
Solution

@dmoggie 

See the attached sample workbook.

View solution in original post