# highlight top 80% values (sum) without sorting

#### lavkeshbhatia

##### New Member
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

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

##### New Member
Hi,

=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&#62;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 &#62;= etc to get the variants you want.

Myles

#### lavkeshbhatia

##### New Member
Hi Myles

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

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

##### New Member
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

##### New Member
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)&#60;0.8;I3;""), K4=IF(SUMA(\$K\$2:K3)/SUMA(\$I\$2:\$I\$11)&#60;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)&#60;0.8;LARGE(\$I\$2:\$I\$11;ROWS(\$I\$2:\$I3));""), L4=IF(SUMA(\$L\$2:L3)/SUMA(\$I\$2:\$I\$11)&#60;0.8;LARGE(\$I\$2:\$I\$11;ROWS(\$I\$2:\$I4));"") etc.