ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Sequential Rank Explaination Please (https://www.excelbanter.com/excel-discussion-misc-queries/445818-sequential-rank-explaination-please.html)

atlasstone

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


All times are GMT +1. The time now is 07:37 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com