How to use Conditional Formatting. Red, Yellow and Green Cells

[ccualumni]

I am struggling trying to find the proper way to set this up, yet it seems like it should be so easy.

Let's say I have a set of data. The data can be just about any number, but I want to put the low range in red, the middle range in yellow and the top range in green.

For the top 35%, they would be green
For the middle 30%, they would be yellow
For the bottom 35%, they would be red.

For example:
if my Data set is: 1, 25, 45, 51, 80, 100
1 (would turn this cell red)
25(would turn this cell red)

45(would turn this cell yellow)
51(would turn this cell yellow)

80(would turn this cell green)
100(would turn this cell green)

I see where I can put above average, below average, or to set a specific range.

But is there a way to select the above without having to set 3 rules? i tried looking at stuff here and on Google, but could not find what I was looking for.

How do I set it so that a range of numbers can be put into the colors I am looking for?

I hope I explained that well enough.

Thanks

 

[lenze]

Are you talking about a percentage of the whole. (Largest number)? Or, will you always be looking at a 1-100 data set/
lenze

 

[Jonmo1]

Look at the PERCENTRANK function...

Say those numbers you listed are in A1:A6
in B1 put

=PERCENTRANK($A$1:$A$6,A1) and fill down

You should be able to use that in conditional formatting like

Red would be
=PERCENTRANK($A$1:$A$6,A1)<=0.35
Yellow would be
=PERCENTRANK($A$1:$A$6,A1)<=0.64
Red would be
=PERCENTRANK($A$1:$A$6,A1)>=0.65

 

[mvptomlinson]

In Excel 2007 you can use Icon Sets within Conditional Formatting, which can put a red, yellow or green icon in the cell with the number based on it's percentile. But if you want the cell background or font to change, you'd have to use 3 formulas (conditions).

 

[ccualumni]

Thanks for the replies.

the numbers in the columns will always be different and measure stuff like amount of time, number of sales, number of calls and such.

I will rank each column and want just the cell to change color, not the numbers (I could not figure out how to do that on here with my example).

Thanks for the help.

 

[lenze]

Jonmo's formulas work perfectly. You don't even need the helper column "B"

lenze

 

[Jonmo1]

Jonmo's formulas work perfectly. You don't even need the helper column "B"

lenze

Yeah, I guess that was a little confusing.
I only suggested the formula in column B as an example of how PercentRank worked.
That was not meant as part of the conditional formatting solution..