Best Tool for Microsoft Excel RANK function of

RANK function has several uses are fairly obvious, such as attraction ratings (imagine that). But it also has, another better use: ability to auto-sort. I mean to keep your data organized without you (or users) have some sort of physical. This can be useful especially when making this spreadsheet will be locked for editing by the user. First though, a brief description of how the function of RANK works.

The first parameter numbers to see. The second parameter numbers to compare it with. The third parameter is the number should be ranked in descending order or sequence. Imagine that you have a number in order 8,6,19,2 down column A in 4 rows and use the function: = RANK (A1, A $ 1: A $ 4.0) When you drag it down so that references all 4 digits, results will be 2,3,1,4. This is because 19 is the first number in the sequence, followed by 8, then 6, then 2. If you change the last parameter to 1, you would rather get 3,2,4,1 since order.

In order to use the auto-sort, imagine that you have the raw data numbers in column B of Sheet 1 and you want numbers will be automatically sorted in descending order in column D Sheet 2. To do this, in column A Sheet 1 using the function = RANK (B1, B: B, 0) Drag down through all the rows of data in column B. This will give you the rank of each value. Then, in Sheet 2, started in cell D2, using the function: = IFERROR (VLOOKUP (ROW (D1), Sheet1 A: B, 2, FALSE), D1) It will look for the number 1 (which is the maximum amount) in column A of Sheet 1 and gives a result corresponding number in column B. When you drag it down, every line up to the next number. In other words, the cell D2 will seek 1, cell D3 would look to 2, cell D4 will look into 3, etc. In the event that there are recurring costs, IFERROR functions will come to play and use the value from the row above. (If you are using Microsoft Excel 2003 will have to use a combination of IF and ISERROR functions, such as the IFERROR function is not available.) In this way, no matter how many times you change the raw data in Sheet 1, it’s always perfect numerical sorted Sheets 2.

If you do not only numerical data, it becomes more complicated. In this case, imagine that you have letters and numbers, but no special characters and no more than 4 characters total per cell. (The last assumption is only for the brevity. Functions that gets longer each time you add a character, and 4 characters should be more than enough to get the idea across.) In this case you will need to put the function in Sheet 1 column C that looks like too will transform your function in a variety of sheet 1: = RANK (C1, C: C, 1) I will explain them all here, because they greatly increase the length of this article, but if you look closely you should be able to find patterns in the event you need to increase it beyond the 4 characters. I will note, however, that in my computer, starting with a capital letter lowercase code 65 and code starting with 97. If different on your computer, your function must change accordingly. To examine the character of your code, create a blank spreadsheet and drag the following functions of cells A1 to A255 = CHAR (ROW (A1)) To classify special characters (spaces, dollar signs, slashes, etc.) must extend the functionality even further, but I’m not going to go here. In conclusion, ranking functions are easy to use to auto sort the data numerically, and somewhat easier to auto-classify other data types.