Rank function in excel examples. Excel Statistical Functions

10 Jul

Good afternoon, dear subscribers and readers of the blog. Today we will talk about sorting data in Excel tables. But sorting is not simple, but with a certain condition - you cannot change the order of data in the table.

Indeed, a simple filter would solve our problems, but any ascending or descending sort will change the position of the data in the table according to their size. Let's consider such an example. There is a table:

If we turn on the filter from largest to smallest, then the order of the stores will change, as it is necessary to see the best and worst results. Select the table header, on the "Home" tab, click the "Sort and Filter" button, then select the "Filter" line. You will be able to select sorting criteria. According to the condition, select the sorting "From the largest to the smallest".

The order of the stores has changed, but, according to the condition of the problem, this should not be allowed. In this case, the RANK function is ideal. Starting with Excel 2010, new functions RANK.EQ and RANK.AVG were introduced. The RANK function has been left for compatibility with previous versions of the program, so a similar RANK.RW function will be used.

The main difference between the functions is RANK.RV takes into account equal values ​​and assigns them the same rank.

RANK.AVG - returns the average between two identical ranks (their ordinal numbers).

The formula syntax will be as follows:

number— numeric value for which the rank is calculated (mandatory argument);

order — the order of setting the rank (if it is 0 or the field remains empty, then the rank assignment will be in descending order, if it is 1, then the ranking will be in ascending order, optional argument).

Let's do the following:

  • add an additional column "Place in profit" to our table;
  • call the function wizard;
  • enter in the search field RANK.РВ;
  • click "OK";
  • fill in the function arguments.

In this formula, the range from B2 to B10 is specifically fixed so that the autocomplete marker can be used. Let's stretch it to the end of the table. The result will not be long in coming.

The RANK.EQ function returns the rank of a number in a list of numbers, that is, its magnitude relative to other values ​​in the list.

Description of the RANK.EQ function

Returns the rank of a number in a list of numbers, that is, its magnitude relative to other values ​​in the list. If multiple values ​​have the same rank, the highest rank of that set of values ​​is returned. If the list is sorted, then the rank of the number will be its position.

Syntax

=RANK.EQ(number, reference, [order])

Arguments

Required. The number for which the rank is determined.

Optional. A number that specifies how to sort.

Remarks

  • If the value of the order argument is 0 (zero) or omitted, Excel determines the rank of the number as if the reference were a descending sorted list.
  • If the value of the order argument is any number other than zero, Excel determines the rank of the number as if the reference were an ascending sorted list.
  • The RANK.EQ function assigns the same rank values ​​to duplicate numbers. However, the presence of repeated numbers affects the ranks of subsequent numbers.

    For example, if a list of integers sorted in ascending order contains the number 10 twice, which has a rank of 5, the number 11 will have a rank of 7 (none of the numbers will have a rank of 6).

  • In some cases, it may be necessary to use a relationship-based rank definition. Let's say that in the previous example, you want to get a rank of 5.5 for the number 10. To do this, add a correction factor (see below) to the value returned by the RANK.EQ function. This coefficient can be applied in cases where the rank is calculated both in descending order (the "order" argument is zero or omitted) and in ascending order (the value of the "order" argument is not equal to zero).

    Correction factor for related ranks

When working with data, there is often a need to find out what place this or that indicator occupies in the aggregate list in terms of value. In statistics, this is called ranking. Excel has tools that allow users to quickly and easily perform this procedure. Let's find out how to use them.

To perform ranking in Excel, special functions are provided. In older versions of the application, there was one operator designed to solve this problem − RANK. For compatibility reasons, it is left in a separate category of formulas and in modern versions of the program, but it is still desirable to work with newer analogues in them, if possible. These include statistical operators RANK.RV And RANK.SR. We will talk about the differences and the algorithm for working with them later.

Method 1: RANK.EQ function

Operator RANK.RV performs data processing and displays in the specified cell the ordinal number of the specified argument from the aggregate list. If several values ​​have the same level, then the operator outputs the highest value from the list of values. If, for example, two values ​​have the same value, then both of them will be assigned the second number, and the next largest value will have the fourth. By the way, the operator RANK in older versions of Excel, so these functions can be considered identical.

The syntax of this operator is written as follows:

Arguments "number" And "link" are mandatory and "order"- optional. As an argument "number" you need to enter a reference to the cell that contains the value whose serial number you want to find out. Argument "link" contains the address of the entire range that is being ranked. Argument "order" can have two meanings - «0» And "1". In the first case, the order is counted in descending order, and in the second case, in ascending order. If this argument is not specified, then it is automatically considered by the program to be zero.

This formula can be written manually, in the cell where you want the processing result to be displayed, but for many users it is more convenient to set the input through the window Function Wizards.


Method 2: RANK.AVG function

The second function that performs the ranking operation in Excel is RANK.SR. Unlike functions RANK And RANK.RV, if the values ​​of several elements match, this operator returns the average level. That is, if two values ​​are of equal magnitude and come after value number 1, then they will both be assigned the number 2.5.

Syntax RANK.SR very similar to the scheme of the previous operator. It looks like this:

The formula can be entered manually or through the Function Wizard. We will dwell on the last option in more detail.


As you can see, there are two functions in Excel to determine the ranking of a particular value in a data range: RANK.RV And RANK.SR. For older versions of the program, use the operator RANK, which is essentially complete analogue functions RANK.RV. The main difference between the formulas RANK.RV And RANK.SR is that the first of them indicates highest level when the values ​​match, and the second displays the average as a decimal fraction. This is the only difference between these operators, but it must be taken into account when choosing which function is best for the user to use.

To rank data in Excel, the statistical functions RANK, RANK.RV, RANK.AVG are used. They all return the number of a number in a ranked list of numeric values. Let's take a closer look at the syntax, examples.

RANK function example in Excel

The function is used when ranking in a list of numbers. That is, it allows you to find out the value of a number relative to other numerical values. If you sort the list in ascending order, the function will return the position of the number. For example, in the array of numbers (30;2;26), the number 2 will have rank 1; 26 -2; 30 -3 (as the largest value in the list).

Function syntax:

  1. Number. For which you want to determine the number in the ranking.
  2. Link. An array of numbers or a range of cells with numeric values. If you specify just numbers as an argument, the function will return an error. Non-numeric values ​​are not assigned a number.
  3. Order. A way of ordering numbers in a list. Options: the argument is "0" or omitted - the value 1 is assigned to the maximum number in the list (as if the list were sorted in descending order); the argument is any non-zero number - ranking number 1 is assigned to the smallest number in the list (as if the list were sorted in ascending order).

Let's define the ranking of numbers in the list without repetitions:

The argument that determines how the numbers are ordered is "0". Therefore, in this function, numbers were assigned to values ​​from largest to smallest. The maximum number 87 is assigned the number 1.

The third column shows the formula with ascending rank.

Let's determine the numbers of values ​​in the list where there are duplicate values.

Duplicate numbers are highlighted in yellow. They all have the same number. For example, the number 7 in the second column is assigned the number 9 (both in the second row and in the ninth); in the third column - 3. But none of the numbers in the second column will have 10, and in the third - 4.

So that the ranks do not repeat (sometimes this prevents the user from solving the task), the following formula is used:

You can set limits for the function to work. For example, you only need to rank values ​​from 0 to 30. To solve the problem, apply the function IF (=IF(A2

Values ​​that match the specified condition are highlighted in gray. For numbers greater than 30, an empty string is displayed.

RANK.EQ function example in Excel

Versions of Excel since 2010 have introduced the RANK.EQ function. This is an absolute analogue of the previous function. The syntax is the same. The letters “PB” in the name indicate that if the formula finds identical values, the function will return the highest ranking number (that is, the first element found in the list of equals).

As you can see from the example, given function treats duplicate numbers in a list exactly as it would in a regular formula. If it is necessary to avoid repetition of ranks, we use another formula (see above).

RANK.AVG function example in Excel

Returns the numbers of a numeric value in the list (sequential number relative to other values). That is, it performs the same task. Returns the average only if identical values ​​are found.

Here is the output of the function:

The formula in the descending column is: =RANK.AVG(A2,$A$2:$A$9,0). So, the function assigned the value 87 an average number of 1.5.

Let's say there are three duplicate values ​​in a list of numbers (highlighted in orange).

The function assigned each of them a rank of 5, which is the average of 4, 5, and 6.

Let's compare the work of two functions:

Recall that these two functions only work in Excel 2010 and above. In earlier versions, you can use an array formula for this purpose.

Download examples of the ranking function RANK in Excel.

Thus, all the examples described above allow you to automate the work of ranking data and ranking values ​​without sorting.

Do you need to assign a number to each number in Excel so that you can sort them by that number? You can come up with complex structures for text data, but for numeric data there is a special RANK function in Excel. Relates to the number static functions and is quite useful. In the article, we also talk about new functions from Excel 2010 RANK.CP() and RANK.РВ()

How to use. RANK function in Excel

The RANK function is quite easy to use. The set of arguments is also simple, namely:

Description of arguments:

  • Number-number for which you want to determine the rank/place in the array
  • Link- an array of values ​​in which the rank/place of a number is determined
  • Order is an optional argument.

If you put 0 or do not fill in this argument, then the rank is determined in descending order. Those. the highest number 2000 will be in 1st place.

If you put any other number, then the maximum value will be under the last place.

It shows in detail how to use the function in the first picture.

Note. If there are 2 identical numbers in the list - in our case 1400, then they will be assigned the same rank values ​​- 2, i.e. second place. In this case, the next place will no longer be 3, but 4 (1,250). Quite an important property, which did not suit everyone. So the developers went ahead and made additional functions RANK.CP() and RANK.RV() in Excel 2010.

RANK.CP

If there are 1400 3 or more numbers with the same value, they will all be assigned a rank =2 - this is for the normal RANK function. The RANK.AVG function will calculate the place as the average of 3 places, i.e. 3 places rank second, third and fourth

RANK will give all 3 numbers second place.

RANK.CH will place all three in fourth place.

RANK.RV

It's simple - this is the same RANK function only for new versions of Excel - 2010 and older.

File example

You can read about sorting and finding the largest value in this article.

(Visited 7 463 times, 16 visits today)

When working with data, there is often a need to find out what place this or that indicator occupies in the aggregate list in terms of value. In statistics, this is called ranking. Excel has tools that allow users to quickly and easily perform this procedure. Let's find out how to use them.

Ranking functions

To perform ranking in Excel, special functions are provided. In older versions of the application, there was one operator designed to solve this problem - RANK. For compatibility reasons, it is left in a separate category of formulas and in modern versions of the program, but it is still desirable to work with newer analogues in them, if possible. These include the statistical operators RANK.EQ and RANK.AVG. We will talk about the differences and the algorithm for working with them later.

Method 1: RANK.EQ function

The RANK.RV operator processes the data and outputs the ordinal number of the specified argument from the aggregate list to the specified cell. If several values ​​have the same level, then the operator outputs the highest value from the list of values. If, for example, two values ​​have the same value, then both of them will be assigned the second number, and the next largest value will have the fourth. By the way, the RANK operator in older versions of Excel does the same, so these functions can be considered identical.

The syntax of this operator is written as follows:

The "number" and "reference" arguments are required, while the "order" arguments are optional. As the "number" argument, you need to enter a reference to the cell that contains the value whose ordinal number you want to find out. The reference argument contains the address of the entire range that is being ranked. The "order" argument can have two values ​​- "0" and "1". In the first case, the order is counted in descending order, and in the second case, in ascending order. If this argument is not specified, then it is automatically considered by the program to be zero.

This formula can be written manually, in the cell where you want the processing result to be displayed, but for many users it is more convenient to set the input through the Function Wizard window.


Lesson: Function Wizard in Excel

Method 2: RANK.AVG function

The second function that performs the ranking operation in Excel is RANK.AVG. Unlike the RANK and RANK.RW functions, if the values ​​of several elements match, this operator returns the average level. That is, if two values ​​are of equal magnitude and come after value number 1, then they will both be assigned the number 2.5.

The syntax of RANK.AVG is very similar to that of the previous statement. It looks like this:

The formula can be entered manually or through the Function Wizard. We will dwell on the last option in more detail.

  1. We select a cell on the sheet to display the result. In the same way as the previous time, go to the Function Wizard through the "Insert Function" button.
  2. After opening the Function Wizard window, select the name RANK.AVG in the list of the “Statistical” category and click on the “OK” button.
  3. The arguments window is activated. The arguments for this operator are exactly the same as for the RANK.EQ function:
    • Number (address of the cell containing the element whose level should be determined);
    • Link (coordinates of the range within which the ranking is performed);
    • Order (optional argument).

    Entering data into the fields occurs in exactly the same way as in the previous operator. After all the settings are done, click on the "OK" button.

  4. As you can see, after the performed actions, the result of the calculation was displayed in the cell marked in the first paragraph of this instruction. The total itself represents the place that a specific value occupies among other values ​​in the range. Unlike the result of RANK.EQ, the result of the RANK.AVG operator can have a fractional value.
  5. As with the previous formula, by changing the references from relative to absolute and the selection handle, you can rank the entire range of data using autocomplete. The algorithm of actions is exactly the same.

Lesson: Other statistical functions in Microsoft Excel

Lesson: How to autocomplete in excel

As you can see, there are two functions in Excel to determine the ranking of a particular value in a data range: RANK.EQ and RANK.AVG. For older versions of the program, the RANK operator is used, which, in fact, is a complete analogue of the RANK.RV function. The main difference between the formulas RANK.EQ and RANK.AVG is that the first of them indicates the highest level when the values ​​match, and the second displays the average as a decimal fraction. This is the only difference between these operators, but it must be taken into account when choosing which function is best for the user to use.

We are glad we were able to help you resolve the issue.

Ask your question in the comments, describing in detail the essence of the problem. Our experts will try to answer as quickly as possible.

Often, calculating the rank of values ​​from a given range of data is useful. For example, if you have a worksheet containing annual sales numbers for the sales force, you can find out how each of them ranks, from highest to lowest.

If you have already used the function RANK, you may have noticed that the positions returned by this function do not have relationships that might suit you. For example, if two values ​​are related by a third place, then the function RANK will assign the number 3 to both. You might prefer to assign each an average rank - in other words, a rank of 3.5 for both values ​​placed in third place.

Excel 2010 contains a new feature called RANK.CP which fixes this issue. On fig. Figure 98.1 shows a table that uses two methods to rank the values ​​in a column. The first method (column C) applies the function RANK, and column D specifies new feature RANK.SR. Range B2:B16 is named Sales.

Note that two people are in third place. Function RANK assigns a value of 3 to each of them, and the RANK.CP function -3.5. In addition, three people are in tenth place. Function RANK gives each of them a rank of 10, and the function RANK.CP, on the other hand, gives the three of them a rank of 11, which is an average of 10,11, and 12.

Formulas that use a function RANK.CP, will return an error in previous versions Excel. If you are going to share your workbook with people who use a version of Excel earlier than 2010, you can use an array formula instead of a function RANK.SR.

Look at fig. 98.1 and enter the following array formula in row 4. Then copy it into the cells below. The result will be exactly the same as when using the function RANK.SR:
=SUM(1*(B4

An array formula is a special kind of formula that operates on internal arrays of data. When entering an array formula, you must press Ctrl+Shift+Enter(not just Enter) to indicate that this is an array formula. Excel will add curly braces around the formula to remind you that this is an array formula. If you don't click Ctrl+Shift+Enter, the formula will not work.

Cystitis is one of the most common inflammatory diseases. More often, women suffer from this due to the peculiarities of the anatomical structure, but cystitis also occurs in men. The likelihood of developing the disease increases after suffering inflammatory diseases, the presence of chronic foci of infection in the body, or any other condition that contributes to the suppression of the body's immune system, as a result of a violation of the diet.



Loading...
Top