Top cumulative %

**sttrader** · 12-17-2011, 08:44 PM

First of all, Thank you for you help...

I am finding it hard to solve this...

Results of an experiment:

Result A - Occurred 5 times
Result B - Occurred 3 times
Result C - Occurred 8 times
Result D - Occurred 7 times
Result E - Occurred 6 times
Result F - Occurred 6 times
Result G - Occurred 5 times

I need to calculate the top 70% (approximate) of the results based on how frequently they occurred.

So in this example...
Result C - Occurred 8 times
Result D - Occurred 7 times
Result E - Occurred 6 times
Result F - Occurred 6 times

Total: 27 (That is 67.5% ~ 70%)
I dont want to include Result A or Result G then it would come to 92.5%

Could anyone please help me?

Thank you

**00Able** · 12-17-2011, 09:47 PM

See attachment for an example.

First sort columns A &B by Largest to Smallest

So how I like to do it is say D1=B1, and then adding B2 & D1, and then pasting that formula down
Then in cell C1 divied D1/D7, make sure to "lock" D7, so it should look like D1/$D$7, then drag that down.

Now you can see the top 70%

Hope this helps.

**sttrader** · 12-17-2011, 11:20 PM

Thank you for taking time to reply and for the worksheet...
However I cant sort the column, since everyday the results vary...

What I really like is probably a conditional formatting formula that will highlight the top 70% of the results everyday

Thank you again

**dangelor** · 12-17-2011, 11:45 PM

You could use Data > Filter > Top 10 > by % =70.

You can do the same with Conditional Formatting.

HTH,
Rich

**sttrader** · 12-18-2011, 01:08 AM

I am adding new date everyday by adding new column, so the filter option wont work,
Could you please tell me exactly what the function to use for Conditional formatting

**dangelor** · 12-18-2011, 01:41 AM

Use Home Tab > Conditional Formatting > Top/Bottom Rules > Top 10%... and change the percentage to 70 and set the format.

**sttrader** · 12-18-2011, 01:18 PM

Thanks for the reply, however it is not the same...

For example...

Lets say the numbers are
4
7
3
5
6
5
The total is 30.
I want the 50% of the total to be highlighted, but in that 50% I want to included the largest numbers first...
so to make up 15 (50% of 30), First # to be included should be 7, then 6, then 5 (it is over 15 but it is ok)

However the method you suggested is same as PERCENTILE function... it is highlighting the numbers that are over 70 percentile (7,5,6,5)

Is there any other way you can think of???

**dangelor** · 12-18-2011, 03:07 PM

Possibly by modifying 00Able's solution...

With this formula, you can sort without sorting (so to speak).

=LARGE($B$3:$B$8,ROWS($B$3:B3)) and copy down will rearrange a number list top to bottom.

This assumes your list of numbers is in B3:B8.