Conditional Formatting if difference in cells is greater than 1%

[Fazila]

Hi All

Wondering if anyone can help me here. I need to highlight cells where the number is lower than say cell A2 but only if there difference is more than 1%. Any ideas? For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]53[/TD]
[TD]52.6[/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD]69[/TD]
[/TR]
</tbody>[/TABLE]

Only the cell in purple would be highlighted. 52.6 would not as it is only 0.4% less than 53.

Thanks

Fazila

 

[KennyGreens]

Go into manage rules and add a new rule, use the last option "use a formula to determine which cells to format". In the area to enter the formula to format based off, try entering this (this formula assumes that the row that contains 53 and 52.6 in your example start in row 3 and you want the value in cell B3 - the 52.6 - to be lower than a predetermined value in cell A2):

=IF(AND(B3<$A$2, (A3-B3)/B3>.01), 1, 0)=1

 

[Fluff]

Maybe
=B2< A2*0.99

 

[Fazila]

Thanks Fluff that worked brilliantly

 

[Fazila]

KennyGreens think I miscommunicated I wanted to highlight cell B3 if it was less than A3 by greater than 1% but thank you for taking the time into looking at this.

 

[Fluff]

Glad we could help & thanks for the feedback