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 farusing 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)
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 farusing 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)