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:
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])
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.
At the end of the function, press Enter and check the result.
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.
To replicate the formula for the other athletes, simply drag the first cell to the others using the fill handle:
After replicating to the other cells, see that the positions were assigned to the other athletes according to their respective orders of arrival:
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:
When executing this operation, note that the table will be sorted according to the order of arrival of each athlete.
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.
Thank you so much! Helped me a lot.
Hi Bruno,
It's OK? For questions regarding the use of Excel, I suggest you visit our expert forum at: https://forum.luz.vc
Hugs,
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
Hi FD, how are you? The best place to answer Excel questions is in our forum: https://forum.luz.vc
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?
Hi Sydney, how are you? The best place to answer your excel questions is in our forum at: https://forum.luz.vc
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?
How nice! We are happy to help
THANK YOU, HELPED MUCH !!
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
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?
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
I wrote his name wrong, sorry, where is Gustavo, I say Rafael.
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.
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
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)).
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
I wanted to know the answer to Gustavo Baclini Hannouche's question… could anyone answer this big question?
You're welcome!
Many thanks, thanks
You can have the start position and end position (first and second update) and subtract
But how do you figure out how many positions a runner went up or down in an update?
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
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.
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)
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
Hi Simone, without seeing the structure of your spreadsheet it is complicated to say what may be occurring.
I applied the right formula, and it's bringing ZERO in the result for everything. What could be happening?
If I am not mistaken you need to use the formatting hh: mm: ss, 0
Very good site, congratulations
Questioner:
What is the best way to aim MM: SS: dd - minutes, seconds and tenths of seconds?
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).
Very explanatory and useful explanation. It was extremely useful to settle a lawsuit that was taking me a long time.
Thank you!