Typically the RANK function has some

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

The first parameter is which quantity to look at. The second parameter is which in turn numbers to compare it to. The 3rd parameter is whether the numbers need to be ranked in descending or climbing order. Imagine that you have the numbers 8, 6, 19, 2 in this particular order going down column A in the first 4 rows and are while using function:

=RANK(A1, A$1: A$4, 0)

When you drag this down in order that it references all 4 numbers, the actual outcome will be 2, 3, 1, 4. This is because the 19 is the first of all number in descending order, and then 8, then 6 then second. If you were to change the last parameter to some 1, you would instead get 5, 2, 4, 1 since it will be in ascending order.

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

=RANK(B1, B: B, 0)

Drag this particular down through r&amp;eacute;f&amp;eacute;rencer un site all of the rows that contain data in column B. This will likely give you the rank of each value. Subsequently, 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 just one (representing the highest number) in line A of Sheet 1 and give as a result the corresponding number in steering column B. When you drag this straight down, each row will look up the following number. In other words, cell D2 look up 1, cell D3 will appear up 2, cell D4 can look up 3, etc. In the event that you can find repeat values, the IFERROR purpose will come in to play and use the value from the row above. (If you are using Microsoft Excel 2003 you will need to use a combination of the IF and ISERROR functions, as the IFERROR function is not available. ) This way, regardless of many times you change the raw data in Sheet 1, it is always perfectly numerically sorted in Sheet second.

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

=36^3*IFERROR(IF(ISERROR(VALUE(MID(B1, 1, 1))), IF(CODE(MID(B1, 1, 1))

You will also need to change your function inside column A of Sheet one to:

=RANK(C1, C: C, 1)

I won't explain all of this here, as it would drastically increase the length of this short article, but if you look closely you should be capable of finding the pattern in the event that you necessary to increase it beyond 4 personas. 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 appropriately. To check your character codes, generate a blank spreadsheet and drag this particular function from cell A1 along through A255:

=CHAR(ROW(A1))

In order to kind special characters (spaces, dollar signs, reductions, etc . ) you will have to expand typically the function even farther, but I won't go into that here.

In conclusion, typically the rank function can be easily accustomed to auto-sort numerical data, and relatively less easily to auto-sort various other data.