Conditional Format of top 80%

[cycle_simon]

I could really use some help on a "custom" conditional format that would highlight the cell values in a column that would cumulatively total to 80% of the column total.

For example in the following list of numbers, with blank cells in the column, highlight each of the largest numbers that cumulatively respresent 80% (rounded, since its unlikely to hit exactly 80%) of the column total.

391194

339817
23733

20685

10443
2822

443
1674




200

850


0
31

908

 

[Matty]

Are you counting from the bottom up?

If so, try:

=AND(A1<>"",SUM(A1:A$25)<=SUM(A$1:A$25)*0.8)

This will highlight all values except the top value in the example provided.

Matty

 

[cycle_simon]

Yes, that is what happened when I entered the formula as a conditional format (all but 1st value was highlighted).

However, what I was hoping/trying to achieve was a formula that looked at the 1st, 2nd, 3rd largest numbers in the column until it got to at least 80%. (The target percentage could be a cell reference so the target value can easily be changed.)

In the example data provided, the 1st and 3rd entries total 92.2%, so only these 2 entries would get highlighted by the conditional format.

I hope that is clearer than the original post.

Thanks in advance.

 

[Matty]

There will be a slicker way of doing this without helper columns, but it seems to work:

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:20px;"><col style="width:83px;"><col style="width:81px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>Data</td><td>
</td><td>Desc. Sorted</td><td>Criteria Met?</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="background-color:#fac090; text-align:right; ">391194</td><td>
</td><td style="text-align:right; ">391194</td><td style="text-align:center; ">Yes</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>
</td><td>
</td><td style="text-align:right; ">339817</td><td style="text-align:center; ">Yes</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="background-color:#fac090; text-align:right; ">339817</td><td>
</td><td style="text-align:right; ">23733</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:right; ">23733</td><td>
</td><td style="text-align:right; ">20685</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>
</td><td>
</td><td style="text-align:right; ">10443</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="text-align:right; ">20685</td><td>
</td><td style="text-align:right; ">2822</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td>
</td><td>
</td><td style="text-align:right; ">1674</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style="text-align:right; ">10443</td><td>
</td><td style="text-align:right; ">908</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td style="text-align:right; ">2822</td><td>
</td><td style="text-align:right; ">850</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td>
</td><td>
</td><td style="text-align:right; ">443</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td style="text-align:right; ">443</td><td>
</td><td style="text-align:right; ">200</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td style="text-align:right; ">1674</td><td>
</td><td style="text-align:right; ">31</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td>
</td><td>
</td><td style="text-align:right; ">0</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">15</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">16</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">17</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">18</td><td style="text-align:right; ">200</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">19</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">20</td><td style="text-align:right; ">850</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">21</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">22</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">23</td><td style="text-align:right; ">0</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">24</td><td style="text-align:right; ">31</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">25</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">26</td><td style="text-align:right; ">908</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>
Formulas as follows...

C2, copied down:

=IFERROR(LARGE(A$2:A$26,ROWS(C$2:C2)),"")

D2, copied down:

=IF(AND(ROWS(D$2:D2)=1,SUM(C$2:C2)/SUM(A$2:A$26)<0.8),"Yes",IF(AND(SUM(C$1:C1)/SUM(A$2:A$26)<0.8,SUM(C$2:C2)/SUM(A$2:A$26)>=0.8),"Yes",""))

Conditonal Formatting formula applied to range A2:A26:

=ISNUMBER(MATCH(1,IF(C$2:C$26=A2,IF(D$2:D$26="Yes",1)),0))

Does this help?

Matty

 

[cycle_simon]

Thanks Matty, your suggestions were very helpful. I was able to use the ideas and formulas to reduce the steps. Still testing and may have to use VBA in the end.

 

[Matty]

Thanks Matty, your suggestions were very helpful. I was able to use the ideas and formulas to reduce the steps. Still testing and may have to use VBA in the end.

Glad it helped. As I said, I'm sure there's a better way to do this (probably with SUBTOTAL and LARGE), but I can't come up with something that works.

Matty