The RANK ( ) , English version RANK (), returns the rank of a number in a list of numbers. The rank of a number is its magnitude relative to other values ​​in the list. For example, in the array (10; 20; 5) the number 5 will have rank 1, since this is the smallest number, 10 is rank 2, and 20 is rank 3 (this is ascending rank when the smallest value is assigned rank 1). If the list is sorted, then the rank of the number will be its position (if there are no repetitions).

Syntax

Number- the number for which the rank is determined.

Link- a link to a list of numbers (a range of cells with numbers). You cannot specify an array directly, the formula = RANK (10; (10: 50: 30: 40: 50)) will not work. But, if you enter the formula = RANK (B7; $ A $ 7: $ A $ 11), then it will work (although the cell B7 - outside the list with numbers). If in B7 contains a number outside the list with numbers, the formula will return the # N / A error.

Order- a number that determines the ordering method.

• If the order is 0 (zero) or omitted, then MS EXCEL assigns rank = 1 to the maximum number, lower values ​​are assigned b O higher ranks.
• If the order is any nonzero number, then MS EXCEL assigns rank = 1 to the minimum number, b O higher values ​​are assigned b O higher ranks.

Determine the rank in the list without repetitions

If the list of numbers is in the range A7: A11 , then the formula = RANK (A7; $ A $ 7: $ A $ 11) will determine the rank of the number from the cell A7 (see example file).

Because argument order omitted, then MS EXCEL assigned rank = 1 to the maximum number (50), and the maximum rank (5 = the number of values ​​in the list) to the minimum (10).

Alternative option: = COUNTIF ($ A $ 7: $ A $ 11; ">" & A7) +1

In the column WITH given the formula = RANK (A7; $ A $ 7: $ A $ 11; 1) with rank ascending, rank = 1 assigned to the minimum number. Alternative option: = COUNTIF ($ A $ 7: $ A $ 11; "<"&A7)+1

If the original is a list, then the rank of the number will be its position in the list.

Relationship between LARGE () / SMALL () and RANK () functions

The LARGE () and RANK () functions are complementary in the sense that by writing the formula = LARGE ($ A $ 7: $ A $ 11; RANK (A7; $ A $ 7: $ A $ 11)) we will get the same original array A7: A11 .

Determine the rank in the list with repetitions

If the list contains, then duplicate values ​​(highlighted in color) will be assigned the same rank (maximum). The presence of duplicate numbers affects the ranks of subsequent numbers. For example, if a number 10 with rank 5 appears twice in a list of integers sorted in ascending order, the number 11 will be ranked 7 (none of the numbers will be ranked 6).

Sometimes this is not convenient and it is required that the ranks are not repeated (for example, when determining prize places, when it is impossible for several people to take one place).

The formula will help us with this = RANK (A37; A $ 37: A $ 44) + COUNTIF (A $ 37: A37; A37) -1

It is assumed that the original list with numbers is in the range A37: A44 .

Note... The function has been added RANK.RV (number; ref; [order]) If multiple values ​​have the same rank, the highest rank of that set of values ​​is returned (assigns the same rank values ​​to repeated numbers). An example file explains how this function works. Also added the function RANK.SR (number; ref; [order]) If multiple values ​​are of the same rank, the average is returned.

When working with data, there is often a need to find out what place a particular 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 task - RANK... For the sake of compatibility, it is left in a separate category of formulas in modern versions of the program, but in them it is still desirable to work with newer analogs, if there is such a possibility. These include the statistical operators RANK.RV and RANK.WE... We will talk about the differences and the algorithm for working with them further.

Method 1: RANK.RV 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 displays the highest 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 for this operator is written as follows:

Arguments "number" and "link" are required and "Order"- optional. As an argument "number" you need to enter a link to the cell that contains the value, the serial number of which 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 "one"... In the first case, the order is counted downward, and in the second, upward. If this argument is not specified, then it is automatically considered equal to zero by the program.

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.SR function

The second function that performs the ranking operation in Excel is RANK.WE... Unlike functions RANK and RANK.RV, if the values ​​of several elements match, this operator returns the middle level. That is, if two values ​​are of equal magnitude and follow the value numbered 1, then both of them will be assigned the number 2.5.

Syntax RANK.WE very similar to 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.

As you can see, Excel has two functions for determining the ranking of a specific value in a data range: RANK.RV and RANK.WE... For older versions of the program, the operator is used RANK, which, in fact, is a complete analogue of the function RANK.RV... The main difference between formulas RANK.RV and RANK.WE consists in the fact that the first of them indicates the highest level when the values ​​match, and the second displays the average in the form of 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.

This article will discuss several statistical functions in Excel:

MAX function

Returns the maximum numeric value in a list of arguments.

Syntax: = MAX

Usage example:

=MAX((1; 2; 3; 4; 0; -5; 5; "50")) - returns the result 5, while the string "50" is ignored, since given in an array.
=MAX(1; 2; 3; 4; 0; -5; 5; "50") - the result of the function will be 50, because the string is explicitly given as a separate argument and can be converted to a number.
=MAX(-2; TRUE) - returns 1, because boolean is explicitly set, so it is not ignored and converted to one.

MIN function

Returns the minimum numeric value from the argument list.

Syntax: = MIN(number1; [number2]; ...), where number1 is a required argument, all subsequent arguments (up to number255) are optional. The argument can be numeric, range references, and array references. Text and Boolean values ​​in ranges and arrays are ignored.

Usage example:

=MIN((1; 2; 3; 4; 0; -5; 5; "- 50")) - returns the result -5, the text string is ignored.
=MIN(1; 2; 3; 4; 0; -5; 5; "- 50") - the result of the function will be -50, since the string "-50" is specified as a separate argument and can be converted to a number.
=MIN(5; TRUE) - returns 1, since the boolean value is explicitly specified as an argument, so it is not ignored and converted to one.

LARGE function

Returns the value of the nth largest element from the specified set of elements. For example, the second is the largest, the fourth is the largest.

Syntax: = LARGE(array; n), where

• n is a natural number (other than zero) indicating the position of the element in descending order. If you specify a fractional number, then it is rounded up to the nearest integer (fractional numbers less than one return an error). If the argument exceeds the number of elements in the set, then the function returns an error.

Usage example:

The image shows 2 ranges. They completely coincide, except that in the first column the range is sorted in descending order, it is presented for clarity. The function refers to the range of cells in the second column and returns the item that is the 3 largest value.

This example uses a range with duplicate values. It can be seen that the cells are not assigned the same ranks if they are equal.

SMALL function

Returns the value of the nth smallest element from the specified set of elements. For example, third smallest, sixth smallest.

Syntax: = LEAST(array; n), where

• array - a range of cells or an array of elements containing numeric values. Text and boolean values ​​are ignored.
• n is a natural number (other than zero) indicating the position of the element in ascending order. If you specify a fractional number, then it is rounded down to the nearest integer (fractional numbers less than one return an error). If the argument exceeds the number of elements in the set, then the function returns an error.

The array or range does NOT need to be sorted.

Usage example:

RANK function

Returns the position of an element in a list by its value, relative to the values ​​of other elements. The result of the function will not be the index (actual location) of the element, but a number indicating what position the element would occupy if the list was sorted either in ascending or descending order.
Basically, the RANK function does the opposite of the LARGE and SMALL functions, since the former finds the rank by value, and the latter finds the value by rank.
Text and boolean values ​​are ignored.

• number Required. The numeric value of the element to find the position of.
• reference is a required argument, which is a reference to a range with a list of elements containing numeric values.
• order is optional. Boolean value responsible for the sorting type:
  • FALSE is the default. The function checks the values ​​in descending order.
  • TRUE - the function checks the values ​​in ascending order.

If there is no element with the specified value in the list, the function returns the # N / A error.
If two elements have the same value, then the rank of the first one found is returned.
The RANK function is present in versions of Excel starting in 2010 for backward compatibility only. Instead, new functions have been introduced with the same syntax:

• RANK.RV - full identity of the RANK function. The added ending ".РВ" indicates that, if elements with equal values ​​are found, the highest rank is returned, i.e. the very first one found;
• RANK.SR - ending ".СР", informs that, if elements with equal values ​​are found, their average rank is returned.

Usage example:

In this case, the return of the rank is used when checking an ascending range of values.

The following image illustrates the use of a function with descending value validation. Since there are 2 cells in the range with a value of 2, the rank of the first one found in the specified order is returned.

AVERAGE function

Returns the arithmetic mean of the specified arguments.

Syntax: = AVERAGE(number1; [number2]; ...), where number1 is a required argument, all subsequent arguments (up to number255) are optional. The argument can be numeric, range references, and array references. Text and Boolean values ​​in ranges and arrays are ignored.

Usage example:

The result of executing the function from the example will be the value 4, since boolean and text values ​​will be ignored and (5 + 7 + 0 + 4) / 4 = 4.

AVERAGE function

Similar to the AVERAGE function, except that true boolean values ​​in ranges are set to 1, and false values ​​and text are set to zero.

Usage example:

The return value in the following example is 2.833333, since text and boolean values ​​are set to zero, and boolean TRUE equals one. Therefore, (5 + 7 + 0 + 0 + 4 + 1) / 6 = 2.833333.

AVERAGEIF function

Calculates the arithmetic mean of cells that meet a specified condition.

Syntax: = AVERAGEIF(range; condition; [average_range]), where

• range Required. The range of cells to check.
• condition Required. The value or condition of the test. Wildcards (* and?) Can be used for text values. Conditions like more, less are written in quotes.
• averaging_range is an optional argument. Reference to cells with numeric values ​​to determine the arithmetic mean. If this argument is omitted, then the range argument is used.

Usage example:

It is necessary to find out the arithmetic mean for numbers that are greater than 0. Since only 3 numbers are presented for the calculation, of which 2 are zero, then only one value remains, which is the result of executing the function.
Also, the function does not use the last argument, so the range from the first is accepted instead.

The following example examines a table showing employee wages. You need to find out the average salary for each position.

AVERAGEIFS function

Returns the arithmetic mean for cells that meet one or more conditions.

Syntax: = AVERAGEIFS(averaging_range; criteria_range1; condition1; [condition_range2]; [condition2]; ...), where

• average_range Required. Reference to cells with numeric values ​​to determine the arithmetic mean.
• criteria_range1 Required. The range of cells to check.
• condition1 Required. The value or condition of the test. Wildcards (* and?) Can be used for text values. More, less conditions are enclosed in quotes.

All subsequent arguments from condition_range2 and condition2 to condition_range127 and condition127 are optional.

Usage example:

We use the table from the example of the previous function with the addition of cities for employees. Let's derive the average salary for electricians in the city of Moscow.
The result of executing the function is 25,000.
The function takes into account only those values ​​that fit all conditions.

COUNT function

Counts the number of numeric values ​​in a range.

Syntax: = CHECK(value1; [value2];…), where value1 is a required argument that takes a value, cell reference, cell range, or array. Arguments value2 through value255 are optional and are the same as value1.

Boolean values ​​in ranges and arrays are ignored. If such a value is specified explicitly in the argument, then it is counted as a number.

Usage example:

=CHECK(1; 2; "5") is the result of function 3, since the string "5" is converted to a number.
=CHECK((1; 2; "5")) - the result of the function execution will be the value 2, since, unlike the first example, the number as a string is written in the array, so it will not be converted.
=CHECK(1; 2; TRUE) - result of function 3. If the boolean value were in the array, it would not be counted as a number.

COUNTIF function

Counts the number of cells in a range that match a specified condition.

Syntax: = COUNTIF(range; criterion), where

• range Required. Accepts a reference to a range of cells to test for a condition.
• criterion Required. A check criterion containing a value or conditions of the type greater than or less, which must be enclosed in quotes. Wildcards (* and?) Can be used for text values.

Usage example:

COUNTIFS function

Returns the number of cells in a range that satisfy a condition or set of conditions.
This function is similar to the COUNTIF function, except that it can contain up to 127 ranges and criteria, where the first is required and the next ones are not.

Syntax: = COUNTIFS(range1; criterion1; [range2]; [criterion2];…).

Usage example:

The figure shows the use of the COUNTIFS function, where the number of people with a salary of over 4000 rubles and living in Moscow and the Moscow region is counted. However, the * wildcard is used for the last condition.

COUNT function

Counts nonblank cells in the specified range.

Syntax: = COUNT(value1; [value2]; ...) where value1 is a required argument, all subsequent arguments up to value255 are optional. The value can contain a reference to a cell or a range of cells.

Usage example:

The function returns 4 because cell A3 contains a text function that returns an empty string.

COUNTBLANK function

Counts blank cells in the specified range.

Blank lines (= "") are counted as blank.

You do not have enough rights to comment.

A. Ranking of Qualitative Features

Example 1.

The subject is offered a task in which seven personal qualities must be ordered (ranked) in two columns: in the left column in accordance with the characteristics of his "real self", and in the right column in accordance with the characteristics of his "ideal self". The ranking results are given in Table 2.

Table 2.

I'm real	Personality traits	I'm perfect
	responsibility
	sociability
	persistence
	energy
	cheerfulness
	patience
	determination

B. Ranking of quantitative traits

Example 2.

As a result of the diagnosis of neurosis in five subjects according to the method of K. Heck and H. Hess, the following points were obtained: 24, 25, 37, 13, 12. This series of numbers can be ranked in two ways:

a larger number in a row is given a higher rank, in this case it will turn out: 3, 4, 5, 2, 1;

the larger number in the row is given a lower rank: in this case, it will turn out: 3, 2, 1, 4, 5.

4.2. Checking the correct ranking

A. Formula for calculating the sum of ranks by column (line)

If ranked N numbers, then the sum of the ranks is calculated by the formula (1.1):

1+2+3+...+N= N (N + 1)/2 (1.1)

In the case of example 1, the number of ranked features was equal to N = 7, so the sum of the ranks calculated by formula (1.1) should be 7 (7 + 1) / 2 = 28.

Let's add the rank values ​​separately for the left and right columns of the table:

7 + 1 + 3+ 2 + 5 + 4 + 6 = 28 - for the left column and

1 + 5+ 7+ 6 + 4 + 3 + 2 = 28 - for the right column.

The sums of the ranks matched.

B. Formula for calculating the sum of ranks in a table

Column ranking.

Example 3. The results of testing two groups of subjects, 5 people each, using the method of differential diagnosis of depressive states by V.A.Zhmurov are presented in Table 3.

Table 3.

Subject number

Task: to rank both groups of subjects as one, that is, to combine the samples and put down the ranks of the combined sample, while maintaining, however, the difference between the groups. We will do this in Table 4, and so that the maximum value will be set to the minimum rank.

Table 4.

Test subject number

Since we have received the sums of the rank by columns, the total sum of the ranks can be obtained by adding these sums: 31 + 24 = 55.

To apply formula (1.1), you need to calculate the total number of subjects - this is 5 + 5 = 10.

Then by formula (1.1) we get: 10 (10 + 1) / 2 = 55.

The ranking is correct.

If the table has a large number of rows and columns, then you can use a modification of formula (1.1)

Sum of ranks in the table

= (kc + 1) kc / 2, (1.2)

where k is the number of rows, c is the number of columns.

Let's calculate the sum of ranks by formula (1.2.) For our example. Table 2 has 5 rows and 2 columns, the sum of the ranks = ((5 2 + 1) 5 2) / 2 = 55

Ranking by row

Example 4.

Table 5. Let's rank by line.

Subject number
Column Sums

In this table, the smallest number is assigned the smallest rank. The sum of the ranks for each line should be equal to 6, since we have three values ​​ranging: 1 + 2 + 3 = 6. In our case, it is so. Now let's sum up the ranks for each column separately and add them up.

The calculation formula of the total amount of ranks for ranking by rows for a table is determined by the formula:

Sum of ranks = nc (c + 1) / 2, (1.3.)

where n is the number of subjects in a column, c is the number of columns (groups).

Let's check the ranking for our example.

The real sum of ranks in the table is 8 + 10 + 12 = 30

According to the formula (1.3): 5 3 (3 + 1) / 2 = 30.

Therefore, the ranking is correct.

The case of the same ranks

Ranking of qualitative features

A. Ranking of Qualitative Features

Let's modify example 1. and rewrite it in table. 6. Suppose that in assessing the characteristics of the "real self" the subject believes that such qualities as "persistence" and "energy" should have the same rank. When conducting the ranking (column 1 of Table 6), these qualities must be put down mental ranks (MR), as numbers, necessarily going in order one after another, and mark these ranks with parentheses - (). However, since these qualities, in the opinion of the subject, should have the same ranks, then in the second column of Table. 6, referring to "I am real", should place the arithmetic mean of the ranks in parentheses, i.e. (2 + 3) / 2 = 2.5. Thus, the second column of the table. 6 and will be the final result of the ranking of the features of the "real self" given to the subjects, and the ranks entered in this column will be called real ranks (P.P.).

Similarly, when ranking "I ideal", the subject believes that such qualities as "sociability", "energy" and "cheerfulness" should have the same rank. Then, when conducting a ranking (see column 5 of Table 6), these qualities must be put down mental ranks as numbers, necessarily going in order one after another, and mark these ranks with parentheses - (). However, since these qualities, in the opinion of the subject, should have the same ranks, then in the fourth column of the table. 6, referring to "I ideal", you should place the arithmetic mean of the ranks in parentheses, i.e. (4 + 5 + 6) / 3 = 5. Thus, the fourth column of Table 6 will be the final result of the ranking of the characteristics of the “ideal self” given to the subjects, and the ranks indicated in this column will be called real ranks. Let us emphasize once again that mental (conditional) ranks, like numbers, should be located one after another in order, despite the fact that the ranked qualities in the data table are not next to each other.

Table 6.

I'm real		Personality traits	I'm perfect
		Responsibility
		Sociability
		Persistence
		Energy
		Cheerfulness
		Patience
		Determination

Legend: M.R. - mental, or conditional, ranks; P.P. - real ranks.

Let's check the correctness of the ranking in the second column of the table. 6, i.e. real ranks related to "I am real":

1 + 2,5 + 2,5 + 5 + 4 + 6 = 28.

Let's check the correctness of the ranking in the fourth column of the table. 6, i.e. real ranks related to "I am ideal":

1 + 2 + 3 + 5 + 5 + 5 + 7 = 28.

By formula (1.1), the sum of the ranks is also equal to 28. Consequently, the ranking is correct.

B. Ranking quantitative characteristics (numbers)

Let's look at the ranking of numbers using an example.

Example. The psychologist received the following values ​​of the indicator of non-verbal intelligence in 11 subjects: 113,102,123,122, 117, 117, 102, 108, 114, 102, 104. It is necessary to rank these indicators, and it is best to do this in table 7.

Table 7

Subjects number	IQ indicators	Thought ranks (M.R.)	Real Ranks (P.P.)

In the example, there were two groups of equal numbers (102, 102 and 102; 117 and 117), since the numbers in the groups are different, then the brackets given to these groups of numbers are also different.

Let us check the correctness of the ranking by formula (1.1). Substituting the initial values ​​in the formula, we get: 11 · 12/2 = 66. Summing up the real ranks, we get:

6 + 2 + 11 + 10 + 8,5 + 8,5 + 2 + 5 + 7 + 2 + 4 = 66.

Since the sums are the same, therefore, the ranking is correct.

The rules for ranking numbers are as follows.

1. The smallest (largest) numerical value is assigned rank 1.

2. The highest (lowest) numerical value is assigned a rank equal to the number of ranked values.

3. Identical numbers should be assigned the same ranks.

4. If in the ranked row several numbers turned out to be equal, then they are assigned a real rank equal to the arithmetic mean of those ranks that these numbers would receive if they were in order one after another.

5. If in the ranked row there are two or more groups of equal numbers, then rule 4 is applied for each such group, and the mental ranks of each group are enclosed in different brackets.

6. The total amount of real ranks must coincide with the calculated one, determined by the formula (1.1).

If it is necessary to rank a sufficiently large number of objects, they should be combined according to some criterion into sufficiently homogeneous classes (groups), and then the resulting classes (groups) should be ranked.

Spearman's and Kendall's correlation coefficients are most often applied to measurements obtained in the rank scale, and, in addition, various criteria for differences are used.

1. In the additional column, in which we will indicate the rating, we insert the RANK function (write in the cell = RANK and select the function proposed by EXCEL from the list, click in the fx formula bar)

2. Fill in the arguments in the window that opens: "Number" - we indicate the first value in our table in the same line where the formula is.

3. "Link" - indicate the entire data array, ie. a range with all numbers (sales values).

4. We fix the boundaries of this range (press F4 on the keyboard) so that the address of the range does not "move out" when further stretching, and press OK.

5. Stretch the formula down to all cells in the "rating" column.

When using this function, the rating is calculated automatically, and if you change any value, then the rating will be automatically recalculated.

If you liked the material or even came in handy, you can thank the author by transferring a certain amount using the button below:
(to transfer by card, click on VISA and then "transfer")