Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sequential Rank Explaination Please
I found a post from T. Valko (Biff) written in 2008 in which a solution is posted which provides a sequential ranking, which allows duplicate ranks but does not skip numbers like the traditional use of the RANK function in excel. the solution posted was:
SUMPRODUCT(--(A2A$2:A$6),1/COUNTIF(A$2:A$6,A$2:A$6&""))+1 It doesn't look like T. Valko has been active for a while, so I thought a general post may be more productive. This solution was exactly what I needed, but I'd really like to understand what this is doing. I believe that the first array is returning a 0 or 1, but when I run it by itself, I get all False values so I'm confused as to what A2 is being compared to that might eventually return a True value. The second array is a complete mystery to me. Without the 1/ it gives me the number of times it finds a value in a range, but I don't understand how that differs if I were to set the static cell value (A2) as compared to looking for the range. I'm also curious about the use of &"" at the end. I apologize for being a pain, but really like to understand these things rather than taking a solution and leaving. If someone could break this down for me, I would certainly appreciate it and I might be able to stop attempting to draw graphical depiction of arrays in a vein attempt to understand this :) Here is a link to the post that I took this from. http://www.excelbanter.com/showthread.php?t=192353 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rank the numbers / range of data using 'RANK' and 'ABS' | Excel Worksheet Functions | |||
Interuption with no explaination :/ | Excel Programming | |||
Formula explaination | Excel Discussion (Misc queries) | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) | |||
Non-sequential VLOOKUP function -OR- sequential sort of web query | Excel Worksheet Functions |