Conditional Format top/mid/bottom 33% of cells, but ignoring blank cells

  • Thread starter Thread starter fish
  • Start date Start date
F

fish

Hello,
I am trying to conditionally format the top middle and bottom thirds
of a range of data. Problem is, that the range needs to be flexible as
sometimes there may be a maximum of 36 cells with data, but sometimes
there may be less (so there are blank cells in the range that need not
be counted). The methods I have tried always include the blank cells,
and so it is not equally formatting the thirds (as it includes the
blanks cells as part of the bottom data)....hope Im making sense! Here
are the 2 methods Ive tried so far:(using excel 2003)

Top 34%:
=IF(INT(COUNT($D$3:$D$38)*34%)>0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*34%)),MAX( $D$3:$D$38))<=D3
Middle 33%
=IF(INT(COUNT($D$3:$D$38)*67%)>0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*67%)),MAX( $D$3:$D$38))<=D3
Rest of the data (bottom 33%)
=IF(INT(COUNT($D$3:$D$38)*100%)>0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*100%)),MAX( $D$3:$D$38))<=D3

and

Top 34%:
=D3<=PERCENTILE($D$3:$D$38,0.34)
Middle 33%
=D3<=PERCENTILE($D$3:$D$38,0.67)
Bottom 33%:
=D3<=PERCENTILE($D$3:$D$38,1)
 
Back
Top