Home Excel Know the Excel Order function

Know the Excel Order function

32
34719
Know the Excel Order function

The famous Excel functions are intended to make users' laborious tasks easier. In this article, we will introduce the Order function, a tool that has the function of calculating the position of a certain value within a list. To get to know it, follow more details below.

As a practical example for better compression, we have in the image below a spreadsheet containing a list of runners' names in an athletics event and with the classification of runners in a given event:

Know the Excel Order function

Note that the classification is not yet shown, and in this case we need to know the order of arrival of each athlete without having to calculate their respective race times. In this case we will use the Order function according to its syntax:

= ORDER (no. Ref; [order])

- Text after advertising -

Where:

On one: It represents the cell that we want to know its position in comparison with the other values. In the case of the above example, we will point to the cell B4 that contains the arrival time of the Mark corridor.

Ref: Represents the range of cells also known as array, in which are contained the values ​​we want to compare with the first value and get the order of this. In the example above, we select the B4: B10 array.

Order: In this case if we enter the value zero (0), the value of the position of the number in the list will be returned decreasingly. If the value you entered is one (1), the value of the position of the number is increased.

In the example below, we use the formula so that the value is displayed incrementally.

Know the Excel Order function

At the end of the function, press Enter and check the result.

Know the Excel Order function

Notice in the image above that the function returned a value from the finish position of the runner Mark. That is, based on the arrival time of all the corridors contained in the time matrix, the function brought the ordered value incrementally.

To replicate the function for the other athletes, it is necessary to lock the cells referring to the time interval. In this case we will use the dollar sign ($) as shown in the image below so that there are no errors during the copy of the formula.

Know the Excel Order function

To replicate the formula for the other athletes, simply drag the first cell to the others using the fill handle:

Know the Excel Order function

After replicating to the other cells, see that the positions were assigned to the other athletes according to their respective orders of arrival:

Know the Excel Order function

For better visualization and understanding, we can also use the "Sort and Filter" tool located on the "Home Page" tab within the Editing tools group. So, just select any cell with values ​​in the table and choose the sort order to form increasing or decreasing as shown below:

Know the Excel Order function

When executing this operation, note that the table will be sorted according to the order of arrival of each athlete.

Know the Excel Order function

As we saw in the example above, the Order function in Excel brings great benefits to users when they need to get the position of certain values ​​in a list. Still in the example above, we quote the classification in an athletics test, however this function can be applied whenever there is need to obtain such results.

- Text after advertising -

32 COMMENTS

  1. Good afternoon, Rafael.

    Would there be any formula for him to automatically sort after placing this formula above ??? That is, if it is 4,3,7,5,6,1,2. When I go typing the results it will already sort from 1 to 7

  2. I have a spreadsheet where it is necessary to divide it into 3 cities, I have two spreadsheets one with the contents and the other with the summaries (the cities are mixed). And to get the results of each city I need a formula to count how many "Yes" and how many "No" you have in each city separately. Could you help please?

  3. Very useful, I have a question:
    I have a spreadsheet to do the ranking, it happens that these are products and some of them have a value of R $ 0,00 or the blank cell “automated classification”, it happens that in the ranking the values ​​with zero value end up being 1, 2, 3rd …… .place. Has the ranking not considered these values ​​0,00 or empty?

  4. Oi Flávia, if you only have one priority type per item (no repetitions), just use the GREATER function to differentiate the order. If you have items with the same type of priority, then you will need to differentiate between the items on the list - in this case, you can add a very small number (eg 0,000001 and then 0,000002, etc.) in each item sequentially to generate differentiations

  5. Rafael, I need to make a spreadsheet that allocates a particular service in a "schedule of available hours" according to priority.
    Ex .: Item 1 - Priority 1 - Duration: 02hs - Start: 06:00 - End: 08:00
    Item 2 - Priority 2 - Duration: 03hs - Start: 08:00 - End: 11:00
    If I change the priority, I need it to change the allocation.
    Ex .: Item 2 - Priority 1 - Duration: 03hs - Start: 06:00 - End: 09:00
    Item 1 - Priority 2 - Duration: 02hs - Start: 09:00 - End: 11:00

    Would you help me?

  6. Hi Alexander, how are you? I usually use an auxiliary column to add a very small value to the original column values ​​to create the differentiation between the listed values

  7. I wrote his name wrong, sorry, where is Gustavo, I say Rafael.

  8. Hello Gustavo, Good morning,

    I'm trying to use this formula to sort a dataset, but my list has repeated data, so the order is coming with some sort problem. For example, my column to be sorted looks like this: 1, 2, 3, 2, 3, 4, 7, 9, 12, XNUMX, etc.

    When I use the OrderEq function (searched number; reference matrix; criterion) I have a result that does not match the correct order, probably because of the repeated data.

    Could you tell me what I can do to heal this problem?

    Hug.

  9. Hi Italo, it was clear yes, but I do not know a way to change the formula based on filters. What you can do is create an SE for, when the cell next to it is blank, use the upper one in the subtraction

  10. Rafael,

    Good morning!
    I came across your site through google, I'm having a difficulty on a spreadsheet and would like your help.
    I have a database with time to stop various equipment, I have numeric values ​​in column G, one of the information I need is simple subtraction (example = (G10-G9)), however when filtering the worksheet by equipment, it can occur of the 9 line is not included in the filter, that's where my problem is, this subtraction would have to be changed to (example = (g10-g (line immediately above G10)).

  11. In this case I usually create an auxiliary column with very small sequential numbers (0,00001; 0,00002, etc) and another column with the sum of the note with that small number. This creates different and unique values, which allows the use of PROCV and other formulas without the risk of having equal values

  12. I wanted to know the answer to Gustavo Baclini Hannouche's question… could anyone answer this big question?

  13. You can have the start position and end position (first and second update) and subtract

  14. But how do you figure out how many positions a runner went up or down in an update?

  15. Hi Beatriz, it depends on how you will categorize these products, normally I use VLOOKUP with an auxiliary column using the GREATER function - so I look for the first, second, third, etc. bigger in an easier way

  16. Good afternoon, Rafael!

    I have a huge spreadsheet of products (> 5000 lines) and I needed to make a table that showed me only the top 10 in the ranking.
    Is PROCV the easiest way or is there any other function or way to do this?

    Thank you, great content.

  17. Hi Gustavo, I usually create a helper column with very small values ​​(type 0,0000001, 0,00000002 and so on) and then I add these values ​​to each of the lines, making the larger value appear, but let them differ when they are equal (this will cause the PROCV, ORDER function, etc. do not understand the values ​​as equal)

  18. Thank you very much Rafa, for sharing your knowledge
    1 - I have a spreadsheet with survey of demands and scoring their relevance (here I use the order function to create the position of the demand according to its criticality - GUT)
    2 - In another spreadsheet, I classify this relevance - here I make a ranking, and through a VLOOKUP, I search the demand spreadsheet for the top 3 positions
    The problem to generate the ranking, is when in the worksheet (1) I have 2 items with the same score, occupying for example the first place.
    Procv will bring me the 1 position item, however, two demands occupy that position.

    How can I solve?

    Thank you very much

    a hug

  19. Hi Simone, without seeing the structure of your spreadsheet it is complicated to say what may be occurring.

  20. I applied the right formula, and it's bringing ZERO in the result for everything. What could be happening?

  21. Very good site, congratulations

    Questioner:
    What is the best way to aim MM: SS: dd - minutes, seconds and tenths of seconds?

  22. Thanks for the comment Danilo. If you have any other questions in Excel, just let us know or send us to our forum (forum.luz.vc).

  23. Very explanatory and useful explanation. It was extremely useful to settle a lawsuit that was taking me a long time.
    Thank you!

LEAVE AN ANSWER

Please, write your comment
Please enter your name