Conditional Formatting to color-code three highest values?

Excel Help for Conditional Formatting To Color-code Three Highest Values? in Automating Data Analysis Tasks

Conditional Formatting To Color-code Three Highest Values?

Rate this: (3/5 from 1 vote)

	I am not the most efficient user of Excel, but I have taken a couple of classes in college (several years ago). I run a non-profit agency that has a motorcycle poker run. What I am trying to do is create a spreadsheet that I can use as a scorecard to score 250 bikers' cards. I have one column (G) with the sums of each person's five cards. In that column, I would like for Excel to recognize the three highest values (1st, 2nd & 3rd place) and change that particular block to another color. I have been able to get it to change the top two values; however, all of the remaining blocks are highlighted for the third one instead of just the one third-highest value. How can I edit this formula to do what I want? What I've done so far is use the conditional formatting and I created a new rule for my range (G2:G251). I formated all cells based on their value and used the 3-color scale option, and input the following values: =LARGE($G$2:$G$251,3) color-coded yellow, =LARGE($G$2:$G$251,2) color-coded red, and Highest Value color coded green. What happened is that the highest value does indeed show up green and the second-highest number shows up red, the way they are supposed to. My dilemma is coming in that ALL of the other blocks in the range show up yellow, not just the block with the third largest value, like I want it to. My poker run is on Saturday and I'm already stressed to the max. This is probably a simple fix, but I'm tired and frustrated and can't figure it out. Any help would be GREATLY appreciated!!!
	heatherchellette
	Posted by heatherchellette on 30 Jul 2010

Replies - Displaying 1 to 1 of 1

Order Replies By: Most Recent | Chronological | Highest Rated

	Multiple conditional formats Rate this: (3/5 from 1 vote) The easiest way is to use multiple condition formats instead of the 3 color scale option. In separate cells create the LARGE formulas to return the 1st, 2nd and 3rd rank values - e.g. =LARGE($G$2:$G$251,1) for 1st etc. On the range of results, create 3 different conditional formatting rules whereby the value equals each of the rankings to color the cells.  This will result in the third ranking only coloring the cells for that value and not the rest. This approach will also enable to provide an area with the summary of results (1st, 2nd and 3rd) optionally with a VLOOKUP with names against them, and can also be extended for 4th, 5th position rankings and so on.
	Excel Business Forums Administrator
	Posted by Excel Helper on 30 Jul 2010

Displaying page 1 of 1