Forum Discussion
Conditional Format Help. Number +/- 10%
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
See the attached sample workbook.
4 Replies
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 >.
HansVogelaar 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.
/6305A 4 40.6 41.5 42.1 44.7 43.8 37.1 38.6 /6307A 4 40.5 41.3 41.9 44.7 43.8 36.9 38.5 /6310A 4 40.1 40.9 41.5 44.3 43.3 37 38.6 /6313A 4 41.2 41.9 42.5 45.1 43.9 37.4 38.9 /6317A 4 59.1 61 60.7 64.5 62.1 49.5 51.7 /6321A 4 40.6 41.2 41.8 44.3 43.7 37.1 38.6 Median 40.6 41.4 42 44.7 43.8 37.1 38.6 See the attached sample workbook.
