Functions EARLIER and EARLIEST

Formula in calculated column will be evaluated using the row context, which means that all columns that are referenced give results in a row in which the formula is located. Evaluation continues for each subsequent row, so that by the end of the table. Since calculating final sum takes place iteratively we can hardly see what happened in the previous row as you would do in traditional Excel formulas. Here come into play EARLIER and EARLIEST functions.

EARLIER function is most often used when we want to compare the value calculated in the current row with those of the previous rows. Its syntax is:

EARLIER (<column>,[<number>])

It is mandatory to specify the column which the budget relates, while the number is optional argument and represents the next level of the formula evaluation. The default value is 1 (first look at the previous calculated row) and it can be omitted.

To understand what is being done I’ll have to make use of an example. Imagine you need to rank products at a price, which would get the cheapest rank 1 and the most expensive rank n (n represents the total number of products).

First, let’s go to the table that holds data about items (Artikli), and let’s sort them by price (PC) in ascending order. Then enter the formula:

=COUNTROWS(FILTER(Artikli,EARLIER([PC])>[PC]))+1

The first part of the formula counts how much rows, in the preceding context, have greater selling price than one in current row. By this operation we calculate rank of prices which is represented by numbers from 0 to n, and since we want that rank starts with number 1 an the end of the formula we will add number one to calculated expression. When we enter the formula we will change the name of calculated column to RANG.

EARLIEST is the function used when we want to evaluate formula according to the first calculated row, and its syntax is:

EARLIEST (<column>)

We seldom use these two functions, but it’s good to know about their existence when we need to compare rows within a table.