The RANK function has some pretty obvious uses, such as ranking things (imagine that). But it also has another, better use: Its ability to auto-sort. By that I mean to keep your data arranged without you (or the user) having to physically sort it. This can be especially useful when making spreadsheets that are going to be locked for editing by the user. First though, a brief explanation on how the RANK function works.

The first parameter is which number to look at. The second parameter is which numbers to compare it to. The third parameter is whether the numbers should be ranked in descending or ascending order. Imagine that you have the numbers 8,6,19,2 in that order going down column A in the first 4 rows and are using the function:

= RANK (A1, A $ 1: A $ 4.0)

When you drag this down so that it references all 4 numbers, the results will be 2,3,1,4. This is because the 19 is the first number in descending order, followed by 8, then 6 then 2. If you were to change the last parameter to a 1, you would instead get 3.2,4,1 since it would be in ascending order.

In order to use this to auto-sort, imagine you have the raw data numbers in column B of Sheet 1 and you would like the numbers to be auto sorted in descending order in column D of Sheet 2. To do this, in column A of Sheet 1 use the function:

= RANK (B1, B: B, 0)

Drag this down through all of the rows that have data in column B. This will give you the rank of each value. Then, in Sheet 2, starting in cell D2, use the function:

= IFERROR (VLOOKUP (ROW (D1), Sheet1! A: B, 2, FALSE), D1)

This will look up the number 1 (representing the highest number) in column A of Sheet 1 and give as a result the corresponding number in column. In other words, cell D2 will look up 1, cell D3 will look up 2, cell D4 will look up 3, etc. In the event that there are repeat values, the IFERROR function will come in to play and use the value from the row above. (If you are using Microsoft Excel 2003 you will have to use a combination of the IF and ISERROR functions, as the IFERROR function is not available.) This way, no matter how many times you change the raw data in Sheet 1, it is always perfectly normally numbered in Sheet 2.

If your data is not solely numeric, this becomes far more complicated. In that case, imagine that you have letters and numbers, but no special characters and no more than 4 total characters in each cell. (That last assumption is simply for brevity's sake.) The function becomes longer each time you add a character, and 4 characters should be more than enough to get the idea across.) In this case you would need to put a function in column C of Sheet 1 that looks like this:

= 36 ^ 3 * IFERROR (IF (ISERROR (VALUE (MID (B1,1,1)), IF (CODE (MID (B1,1,1)) <97, CODE (MID (B1,1,1) ) -54, CODE (MID (B1,1,1) - 86), VALUE (MID (B1,1,1)) + 1), 0) + 36 ^ 2 * IFERROR (IF) ISERROR (VALUE) MID (B1,2,1) - 54, CODE (MID) (B1,2,1)), CODE (MID) ) -86), VALUE (MID (B1,2,1)) + 1), 0) + 36 ^ 1 * IFERROR (IF) ISERROR (VALUE (MID (B1,3,1))), IF (CODE) MID (B1,3,1)) <97, CODE (MID (B1,3,1) - 54, CODE (MID (B1,3,1) - 86), VALUE (MID) B1,3,1 ) (+ 1), 0) + 36 ^ 0 * IFERROR (IF (ISERROR (VALUE (MID (B1,4,1)), IF (CODE (MID (B1,4,1)) <97, CODE MID (B1,4,1) - 54, CODE (MID (B1,4,1) - 86), VALUE (MID (B1,4,1) + 1), 0 -WW (C1) 1048577

You will also need to change your function in column A of Sheet 1 to:

= RANK (C1, C: C, 1)

I will not explain all of this here, as it would drastically increase the length of this article, but if you look closely you should be able to find the pattern in the event that you needed to increase it beyond 4 characters. I will note, however, that on my computer, capital letters start at code 65 and lowercase letters start at code 97. If this is different on your computer, your function will have to be changed accordingly. To check your character codes, make a blank spreadsheet and drag the following function from cell A1 down through A255:

= CHAR (ROW (A1))

In order to sort special characters (spaces, dollar signs, slashes, etc.) you will have to expand the function even farther, but I will not go into that here.

In conclusion, the rank function can be easily used to auto-sort numeric data, and somewhat less easily to auto-sort other types of data.

Dejar respuesta

Please enter your comment!
Please enter your name here