Dec 06 2021 02:16 PM
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
Dec 06 2021 02:28 PM
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 >.
Dec 08 2021 12:15 AM
@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.
/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 |
Dec 08 2021 04:37 AM
SolutionSee the attached sample workbook.
Dec 12 2021 11:40 PM
Dec 08 2021 04:37 AM
Solution