Conditional formating to choose High and low for background

[jonjon_01]

Hi guys.

Is it possible to use conditional formatting to choose one or two cells from a column to be colored for the top highest numbers and one or two cells for the lowest values to be colored a different color?

Example I9 & I10 would be colored Blue and I5 & I15 Red...

Clerk Productivity2.b.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3		Date:	NMT				MT				FFT
4			Idle	Tot Vol	LG Prod	Nix Prod	Idle	Tot Vol	LG Prod	Nix Prod	Idle	Tot Vol	LG Prod	Nix Prod
5	Wk 1	10/22/2005					0.66	2853	931.09		1	303	239.14	206.56
6		10/23/2005
7		10/24/2005					0.23	3730	1043.11		0.81	676	262.67
8		10/25/2005					0.29	3467	998.34		0.98	730	264.68
9		10/26/2005					0.33	4655	1047.9		0.59	588	274.6	252
10		10/27/2005					0.08	4054	1140.19		0.63	485	269.53
11		10/28/2005					0.24	2609	1044.88		0.68	471	248.17	100
2908

Thanks.

 

[texasalynn]

try this in the conditional format
formula is
=MIN(RANK($I5,$I$5:$I$11))>4
then format for the lowest
add another
formula is
=MAX(RANK($I5,$I$5:$I$11))<3
then format for highest

HTH

 

[jonjon_01]

tried format-not working

Hi TexasAlynn,

I tried the format, did the min, and the max. I get a red cell for the very last cell. Right now, I20-32 are blank.

The column goes to I32. I changed your forumula to =MAX(RANK($I5,$I$5:$I$32))<3 for the max with a blue background, and the same for the min with a red background...

I'm thinking this didn't work because of the blanks in some of the cells in the column.

Am I correct in this assumption? How can I fix this?