# Excel 2007: how to work out percentages of groups (top 10% of…)

I've recently read the following paragraph, and wondered:

• how you would organise the data (possibly Column A = country, Column B = salary, Column C = tax paid)
• but what formulas/calculations are used to work out these types of % figures:

In country Y the top 0.5% of taxpayers pay 17% of total income tax. In country X the top 0.1% of taxpayers pay 8% of total income tax and in country Z, the top 1% pay about 40% of total federal income tax.

I've gone through the help files and searched within Excel websites but I'm struggling to find an answer. %'s interest and trouble me...

Any pointers or examples very welcome.

Thanks

Mike

-

Isolate rows which refer to the same country, then order range A:C by column C from larger to smaller values.

Then consider the first k rows at the top, where k is equal to the number or rows divided by 200 (0.5%=100%/200), and sum the corresponding values in column C divided by the total sum of values in column C.

-

The easiest way would be to have the country data separated. Once you've done that you can identify whether each row satisfies your percentile requirement. For example if you are trying to isolate rows that have salary (Column B) in the top 5% use this formula in Column D:

``````=B2>=PERCENTILE(\$B\$2:\$B\$17,0.95)
``````

The .95 is one minus the percentile you are trying to find. Now you will have a column of true/false and you should be able to easily run a pivot table on it.

-