conditional format

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

Excel 2007

By conditional formatting I want to highlight the top 25% of numbers in a
column, but I want to ignore all "0". What formula might work?

Regards, Gary
 
See if this works for you:

=RANK(A1,A$1:A$20)<=COUNTIF(A$1:A$20,">0")/4

Adjust your ranges.
 
Excel 2007

By conditional formatting I want to highlight the top 25% of numbers in
a column, but I want to ignore all "0". What formula might work?
Click to expand...
I just with there was a way to paste *only* conditional formatting in
Excel 2003.
 
RD,

The formula works great. How would I do the same thing, but highlight the
bottom 25%?

Regards,
Gary
 
There must be an easier way, but right now I can't get my head around it:

=AND(RANK(A1,A$1:A$20)<COUNTIF(A$1:A$20,">0")+1,RANK(A1,A$1:A$20)>=COUNTIF(A
$1:A$20,">0")-(COUNTIF(A$1:A$20,">0")/4)+1)
 
This "TRUE's" any and all the zero cells Bob.

I'm sure there must be something better then my suggestion, but I can't see
it right now.<g>
 
Thanks for the feed-back *BUT* -

*** Some caveats ***:

Just noticed that the above formula:

=AND(RANK(A1,A$1:A$20)<COUNTIF(A$1:A$20,">0")+1,RANK(A1,A$1:A$20)>=COUNTIF(A
$1:A$20,">0")-(COUNTIF(A$1:A$20,">0")/4)+1)

will *fail* to return the full 25% compliment of values if any of those
bottom values are duplicated to the extent that they exceed the bin size of
25%.

i.e.; no zeroes present - 25% = return of 5 bottom values.

1, 2, 3, 3, 3, returns 5 values,
1, 2, 3, 3, 3, 3, returns 2 values,
3, 3, 3, 3, 3, returns 5 values,
3, 3, 3, 3, 3, 3, returns *no* values!


If this presents a problem, you might keep checking back to see if anyone
will come up with something better.
 
Back
Top