Create categories based on quintiles?

Question

Level 1

0 points

Create categories based on quintiles?

I want to create categories in a table of statistics for countries. To compare different groups of countries I want to first divide the 177 countries in my table into 5 groups, quintiles, based on one variable and then compute the average value of another variable.

I could do this in a statistics programme but I want to do in a spreadsheet so my students can do it themselves. I have done it with pivot tables in Excel but I had to create the categories manually and insert them as separate columns which then could be used in pivot tables. It works rather well but if Numbers could divide the countries into quintiles it would be possible for the students to work with the data more independently. If you want to try using my Excel table it is available at www.goranlindgren.se/appendix.xls The variable names are in Swedish.

I am grateful for all suggestions!

iMac

Posted on Feb 3, 2010 3:11 AM

Reply

Answer 1

Badunit

Level 7

30,720 points

Feb 3, 2010 10:29 AM in response to goranlin

I'm not sure I totally understand your question but here are my thoughts that might get you started.

Using RANK you can rank order the countries by the values in a specified column. You can make your quintiles from those rankings. Of course, 177/5 is not a whole number so your quintiles won't be exact quintiles. If you have groups of 35,35,35,36,36 (=177 total),

Ranks 1-35 are quintile 1
Ranks 36-70 are quintile 2
Ranks 71-105 are quintile 3
Ranks 106-141 are quintile 4
Ranks 142-177 are quintile 5

RANK has one issue, though. If two numbers are equal, they are given the same rank. You may not have that problem with your numbers and it may not even matter if you do (maybe it isn't that important for your quintiles to be that exact) or maybe you want equal values to be ranked the same. But, if you care, you can get around that by adding a small amount to each value that is based on the row number. So instead of using RANK on the values in column B you would create another column that is =B+ROW()/1000 (or some other formula that you can be sure won't affect the results incorrectly) and rank that column instead.

You can sort by the rank or you can have formulas that turn the ranks into quintiles then sort by that column. A sample formula might be

=IF(Z>141,5,IF(Z>105,4,IF(Z>70,3,IF(Z>35,2,1)))) where Z is the column of ranks.

Or you could use one of the LOOKUP functions to create separate tables for each of the quintiles. That takes further explanation.

I hope some of this was helpful and I wasn't too off track.

Reply

Answer 2

goranlin Author

Level 1

0 points

Feb 3, 2010 11:51 AM in response to Badunit

Thank you very much for your attempt. Your suggestion is unfortunately not so simple as I hoped and is similar to what I did in Excel. I was hoping that the category tables could be used to make it easier. It is written in the manual that they can be used based on values but I have been unable to create groups for quintiles. I would prefer not to sort and then divide according to rank as I did in Excel.

Reply

Answer 3

Barry

Level 9

60,023 points

Feb 3, 2010 6:55 PM in response to goranlin

I don't think there's a need to sort (although I have given the following only a perfunctory test).

AVERAGEIF(test-values, condition, avg-values)

looks promising.

Carrying on from Badunit's proposed formula, placed in column AA, these five should give the averages within each detemined quintile for data in column C (not necessarily the column on which the ranking and consequent quintiles) are determined.

=AVERAGEIF(AA, "=1",C)
...
=AVERAGEIF(AA, "=5",C)

Regards,
Barry

Reply

Answer 4

goranlin Author

Level 1

0 points

Feb 10, 2010 1:45 PM in response to Barry

I am struggling... I used the function PERCENTILE to get the values of the percentiles and =PERCENTIL(C4:C180;0,2) gives the value 33 for the first quintile. Then I tried use that in a new column where I placed the category for each value (country). I tried with the formula:
=OM(C4<C$182;"Ginikvint1";OM(C4<C$183;"Ginikvint2";OM(C4<C$184;"Ginikvint3";OM(C 4<C$185;"Ginikvint4;OM(C4>C$185;"Ginikvint5")))))
I do not understand where I can chang the formulas. Some parts of them are colored and I cannot copy a part of the formula to another place in the same formula.
I use a Swedish program OM is IF. It did nor work properly. My file is available at http://www.goranlindgren.se/GiniQuintiles.numbers

I am getting tired and confused and would love to get some help! Please!

Reply

Answer 5

Feb 10, 2010 3:36 PM in response to goranlin

g,

I have used the function PERCENTRANK to assign a quintile rank to each row. If the second data item is in the same row, it can be easily averaged for each quintile by use of the AVERAGEIF function.

Here are the details...

Add one country to the bottom of your list, I called it DummyMax. The expression for DummyMax's value is:
=MAX(INDIRECT(ADDRESS(1, COLUMN())&":"&ADDRESS(ROW()-1, COLUMN())))+1

What this does is make your top value fall in the proper quintile under the method I use. Here's the method:

Add a column for the Quintile assignment. Let's assume your data is in Column B. The Quintile value expression is:

=INT(PERCENTRANK(B, B, 4)*5)+1 (Fill Down)

Your quintile values will be 1, 2, 3, 4, or 5. Your DummyMax row will have the value of 6, to be ignored.

Now you can AVERAGEIF with the computed quintile values as the condition of comparison.

Regards,

Jerry

Reply