Forum Discussion
Conditional Format Help. Number +/- 10%
See the attached sample workbook.
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.
- Thank you so much I just could not figure it out.