SOLVED

New Contributor

# 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.

4 Replies

# Re: Conditional Format Help. Number +/- 10%

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.

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 >.

# Re: Conditional Format Help. Number +/- 10%

@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

best response confirmed by dmoggie (New Contributor)
Solution

# Re: Conditional Format Help. Number +/- 10%

See the attached sample workbook.

# Re: Conditional Format Help. Number +/- 10%

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