Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.



share|improve this question
up vote 0 down vote accepted

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.

share|improve this answer

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:


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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .