conditional format

[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

 

[Ragdyer]

See if this works for you:

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

Adjust your ranges.

 

[Paul Hyett]

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?

I just with there was a way to paste *only* conditional formatting in
Excel 2003.

 

[Gary]

RD,

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

Regards,
Gary

 

[Bob Phillips]

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

 

[Ragdyer]

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)

 

[Ragdyer]

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>

 

[Gary]

RD, It is working exactly like I want. Thank you for taking time to assist.
Regards, Gary

 

[Ragdyer]

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.

 

[Paul Hyett]

I just with there was a way to paste *only* conditional formatting in
Excel 2003.

I don't suppose that *can* be done in Excel 2007?