Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
2 part ranking question
Hello all,
I have a list that changes daily. The data is a 3 digit station code, e.g. LAX, GGV, JFX, ect. First, I need to rank the station by occurrence, e.g. if LAX comes up 15x and GGV comes up 5x then LAX would be ranked 1 and GGV 2, ect. Then, rank the station to the division it finds it's self, e.g. LAX is Western, GGV Western, JFX Eastern, ect. (there are only three division, Western, Southern and Eastern). Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
2 part ranking question
You can get ranks by occurrences with a set of formulas like the following.
You'll need the DistinctValues VBA function available at http://www.cpearson.com/Excel/DistinctValues.aspx. Suppose your data is in A1:A10. In B1, enter =DistinctValues($A$1:$A$10) and fill down to B10. In C1, enter =IF(B1="","",COUNTIF($A$1:$A$10,B1)) and fill down to C10. In D1, enter =IF(B1="","",OFFSET($B$1,IF(C1="","",COUNT($C$1:$C $10)-(RANK(C1,$C$1:$C$10)+COUNTIF($C$1:C1,C1)-1)+1)-1,0,1,1)) and fill down to D10. This will create in D1:D10 a list of the distinct items from A1:A10 ordered by frequency in ascending order (least common value to most common value). If you want the list in the reverse order, enter the following array formula in E1 and fill down to E10. =IF(D1="","",OFFSET($D$1,MAX((--($D$1:$D$10<""))*(--(ROW($D$1:$D$10))))-ROW(),0)) This is an array formula, so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the formula bar enclosed in curly braces { }. See http://www.cpearson.com/excel/ArrayFormulas.aspx for more information about array formulas. Ranking data is covered in much more depth at http://www.cpearson.com/Excel/rank.aspx. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "pgarcia" wrote in message ... Hello all, I have a list that changes daily. The data is a 3 digit station code, e.g. LAX, GGV, JFX, ect. First, I need to rank the station by occurrence, e.g. if LAX comes up 15x and GGV comes up 5x then LAX would be ranked 1 and GGV 2, ect. Then, rank the station to the division it finds it's self, e.g. LAX is Western, GGV Western, JFX Eastern, ect. (there are only three division, Western, Southern and Eastern). Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ranking Formula Question | Excel Discussion (Misc queries) | |||
Tricky Ranking Question | Excel Discussion (Misc queries) | |||
Ranking question (ties) | Excel Discussion (Misc queries) | |||
Ranking formula question? | Excel Discussion (Misc queries) | |||
Another ranking question | Excel Worksheet Functions |