highlight top 80% values (sum) without sorting

[lavkeshbhatia]

how do i approach this ?


There are values in column A which are not sorted


I need to highlight the top values which will add up to 80% of sum of all values in column A


eg

15

25

18

5

7

8

12

10


adds up to 100


the output should highlight 25,18,15,12,10


A variation would be to highlight values at least till 80% (of the sum) is achieved

 

[Clarity]

Hi,


The PERCENTILE formula will help you acheive this.


=PERCENTILE(ARRAY,K)


Where array is your list of numbers and k is the percentage you want.


If you build this formula into a conditional format you will get the result you are looking for.


For example:

=c5>percentile($c$5:$c$9,0.2)


where c5 is a number in your list, the list is in the range c5:c9 and 0.2 is the 20% (100%-80%). You can play with >= etc to get the variants you want.


Myles

 

[lavkeshbhatia]

Hi Myles


Thanks for your reply


A percentile may not give the correct answer in case the number of entries increase


to clarify my question


consider

25

22

18

15

12

10

8

7

5

3

3

2

2

2

2

2

2

2

2

1

1

1

1

1

1


which has 25 entries adding up to 150


a percentile formula will highlight top 19 values which adds up to 144


required answer should highlight

25

22

18

15

12

10

8

7

5


the top values which add up at least to 80% of the universe i.e.80% of 150


these sum to 122 which is the closest value to 120


Its slightly more tricky than it seems


So guys ill need all the help I can get


Thanks again for your reply, hope a different approach shoul help solve it


Lavkesh

 

[keymaster]

Why not use a helper column and sort the values? Then you can use a simple SUM formula to compare if the sum has reached top 80%. It is a bit tricky to sort when the values have duplicates. But you can overcome that very easily by adding a small but unique fraction to each number (like running fractions - 0.0000001, 0.0000002...).

 

[bohtab]

Try this:

Cells I2 - I11 enter randome values

Col. K: not sorted 80%: Cells K2=I2, K3=IF(SUMA($K$2:K2)/SUMA($I$2:$I$11)<0.8;I3;""), K4=IF(SUMA($K$2:K3)/SUMA($I$2:$I$11)<0.8;I4;"") etc.

Col. L: sorted 80%: Cels: L2=LARGE($I$2:$I$11;ROWS($I$2:$I2)), L3=IF(SUMA($L$2:L2)/SUMA($I$2:$I$11)<0.8;LARGE($I$2:$I$11;ROWS($I$2:$I3));""), L4=IF(SUMA($L$2:L3)/SUMA($I$2:$I$11)<0.8;LARGE($I$2:$I$11;ROWS($I$2:$I4));"") etc.