Identifying top 33%, middle 33%, and bottom 33%

Hello!

Can anyone help me figure out a formula that will give a column a "1" if the ranked column next to it is the top 33% of cells, a "2" if the rank is the middle 33%, and a "3" if the rank is the bottom 33%. The number inside the cell is not important anymore, I just need to know the rank (for example there may be 9 rows, I want to identify the first 3 cells as a "1", middle 3 as a "2", and bottom as a "3".

I can not use conditional formatting to use the icon because I need this number to come back to a control tab through an index match. (unless somebody knows how to bring the icons to the control tab)

I am trying to see what are the top, middle, and bottom iteams within a spreadsheet. To rank the items I created a power rank, but am now stuck on how to break up the items into 33%s.The document also has 100 tabs that I will need to apply this formula too.

Thanks!

Hi,

Suppose your rankings are in column B (from rows 1 to 9), then in cell C1 enter the formula

Formula:

Please Login or Register  to view this content.

and drag it down column C as far as is necessary.

Hope this helps