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

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top