Conditionally formating

[khardu]

Hello Excel Lovers,





I have a range of values (more than a 1000). I want to format some of the values such that the sum of the formated values should be equal to a specified percentage of the sum of all the values. For example I have 10 values as:


13
21
34
99
43
56
78
31
101
342

Sumof these values or say TOTAL = 818

Now Suppose I want, out of these 10 values, the values whos sum is 60% of the sum of all the values (TOTAL) and these values should be the top most,ie., 818 x 60% = 490 (values should be 342, 101, and 99). Although the sum of these three values is 542 which is 66% of the TOTAL, but dropping 99 will reduce the percentage to 54%.

If I apply built-in function of " Top 10% ..". This highlights the top 60% of the values which are 342, 101, 99, 78, 56, 43 and the sum of these values is 719, which is 88% of the total sum 818. Let me know if this doesn't make sense.



Thanking you.





Regards,





KHARDU

 

[cyrilbrd]

Just being curious:
this is what I propose:
If in A1:
[TABLE="width: 260"]
<tbody>[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]342[/TD]
[TD="align: right"]41.81%[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]54.16%[/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]66.26%[/TD]
[/TR]
[TR]
[TD="align: right"]99[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]75.79%[/TD]
[/TR]
[TR]
[TD="align: right"]43[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]82.64%[/TD]
[/TR]
[TR]
[TD="align: right"]56[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]87.90%[/TD]
[/TR]
[TR]
[TD="align: right"]78[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]92.05%[/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]95.84%[/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]98.41%[/TD]
[/TR]
[TR]
[TD="align: right"]342[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
</tbody>[/TABLE]

In B1 type:
=LARGE($A$1:$A$10,ROWS($B$1:B1))
Copy down till B10.

In C1 type:
=SUM($B$1:B1)/SUM($A$1:$A$10)
copy down till C10.

Conditional formatting as follows:
Formula is =$C1<70%
range is $B$1:$B$10

this will highlight (in the color / format of your like) the following values: 342, 101 and 99.

Is this close to what you need?

 

[khardu]

Thank you Cyrilbrd for your reply.

Actually there's a lot of data linked to these values. Changing their order (sorting them) will create a great mess. I want to highlight them where they are. Any help will be appreciated.

 

[Skybluekid]

x