Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

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:

=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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.