CODEWITHSUNDEEP
excel
ARW
ARW

Reputation: 3416

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

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

Upvotes: 2

Views: 3089

Answers (2)

Amaury V
Amaury V

Reputation: 11

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.

Upvotes: 1

Aprillion
Aprillion

Reputation: 22330

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.

enter image description here enter image description here

Upvotes: 2

Related Questions