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])
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.