Excel 2007 : 3 color Scale - Ignore "0" values

I'm trying to scale in 3-colors.

My minimum values are in the thousands, but I also have ZERO & "blank" values.

I want to ignore those values in my scaling. Otherwise, all zeros turn RED (my minimum set color) and everything else appears yellow or green.

the sheet is massive and manually setting each minimum value would be too time consuming.

micahblouin,

It sounds like you're looking for Conditional Formatting. I see you have Excel 2007. Here's a couple links to get you started:

Demo from office.microsoft.com
Tutorial from www.homeandlearn.co.uk

Hope this helps,
~tigeravatar

Within Conditional formatting... I'm trying to dig deeper than that...

attached is a LifeCycle schedule I'm working on. Each row will have sales Revenues per product per month. I'd like to have excel format in the way that I've manually input.

If I use 3 Color Scale, it will include the ZERO values and bring down my averages, making the product line look far healthier than it actually is.


If this were just one product, I could manually set the parameters; however, i'm dealing with about 100 products and their revenues ranges from 3,000 to 3million, so, it's a big spread and the max-mid-min values wouldn't jive.

I also don't want to spend 20 hours on this thing. I need to MAKE it so I can USE it

micahblouin,

The example sheet you provided doesn't make sense to me. There doesn't seem to be any pattern at all as to how you've highlighted the cells. What is your criteria for highlighting?

~tigeravatar

loose criteria at this point... but let's define it...


> (AVERAGE(previous 3 months)) --------> Green


< 100% but > 75% (AVERAGE(previous 3 months)) --------> Yellow


< or = 75% * (AVERAGE(previous 3 months)) --------> Red


I want to be able to glance at a product and see it's lifecyle (whether or not its sales are increasing, plateauing (is that a word?), or decreasing.