Color scale formatting in Excel 2007 based on "good" center value/absolute value

Question

Imagine I have a cell that I want to be red if the value is 2000, yellow if it is +-200 and red if it is +-500. Excel doesn't seem to want to let me have a good center value, with any value that is too high or too low being bad values.

The closest I can get is a 3 color gradient where it is red/green/red, with no yellow intermediate coloring. I attempted to use a formula based on absolute value, where I set the minimum color to green with a formula of abs(a1-2000)=0, yellow midpoint of abs(a1-2000)=200 and red maximum of abs(a1-2000)=500 but Excel won't allow me to use relative references in formulas for color scale formatting.

Anyone have any ideas?

Edit: I should clarify that I'm using Excel 2007

Answer 1

create 3 new rules of the Format only cells that contain type, all stop if true:

1. for exactly 2000 red
2. for between 1800 and 2200 yellow
3. for between 1500 and 2500 red.

Answer 2

Use as many hidden cells at 2000 as required to skew the average towards 2000. You can select all those extra cells when creating the rule, then hide them.