Conditional Formatting within percentage range

[wrkforce1]

I'm trying to highlight cells within 5% of goal green and those that are outside the range to be highlighted red

I tried to use conditional formatting formula and wrote it as the following, but it's not working out.
=(ABS(A2-$A$1)/$A$1)<=0.05

Where is my error? Also, where should my anchors be for the first cell I select? The goal value is a fixed value/cell so I anchored that one, not sure how to place the anchors if I want the formula rule to apply to all the cells in the range I select.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]405[/TD]
[TD]<-Goal[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]468[/TD]
[TD]414[/TD]
[TD]534[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]474[/TD]
[TD]420[/TD]
[TD]564[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]456[/TD]
[TD]426[/TD]
[TD]546[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]468[/TD]
[TD]432[/TD]
[TD]600[/TD]
[/TR]
</tbody>[/TABLE]

 

[ClaireS]

Applying this conditional formatting rule to cells A2:C5, I am successfully seeing a green fill in cells B2:B3. Adding a second rule of

=(ABS(A2-$A$1)/$A$1>0.05 gives me a red fill in the other cells. In what way is your conditional formatting "not working out"?

Your formula is fine. As you are referring to one fixed cell, your Target value, you are correct in fixing the entire cell reference to $A$1. When thinking about "anchoring" cells in other CF formulae, though, follow these steps:
​

1. 
   [*=left]Select the entire range you want to format
   [*=left]Create the formula as though for the first cell in the range - the active cell
   [*=left]Apply $ signs in a way that would allow this formula to be copied to the other cells in the range successfully

For example, if you are formatting cells A2:Z99, based on a certain result in the Status in column P, your formula would need to refer to $P2, i.e."column P (fixed) of this row (relative)"

Hope this helps - but I really can't see any problem with your original formula. You did remember to set formats? Sometimes I get so bogged down in creating a good formula that I forget to set the actual format! <strike></strike>
​

 

[wrkforce1]

I had an anchor in the first cell I selected so it was (ABS($A2-$A$1..... removing the anchor worked, I guess I should've tried the formula posted on the forum :P

If I wanted to add a scale ie. all values that are within 5- 10% are Yellow, how would I formulate that?

=((ABS(C80-$G$75)/$G$75)>0.05)*(ABS(C80-$G$75)/$G$75)<=0.1))" This isn't working out for me

c80 = A2
$G$75 = A1

 

[wrkforce1]

Nevermind, I hate myself. I had an extra parenthesis