Using the major and minor functions to do ranking in Excel

118
47471
Using the major and minor functions to do ranking in Excel
Excel Spreadsheets

With the increasing use of Excel, people are looking for different ways to automate their tasks and data assessments. The functions available offer a wide range of possibilities, being possible to apply them in different situations. In this sense, we will demonstrate how to build a ranking using the functions "major" and "minor", presenting in detail their structures.

Excel Online Course

Knowing the functions "greater" and "minor"

The functions we use in our example have very similar structures and easy assimilation, differentiating only by the fact that one will return the highest value and another the lowest value. Given the similarity, we will show the "larger" function whose syntax is as follows:

MAJOR (matrix k)

Both syntax arguments are required. The array corresponds to the data range from which the desired values ​​are extracted. Already ok (k-th value) refers to the position of the highest value in the supplied array.

Understanding the concept of k-th value

K-th value is a term of Applied Statistics, which corresponds to the magnitude of a value in a given series. In a set formed by the numbers (4, 8, 7, 6, 2), for example, we can observe that the third greater value is "6". In this case, the kth value would be "3", equivalent to third. The kth of "8", the highest value of the set, would be "1", and so on. The same principle applies to the kth of the smallest value: for "4", in the example above, the kth would be "2" (second smallest value).

Building a ranking with the functions "major" and "minor"

To apply the functions in the construction of a ranking, we will first make the table, which will be our matrix. In our example, we will use a table that lists the total sales of the employees of a company in a certain period, sorting them in alphabetical order.

Using the major and minor functions to do ranking in Excel

After this step, we will now prepare the cells in which the functions will be applied and displayed the ranking. For this, we will make a new table that will present the five largest and the five lowest total sales of employees. So we will be ready to apply the functions.

Using the major and minor functions to do ranking in Excel

Entering Functions

In the table that we create for the ranking, we will click on the cell that will display the highest sales total, which in our example will be the cell "H6", inserting the "greater" function. The matrix will be the table with the sales totals (if there were, in addition to the cells that display the sales figures, any other cell with numbers, we would have to be careful to only select the data range that would make the composition of the ranking - in this example , the interval between "B2" and "B15"). Completing the function, the "k" would be the 1 number, since we want to obtain the highest sales value. By typing "ENTER", we can see that the value of R $ 9.000,00 has actually been displayed.

Using the major and minor functions to do ranking in Excel

Using the major and minor functions to do ranking in Excel

A practical way to populate the other fields is, after locking the array, copy the function. First, we'll click the bottom corner of the cell, dragging it to the cells below. In the sequence, we will change the value of "k" according to the position in the ranking (in the cell "H3" the "k" would be the number 2 - second largest value, and so on).

When clicking on the cell "J6", we will insert the "minor" function, which has the same parameters as we know. Therefore, just repeat the previous steps so that we have in the ranking the five lowest total sales.

Using the major and minor functions to do ranking in Excel

So, our ranking is finished, just do the formatting at will. Excel functions stand out for versatility and can be used for a variety of purposes, as we have seen in the example.

Excel Online Course

Please tell us below how you use the "major" and "minor" functions in your worksheets and keep track of our blog for more tips. 

Excel Spreadsheets

118 COMMENTS

  1. Let's assume that one or more sellers had no sales and got $ 0,00 of sales, as ignore this value at the time of ranking. In my case these are product values ​​that also deal with values ​​that I need to put in ascending order disregarding the values ​​$ 0,00.

  2. Hi Acacio, you can use the PROCV function. In this case, you would need to create an auxiliary column on the right side of the SOMASE column for PROCV to work.

  3. Good afternoon!
    I have a question and I hope you can help me. In the case above, suppose that the winner “Henrique” had made 3 sales of R $ 3.000,00, however, in the column with the names of the salespeople there was not the total amount which is R $ 9.000,00, but the 3 separate sales . Considering that to compose my Ranking I had used the Somase formula, which formula would I use to return the name of who is the person who made the sales that totaled R $ 9.000,00?

    Thankful!!

  4. I would try the following logic. Assuming cup number 23 is in cell A1

    - (A1 / 4) - (ARRED (A1; 0) / 4) - so you get exactly what the decimal value is
    - then you can use an SE for an SE that is greater than 0,5, be ARRED (A1; 0) / 4 +1 or if it is less, only the ARRED

  5. Hello good morning blessed I'm trying to make a calculation with the following I have some sectors for example
    cup
    refectory
    financial
    file
    quality
    in these sectors I have a fixed population for example
    Cup 23
    refectory 17
    9
    27 file
    6 quality
    I need to use 25% of the population for training, but I want to use the higher percentage.
    for example 27 / 4 = 6.75 in this case I would use 7 population member because the division calculation gave an integer and the rest of the division gave above 5, but if in the following calculation 5 / 4 = 1.5 in this case I will use 1 member because the division gave an integer and the rest of the division my half of one.
    Can you do a calculation like this in Exel?
    I thank you for your attention.

  6. In this case you can create an auxiliary column by adding a small value and differentiating all values. So, afterwards you can have only one greater, even if it is equal to another

  7. Good Night, now take me a doubt, if in a column of numbers I have several values, however I need to find the Biggest and the Smallest value and only highlight 1 of them, how can I proceed or is it impossible to perform this formatting. Note: In this case, some numbers are repeated in both the largest and the smallest, however I need to highlight only 1 of them.
    Example:
    177
    179-Largest
    176
    179-Largest
    170-Minor
    171
    172
    170- Minor

    But I need that in the worksheet just highlight 1 of the Greater and 1 of Minor. Without repeating the highlighted.

  8. Hello Kabaré!
    I was trying to sort with duplicate values ​​and was not getting it, but with that formula you posted it worked out fine.

    Thanks!!

  9. Hello Rafael, great function. Please, in your example, how to bring the name of the seller with the highest value? I have a spreadsheet with several tabs and in one of them I add the total amount of how much each seller has sold. So with “BIGGEST” I can see which ones sold the most (ex: = BIGGEST (SELLER! R: R; 2) to show the second highest selling value) but I can't tell who it is… I would have to look for that higher value and show the name of that seller. Thanks

  10. Like this:
    I have two values ​​of 99%
    with the auxiliary column I will add to one 0,0000001 and in the other 0,0000002
    so it will be different 99,000001% and 99,0000002%
    this is enough to different at the time of creating the order. In this case, you must use as base the auxiliary column with the summed values

  11. Good morning Rafael, my doubt is similar to the doubt posted by Mackeila Goulart in 26 / 01 / 2017. In the ranking that I am developing is by performances (values ​​in percentage), in some cases equal values ​​appear and PROCV repeats the name of the collaborator instead of identifying that 2 collaborators have 99%.
    I tried to make the auxiliary column with sequential numbering, but I could not figure out how to use it. Please, how do I use this auxiliary column to differentiate the employees in the ranking?

  12. Hi Mauricio, you can create auxiliary formulas to evaluate values, for example an SE that evaluates whether the value in question is greater or less than the 20% of the lowest value. Then just create the conditional formatting on the cells that have the result of the SE when the value is greater than the 20%

  13. Sorry for the misunderstanding: I wanted to address Rafael (but I'm grateful if anyone else can help)

  14. Hello Gilmar! Thanks in advance if you can help me:
    I need excel to highlight the cell (or cells) that have 20% greater than the lowest value found.
    Attempting to Exemplify: In this case, each row in the worksheet has 3 unit values ​​of products. I need it to identify which values ​​exceed 20% on the lowest value of this line.
    grateful!

  15. Hi Gilmar, you can create a VBA code that updates the column's classification whenever a change occurs in the worksheet

  16. HELLO.
    A help.
    I have a table with 4 columns, "name", "cash sales", "credit sales" and "total sales".
    It must be updated daily and indicate the decreasing ranking in the total sales column, with the “cash sales” and “installment sales” columns being updated from other tables.
    How can I do this.
    Gilmar

  17. Hi Maike, you can do it using PROCV or SE. In the case of the SE that is simpler, it would be something like (assuming that in A1 we have the sales result)
    = SE (A1> 100000; ”Maximum Goal”; SE (A1> 80000; ”Average Goal”; SE (A1> 60000; ”Minimum Goal”; ”Did not reach any goal”)))

  18. Good afternoon!

    I would like simple help. I have a commission worksheet with 3 possible goals attainable by the total amount.

    EX: If the total sales value is R $ 60000,00 is Minimum Target
    If the total sales value is R $ 80000,00 is Average Goal
    If Total Sales Value is $ 100000,00 is Maximum Target

    My worksheet already calculates the total sales value and I would like to know how I can manage to appear the names next to the total sales, according to the goals above. EX: If I sold R $ 75000,00 I would like it to appear that I am in the Minimum Meta as well as if I sell R $ 100000,00 show in the cell next to this value I am in the Maximum target.

    How can I make this friend?

    Thank you!

  19. You can put an auxiliary column that analyzes the sum of the lines and another to evaluate if the sum reached the goal. If you reach or exceed it, you can have a different value (using an SE function) and then you can use a PROCV to fetch this value and return the date it was reached

  20. I have a worksheet with DATE, VALUE, NAME, META.

    I need to add the values ​​until I reach the goal, however, I need the date on which the goal was reached, and create a ranking of the first three (podium), with the date of each “achievement of the goal”.

  21. To get automatically in ascending order you would need to create a VBA code to run whenever you insert a new item on that tab and update a filter in ascending order. If it can be manual, just use the filter.

  22. Good morning, Rafael, I need your help.
    you need a formula that when entering the date automatically it is in ascending order,

  23. Hi Marco, I really could not think of any logic for the negative numbers. Maybe if you tell me a little more about the possible numbers I can think of some way to get into an auxiliary table that allows you to concatenate correctly.

  24. Hello,

    I have a complicated impasse, I am not finding a solution. I need to return values ​​like "1st"; "2nd"; “3rd”, etc., but the problem is that I have repeated values ​​in a first condition, and so the formula needs to go to a second criterion condition to differentiate which number is greater. In the example below, column B is more important than C, which in turn is more important than D, so the result presented should be as shown below.
    ABCD RESULT
    JOAO 7 4 2 2º
    MARIA 6 3 -2 4º
    PEDRO 6 3 -1 3º
    RODRIGO 7 5 1 1º

    I had a great idea where I assembled the values ​​by making a single number, using the & function. In this way, I returned values: JOAO = 742; MARIA 63-2; PEDRO 63-1; RODRIGO 751. After that, I would just apply the ORDER function and find the desired result. This would solve my problem if I didn't have the negative values, because excel is not understanding the value in this way and thus is unable to make the ranking. With the numbers only positive, my idea is already working.

    How can I circumvent this problem? The option of formatting a table with filters is not interesting in my case, although I know that excel would do this ranking with various secondary conditions. What I really need is this way that I showed, where I return in a column next to the answers 1º, 2º, 3º etc.

    Thank you.

  25. Hi Layra, this will depend on how your data table is presented. If 50 is worse than 30, you can format the axis of the graph to be displayed in reverse order.

  26. Hi Rafael, I need a lot of help. Thanks in advance.
    I have to do a ranking chart, but when the position improves, for example, the municipality leaves 50 and goes to 30º, the graph line would have to grow showing improvement. But in the chart that made the line decreases, that is, indicates worsening.
    How do I get this right?

  27. Hi Cláudio, you can nest a function or use an auxiliary column. For example, you can create a column with the values ​​of 1 until the last one and at the time of using the value K, it will refer to the first one (value 1) and, when dragging, it will sequentially pick up the values, 2, 3, 4, etc.

  28. Hello Rafael, how are you? I have a doubt: what if my database was very large, would I have to change the “K” value one by one, or can I nest a function to automate this task?

  29. Hi Charles, in that case you need to use the nested SE conditional. It would look like = SE (A1 = 0; ”Very good”; SE (A1 = 1; ”Good”; if (A1 = 2; ”Bad”; ”Very bad”)

  30. Hi how do I sort the 0 value in very good, the value 1 in good, the value 2 in bad and the value 3 in very bad?

  31. Hi Aafneto. I could not think of any logic quickly that can do this analysis in Excel. I recommend that you use forum.luz.vc to see if any of the participants can help you.

  32. https://uploads.disquscdn.com/images/33a8f0819a5e8bef459f520e569e76a294918696c4ca3f416590ed8856efb051.png Hello I need a help .. I have a table that has more than one variable .. ex: column a = name of people, column b = number of points column c = number of victories, column d = number of frequency,… being so I need that, in order to rank this information, taking into account the following criteria: 1 - (column b), 2 (column c), 3 (column d)… that is… if there is no tie, it will be considered exclusive to column B as greater ... but if the values ​​in this column are the same ... it goes to the next column ... and so on ...

  33. Hi Carlos, in these cases with repeated values, what I usually do is add an extra column with a very small increasing value per line. For example, line 1 = 0,0000000001, line 2 = 0,00000000002, and so on.

    Then I add this value to the value that is being ranked so that there is a small difference (imperceptible to the eyes, but sufficient for Excel to rank). So you can have rankings of “equal” values ​​appearing as different

  34. Hello, in my table I ran the values ​​for the day also, but there is incidence of repeated values ​​in the ranking. I would like to highlight the other days that contain the same value.

    What happens:
    1. 11 15 / ago
    2. 9 18 / ago
    3. 9 18 / ago
    4. 8 27 / ago
    5. 7 02 / ago
    6. 7 02 / ago
    7. 7 02 / ago

    What I wanted:
    1. 11 15 / ago
    2. 9 18 / ago
    3. 9 25 / ago
    4. 8 27 / ago
    5. 7 02 / ago
    6. 7 16 / ago
    7. 7 04 / set

    I'm using = GREATER (D: D; 10) and = VLOOKUP (K22; $ D $ 2: $ E $ 200; 2; FALSE) (the data are in column D, the dates in column E and the 'ranking' 1., 2. 3… in column K)

    Thank you in advance!

  35. Hi Vanessa, you will need to follow a step by step that will use filter and create two auxiliary columns.

    1 - add filter to the table (data tab> filter)
    2 - place in descending order using the filter
    3 - in the first auxiliary column, calculate how much each item is worth in% (item value divided by the total)
    4 - in the second auxiliary column make the sum name by name (for example, if the 1 is worth 30%, in this line we will have 30%, if the 2 is worth 10%, in the second line we will have 40% and just use a formula that does the accumulated sum - in the right logic, the last item will reach 100%

    This way you can see which names correspond to which percentages in billing. This logic is similar to the calculation of the ABC curve and Pareto graph, which I explain in this course here, in case you are interested - https://cursos.luz.vc/curso/curso-avancado-de-graficos-no-excel-2016/

  36. Good afternoon! I have a doubt. I was able to do the Ranking, but now I want to know the 150 names that I have, who represents me 20%, 30% and 50% of my billing. How do I do?

  37. You would need to make an adjustment to the ranking table by adding an extra column with the names on the right side of the values. For example:
    Adriano | 7200 | Adriano.

    So you would use a procv to fetch the 7200 value from the list and return the value from the second column

  38. You could explain me better, I'm with the same doubt .. I managed to do the ranking but then how do I use procv to pull the winners name?

  39. You can use an auxiliary list with the same numbers (but using the delete duplicates feature to have a unique value for each CPF), then you can use the CONT.SE function to count how many times each CPF appears, and then use the MAJOR function to put in order of the ones that appear more times for the ones that appear less times (the filter would also work for that case)

  40. Good afternoon, I have a spreadsheet with more than 1000 cpf. I would like to know how to get a list, with a cpf ranking and how many times it appears in the spreadsheet.
    Thank you.

  41. You have yes, just use the search value of each of the five highest values

  42. Hi Guilherme, in this case you can do a PROCV that returns the value referring to the highest analyzed value

  43. I would like to know how to bring the name of the person who came first. I have a ranking with five consultants and would like to bring the name instead of the value analyzed to set the ranking. Thank you.

  44. I usually use a subterfuge to create a helper column and add sequenced values ​​to each of the items. For example, in John I add + 0,0000001 and Mary + 0,0000002, and so on.

    Then at the time of analyzing the greater, it will understand the different values ​​and sort according to that sum. It is not a perfect result, but in practice it solves the problem of order

  45. I think his question (and my tb) is not equal names, but equal values. ex: João and Maria sold R $ 1000. How to make them one in 1º and another in 2º (assuming I can divide them into even positions with equal value), or divide the same position if necessary (both are 1º)?

  46. Hi Jonathas, I already answered in the other comment, but just to reinforce, you can use SOMASE to add the true values

  47. You can make a conditional sum with SOMASE, adding only the true values. Another way, even easier is using a CONT.SE that the result will be the same

  48. You could exemplify how to use the PROCV command according to Luiz Rezende's doubt, which is the same doubt as mine. Because I could not use the command

  49. Exactly my problem, my ranking is by percentage, I have several people with 100% and I would need their names .. when I use procv it only searches for 1 name for all cells, I tried some logic with concatenation and it also gave error…

  50. Dear, I have another difficulty and once again I ask for your help, as they helped me immensely the last time: I need to consider the value of a cell since a sentence is fulfilled, more or less as follows: I have a calendar from the 01st to the 31st (on the lines); in the columns I have a series of data, and in one I have the variable slack; filling the cell with an “X”, I managed to get to the logical test (true or false); however in the case of true, I need to add a cell in this line; to try to explain it in another way; as an example: I mark the selection clearance (column) on days (lines) 09, 15 and 20, as I said I managed to create the column true or false; now i need a value (from another column / cell) on these days 09, 15 and 20 compose a sum… from now on thank you for your attention and congratulate you for the help and attention you dedicate to everyone !!!

  51. I do not know a way to do array ordering. What I imagine to be possible is to create an auxiliary column of the GREATER formula to sort these values.

  52. Hi Rodrigo, you may need to use a PivotTable to better visualize this information

  53. HELLO, NEED TO ORDER NUMBERS IN GROWING OR DECREASING ORDER FROM A TABLE WHERE I HAVE TO COLLECT DIFFERENT LINES AND COLUMNS INFORMATION, HOW DO I DO IT? TO ORDER ONLY FROM THE LINE OR ONLY FROM THE COLUMN _ | I USED THE MINOR FUNCTION, WHICH WORKED PERFECTLY, BUT I CAN NOT ORDER CROSS DATA.

  54. If you use the term TRUE at the end it will find an approximate and not exact value, but it is not a variation that is usually useful.

    What I most point out is that you make some variation of their names so they are always unique, for example, using CONCATENATE with another column that generates a unique id for that value

  55. Rafael, good afternoon.

    If I have two or more names with the same value, PROCV will always display the name of the first value it finds, right? Is there a way for him to find other (s)?

  56. You can create a PROCV for the value and return the value next, in this case the name of the seller

  57. Hello! How do I make the ranking appear in the respective name of the seller, in the case of the example, instead of the value?

  58. In this case, if you already know which cells you want to evaluate, create an auxiliary table with only those two cells. If the behavior is dynamic, create an auxiliary table with formulas that look for the cells that you want

  59. Hello good day!!! I need to determine the highest value only between two cells and this function comprises a range; more or less this way: what is the largest value between C8 and H8, only between these two, not considering D8, E8, F8 and G8. Thank you in advance for your support!

  60. Hi Romualdo, you can use the copy and paste functionality without having duplicate values. For this, you can follow this path

    Select the list in question> Go to the Data tab> choose the Advanced option> in the window that opens choose the option to copy to another location> and mark only exclusive records

    That way you do not mess with your list and you can have a new mirrored list with only unique values

  61. Good afternoon, noble professor. See if you can help me please ... I have a relatively large table, with a column that has the number of days (for exchanging duplicates). I would like to order from the 1st to the 6th value, but it has several repeated days. In my case the sequence is 15, 16,18, 21… except that the fifth number, he brings the 21 again (because there are many times in the matrix repeating the number 21… and I needed him to make the sequence ignoring the other 21…

  62. Good afternoon Teacher, I'm trying to build a spreadsheet, it's already done, but I need to add data, something like this: employee 1, 2, 3 etc, every time I feed the spreadsheet, the balance goes automatically for everyone the officials quoted and if possible I add given in other columns, do you have any class reflecting this? Thank you

  63. Hi Flávio, you can use a conditional formula in an auxiliary column that looks something like SE (A1> Z1; A1; ””)

    In this scenario A1 would be the salary and Z1 its limiter. That way, the values ​​that do not match will be blank, so that the ranking is only with values ​​greater than your criteria

  64. If you used a filter in the “Employees / Sales” table, to hide, for example, values ​​above 8.000. The Ranking (I would shift down), would still consider hidden cells and would not change anything! The “SUBTOTAL” function would not solve, as it does not include the BIGGEST / MINOR functions in your list. Would I have a trick, because that's exactly my problem?

  65. Hi Thiago, for this you can use the function CONT.SE to count the number of times each option was selected. In the case of%, simply create a formula by dividing the number of choices by the total

  66. In my table the lines are the questions numbers of a questionnaire of alternatives from A to D and the columns are the names of the people who answered and underneath the name the alternative that each one answered. What I need to do is to know between each line, which alternative was the most chosen and, if possible, 100% of respondents, how many% chose the alternative. thankful

  67. In this case I would create an auxiliary column with the conditional function SE and repeating the sales value if the region is equal to the region selected for analysis.

    This way, if you choose the 2 region, only the 2 region values ​​appear in this column, and the larger function will only analyze the values ​​related to the 2 region

  68. HI…
    VERY GOOD The biggest function… but I'm not able to apply it when there is a need to do a logical test. I want to know, below, what is the biggest sale in sales region number "2". I tried to use the “SE” function with the “largest” but it was wrong…
    the original table has a thousand lines….

    REGION OF SALES SALES SALES

    1 JOÃO 400

    2 JOSE 340

    3 MARIO 3

    2 CESAR 3435

    2 JOEL 34200

    2 MARCELO 485

  69. Hi André,

    The way you said it is impossible to find any value because there are no numbers greater than 25 AND smaller than 15 at the same time. Can you explain a little better exactly what are the conditions for them to appear on the chart?

    Anyway, if you want to find two conditions at the same time you can use the IF function with E. It would be something like IF (E (A1> 15; A1 <25); "Correct"; "Wrong")

    If not, explain me better.

  70. Good afternoon
    I need help, I have a table
    with multiple values, I need to highlight values ​​that are larger than the 25 number and smaller than the 15 numeral and put in a chart.
    Thank you very much in advance
    Att

  71. Thank you for the help!
    I had a problem with some notes, but it was easy to solve manually.

  72. Hi Rafaela,

    in this case you have two options, the first is to put the values ​​in the cell next to the one that has the name and then using the filter in the cell with the notes, you would put them in descending order so that you would see the names in the order according to the numbers .

    Or, if you prefer, you can use a formula to extract only the numbers of those cells (probably the RIGHT one would solve), then create another one to analyze which one is the largest (MAJOR function) and even one more to sort (probably ORDER)

  73. Rafael, I do not know if you can help me, but I have the following situation:
    I have a list in excel with the result in a test, followed by the name of the candidate, all in the same cell only that this list is in alphabetical order and I need to organize it in order to know the classification of each one. They are 422 people and their notes, and I need to be in the correct order not only the numbers (which I could put in another cell), but especially the names, since I am looking to know what position each person has reached.
    Thank you very much in advance!

  74. It has like, but you would need to use a DESLOC for the found value or create an auxiliary column with the names of the employees and create a PROCV from the found value.

    An important care is with duplicate values

  75. In the example spreadsheet shown in the article (very good indeed) it is possible to check the higher and lower values ​​in their respective positions. For example, the first place, of higher value, is R $ 9.000,00. Second place, R $ 8.500,00… and so on. I have a question: how can I include the employee's name in the ranking? Using the same data as the spreadsheet of the article, instead of staying: 1º - R $ 9.000,00, it would look like this: 1º - Henrique - R $ 9.000,00. Has as?

  76. All good.

    Thank you for your prompt response.

    Let's assume that in column B and the 11 line I type the number 20. The 12 line is given the value 1, the 13 line is 2, the 14 line is 3, and so on, until the 20 total is completed. Of course it can not be limited to 20 and may even be smaller than 20.

    So, can I implement a formula in my spreadsheet so that it's done automatically, without my typing?

  77. Hi Roberto, how are you?

    I'm sorry, but I do not understand what you want to do, can you explain a little more?

  78. Hello Rafael!

    What function do I need to list in numerical order the values, for example, of the 20 number. I'm setting up a Sunday school game. I want to inform the number of children and excel list these numbers.

    Looking forward,

  79. Hi Rafael!! The version in which the spreadsheet was made is older and I am in a more current version. I pasted the data to another version and it worked. Thank you!!!!

  80. Rafael needs a formula for overtime calculations so: up to two hours is 75% and after two hours 100% would want the difference of the two would be thrown to another cell. thank you

  81. Hi Mila, how are you?

    I ran tests here up to 30.000 and it worked perfectly. My version of Excel is 2013, will see in older versions may have limitations. If it is not, perhaps between the 12.000 and 15.000 registers you may have some data entered wrongly resulting in the error.

  82. Hello! I need help, please :)!
    I have a table with more than 15 random number records and I need to sort them from largest to smallest. However, the = major function only accepts a data range of up to 12 thousand. If you put for example = greater (c9: c12000; lin (a1)) it will order in a good way. but if you put = bigger (c9: c15000; lin (a1)), you are giving an error… How do I do ???

  83. Hi Matheus,

    in this case you need to use the AND function together, like this:

    = SE (E (B38> B34; B38

  84. I need help! how to put the function “if” a cell is larger than one and smaller than another cell. Type like this: = SE (B34

  85. I need help! how to put the function “if” a cell is larger than one and smaller than another cell. Type like this: = SE (B34
    Excel is not answering me as I would like!

  86. I need help! how to put the function “if” a cell is larger than one and smaller than another cell. Type like this: = SE (B34

  87. Hi Anderson, I believe this post I wrote here helps you. https://blog.luz.vc/excel/variacoes-da-funcao-procv-no-excel-mais-de-uma-condicao-e-valores-repetidos/

    In that case I wanted a list of all the repeated values. In your case, I believe that just picking up the smaller one already solves.

    Perhaps what is missing is to turn the list of 2000 names into a list with unique values. To do this simply go to the Data tab and the Sort and Filter group choose the advanced option to copy the list to another place by picking only unique values

  88. Rafael, I need some help, please.

    I have a table of names that in column A contains 2 thousand lines. In these 2 thousand lines the names repeat at least 4 times. In column B I have destination addresses and in column C the cost for each address. I need to get a result that gives me, for each name, the lowest cost and the address. Can you help me? That is, the result has to appear only 1 each time, showing which address has the lowest cost. hug

  89. Hi Vanessa, you can do this using the MINOR function first and then using a PROCV to fetch the name of the company that is related to that lower value.

  90. Hello, I need to find the smallest one that appears, for example, the name of the company that has the lowest value type in values, select the company that has the lowest value and not the lowest value, does anyone know?

  91. Hello planners, it's a pleasure to chat with you.

    Creating RANK with the BIGGER and MINOR functions is easy enough, but for me at least, efficiency crashed into an obstacle when equal values ​​appeared in the table. Then I used another function called ORDER (= order (h7; h7: h48)) combined with the function CONT.SE. It was as follows: = ORDER (H7; $ H $ 7: $ H $ 48) + CONT.SE (H7: $ H $ 48; H7) -1, obeying the line locks so that the reasoning works as expected .

    the formula reading is: what is the order of the H7 value in the range H7 to H48 summed to the count of values ​​equal to H7 in the range of H7 to H48 minus 1. If the value of H7 is unique, it goes to the next rank position, if there are other values ​​equal to H7, the rank becomes the position of H7 minus the amount of equal values ​​minus 1.

  92. Hi Jefferson, I never had to mess with this kind of functionality and I would not be able to help you directly. I searched for some references by google and what I found closest to helping you was this code down here.

    If you have copied and pasted this code from somewhere and you are having difficulty with it, it would tell you to do the procedure by writing a macro, that is, copying the image using the camera functionality and then pasting the table image. This way you can access the module in VBE and analyze exactly how the code should be.

    Subtest ()

    ”'Set Range you want to export to file
    Dim rgExp As Range: Set rgExp = Sheets (“Plan1”). Range (“B5: H14 ″)

    ”'Copy range as picture onto Clipboard
    rgExp.CopyPicture Appearance: = xlScreen, Format: = xlBitmap

    ”'Create an empty chart with exact size of range copied
    With ActiveSheet.ChartObjects.Add (Left: = rgExp.Left, Top: = rgExp.Top, _
    Width: = rgExp.Width, Height: = rgExp.Height)
    .Name = “ChartVolumeMetricsDevEXPORT”
    .Activate
    End With

    ”'Paste into chart area, export to file, delete chart.
    ActiveChart.Paste
    ActiveSheet.ChartObjects (“ChartVolumeMetricsDevEXPORT”). Chart.Export “D: AdobetestmeExportChart.jpg”
    ActiveSheet.ChartObjects (“ChartVolumeMetricsDevEXPORT”). Delete

    End Sub

  93. Could someone help me mount these two macros? They are used to copy and paste a table as an image.
    Copy as image: selection.copypicture Appearence: = XLScreen, Format = XLBitmap
    Paste as picture: Activesheet.pictures.paste.select

  94. Hi Jefferson,

    Of course there are simpler ways to place the order from highest to lowest in a worksheet. The name of this feature is CLASSIFY and you can use it directly from the FILTER tool.

    To do this, you need to add filters within your table (in the DATA tab) and choose how to sort ascending or descending.

    The LARGEST and LOWER functions will be most useful in cases where you are interested in creating a new table or other ways of using it. If you just want to sort the data use the classification of filters that is much easier even. I hope I have helped, hugs

  95. My co-worker knows how to rank differently. Way better than this. But I can not do it his way. He knows how to leave in a way that goes from highest to lowest.

  96. Hi, Joshua, how are you?

    it is possible yes, but you gave me little information to complete the puzzle. Questions I need the answer to give you a solution.

    1 - What number is this? Where does it come from?
    2 - Does it already have a formula for you to get to it?
    3 - Why you need to separate into these groups
    4 - How many groups will you use to separate it?

    Anyway, I'll tell you what I thought of solution here at first:

    - if that number is in cell A2 for example, you could create functions in cells B2, C2 and so on until you reach the number you want

    - for that, you could create a conditional function, which would be something like:

    na B2 =SE(A2<=4;A2;4)
    na C2 =SE(A2<=4;0;SE(A2<=10;A2;10)

    and so on. The problem with this solution is that if you have variations that go up to 100 for example and you want to join them in 4 groups in 4, you will have a gigantic function going forward. In that case, we would need to think of another logic.

  97. for example in a precise cell that reaches no more than 4, if it is greater than 4, throw the rest into the next cell… is it possible to do this?

  98. Oi Wemenn, everything good?

    Looking straight at the Excel file would make it easier to do. Anyway, from what I can see from what you wrote, your formula has 3 problems:

    1 - the FALSE answer is missing, because from what I saw, you only put answers if the argument is true. Remembering that the SE function is like this, SE (argument, if_true, if_false). As you put it, the side of the 14 should have a semicolon and the answer SE_FALSO

    2 - There are some values ​​that are not included in your arguments (for example, between 3 and 21. If that is not a problem, then that's fine

    3 - the order of factors influences the result. I'm not 100% sure about this, but if I'm not mistaken, you would have to put> = 41 first, then 31 and then 21, otherwise he will always understand that 50 or 65 or any value greater than 21 is a correct answer to the argument> = 21 and will return answer 7

    I believe that from what I read, if you hit these items should have the correct and functional answer

  99. hello friend would like to know what the error in my table is because it only goes back to the first value = SE (D8 = 1; 1; SE (D8 = 2; 2; SE (D8 = 3; 35; SE (D8> = 21; 7 ; SE (D8> = 31; 10,5; SE (D8> = 41; 14))))))

  100. Hi Wellington,

    when you have repeated values ​​the BIGEST function will return the first repeated value that appears. You need to create some other logic if you want to analyze differently. For example, you can create a helper column that adds a small value (0,00001, 0,000002, etc) to your ranking numbers.

    As the value is minimal, it will not change your perception of the data and this is enough for Excel to differentiate one from another.

    I imagine you are using the larger function with another function. If it is PROCV, you can use the value TRUE at the end instead of FALSE. In the case of FALSE it returns you the first value that appears, in the case of the true, the latter if I am not mistaken.

    Another way is shown in this video, but for the ORDER function, I do not know if it will help you, but it's worth seeing: https://www.youtube.com/watch?v=MSrfXRcH4YM

  101. Hi Ismael,

    you could arrive at this result using PROCV, but for this you would need to make some changes in the first table.

    To start, I recommend reading this post of ours - https://blog.luz.vc/excel/como-usar-funcao-procv-vlookup-excel/

    With it you could reference the values ​​of the ranking table and from that value look for their names in the first table. As procv looks from left to right, you would either need to either create an auxiliary column by repeating the names of the first column or you would need to change the order of the columns, first showing the value and then the name.

    There is one more problem you can face. If you have equal values, PROCV can / will return a value not necessarily right for you, so in these cases you need to make sure that all values ​​are unique and do not repeat.

    If you have any other questions just let me know

  102. I have a question: How could I include the name related to the value in the ranking? That is, how would you do that in three columns on the same line appear the placement, the value and the name? In the example described above it would be: 1º; R $ 9000,00; Henrique.

LEAVE AN ANSWER

Please, write your comment
Please enter your name