Needing some help if possible please.
I have a sheet of data of 50R x 60C containing data represented as percentages. On a separate sheet I want to generate a series of columns which shows the Top 'X' values for each column in the data range but instead of returning the value found for each rank, I want to return the value of the row heading. If possible I'd like to be able to change how many values I wish to return.
An example of the data
[4][ROW1][22%][12%][78%]...[1%]
[5][ROW2][8%][51%][9%]...[90%]
[6][ROW3][45%][83%][56%]...[27%]
.
.
.
[54][ROW50][1%][7%][14%]...[88%]
I guess basically what I am trying to do is a reverse intersection. I tried using LARGE and OFFSET but wasn't really getting anywhere. I am using Excel 2007 too.
I tried looking at some detailed examples of using RANK from http://www.cpearson.com/excel/rank.aspx but I couldn't seem to get some of the suggested formulas working for me.
Any help would be appreciated.
Bookmarks