Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Ranking cells largest to smallest
I've got a large amount of data that I've sorted with a pivot table. I need
to order the data within each column of the pivot table (outside of the pivot table). Example: Frequency A 6 B 7 C 6 D 5 E 4 Obviously, the largest number in the frequency column is 7 and obviously, the next largest number is 6. What I'm trying to do is to get Excel to list the results by row heading (i.e., B is the largest, A is the next largest, C is the next largest, etc.) even when there is a duplicate number. I've been using the =large() function to no avail (it gives me a value of 6 for both the second and third largest values). How do I get Excel to differentiate between the two distinct values of "6"? Thanks in advance, Jim314 |
#2
|
|||
|
|||
Assuming your data is in A1:B5, here's an array formula, which, when entered
in C1 and copied down through C5, gives B A C D E: =INDEX(A$1:A$5,MATCH(LARGE($B$1:$B$5+1-ROW($B$1:$B$5)/100,ROWS($C$1:$C1)),$B$1:$B$5+1-ROW($B$1:$B$5)/100,0)) Be sure to enter it with CTRL+SHIFT+ENTER. On Mon, 10 Jan 2005 11:05:01 -0800, jim314 wrote: I've got a large amount of data that I've sorted with a pivot table. I need to order the data within each column of the pivot table (outside of the pivot table). Example: Frequency A 6 B 7 C 6 D 5 E 4 Obviously, the largest number in the frequency column is 7 and obviously, the next largest number is 6. What I'm trying to do is to get Excel to list the results by row heading (i.e., B is the largest, A is the next largest, C is the next largest, etc.) even when there is a duplicate number. I've been using the =large() function to no avail (it gives me a value of 6 for both the second and third largest values). How do I get Excel to differentiate between the two distinct values of "6"? Thanks in advance, Jim314 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To safety merge cells without data destroyed, and smart unmerge! | Excel Discussion (Misc queries) | |||
Heps to design Locked/Unlocked cells in protected worksheet | Excel Discussion (Misc queries) | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
Count number of shaded cells | Excel Discussion (Misc queries) | |||
Cells losing formatting | Excel Discussion (Misc queries) |