esCalc stores data in hierarchical, structured spreadsheet, which enables the use of structure-related functions in performing data analysis and aggregation. Here let’s talk about these functions and their basic usages.
1. The sequence of cell values
Through esCalc Basics: Sequences, you know that [a:b] can be used in a formula to calculate a sequence composed of values of a stretch of cells from a to b. For example:
In this hierarchy-free cellset, both D6 and E6 calculate sequences made up of values of cells from B3 to D5 using the same formula – =[B3:D5]. They get the same result – [30,15,1.91,35,18,1.93,47,27,2.46] after the formulas are entered. This means when getting a sequence of cell values with [a:b], the values of rows will come first.
If the calculation is performed in a cellset with hierarchy, for example, the following spreadsheet containing climate data of a place:
B10 does the calculation with the same formula =[B3:D5] and gets the same result. But since the data may possess different types of meaning in a hierarchical cellset, such a result as [30,15,1.91,35,18,1.93,47,27,2.46] containing both the lowest temperatures and the precipitation amounts is not the desired one.
Only data of the same type of meaning can be used for summarizing. For instance, C10 tries to use =[B3:B9] to get all the highest temperatures but the result is [30,35,47,”High °F”,60,71,80]. This is because the formula uses a stretch of “continuous cells” containing the header information in B6. To avoid the involvement of B6, you should not use a formula containing a stretch of cells; instead, you have to use =[B3:B5,B7:B9], the formula in D10.
In an esCalc cellset, “data of the same type of meaning” is stored in homo-cells. Thus an alternative is to use {a:b} in the formula. This is different since {a:b} indicates a sequence consisting of values of the homo-cells in a stretch of continuous cells from a to b, as the following case shows:
This way A10’s result is [“Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”], a sequence consisting of the months in the cellset. B10’s result is [30,35,47,60,71,80], C10’s is [15,18,27,37,46,56] and D10’s is [1.91,1.93,2.46,3.55,4.07,4.18]. They are sequences consisting respectively of the highest temperatures, the lowest temperatures and the precipitation amounts in every month. The result of E10 is the same as that of B10, since in a certain area only values of B3 and its homo-cells will be selected to form the sequence. Please see esCalc structure: Homologous Relationship for details about homo-cells.
2. Row numbers and homo-cell sequence numbers
In esCalc, you can use row() function to get the number of the row in which a cell resides. In the preceding cellset, for example, you select cells from A10 to E10 at the same time and remove their cell values using the delete button and then use Alt+delete to delete their formulas. In both E2 and E3, you enter the formula =row(), which will be automatically copied to their homo-cells. Here are the results:
As can be seen from the results, the row() function returns the number of the row where a cell is located. The result is irrelevant with the level to which the cell belongs.
It is seldom in real data analysis and calculations to use row numbers, though sometimes they are used to modify the appearance properties. Select A3, B3, C3, D3 and E3 simultaneously and set the Background property in the right-hand property section through the formula if(row()%2==0,-3342337,-3355393), which means setting different background colors for these cells based on whether their row numbers can be divisible by 2. A color is represented by an integer.
The setting will change the background colors of these cells, as well as their homo-cells:
But in many cases, people really concern the position of the data item among data of the “same information type”. This position is known as the homo-cell sequence number, which can be represented in a formula simply by the sign #. Still you can modify the original structured cellset by typing the formula =# into F2 and F3. By doing so, you also set the same formula for their homo-cells. Here is the result you’ll get:
The use of # gets the homo-cell sequence number of the current row in the band where its parent row resides. For instance, the third row contains the climate data of a month. The second row is its master row whose band, including the second to the fifth row, contains the climate data of the first quarter. The result of F3 tells that the third row holds the data of the first month in this quarter. Similarly, the result of F9 means the row falls in the third month of the second quarter. Both F2 and F6 get the homo-cell sequence numbers for the quarterly data. As can be seen from their results, the second row is data of the first quarter and the sixth row is data of the second quarter.
You can also find that the homo-cell sequence number is directly relevant to the cell whose homo-cell sequence number you want to know and to the band where the cell resides. To specify a band, use the ord(A,L) function for calculating homo-cell sequence number in its complete form. This function returns the homo-cell sequence number of A across level L, in which L merely means the data level.
A modification of the preceding esCalc cellset will help you better understand the function. Suppose you clear cells in column E and column F of the formulas and values, and then set the formula =ord(A3, A2) for E3 and =ord(A3,A1) for F3. Thus you would get the following result:
In an esCalc spreadsheet, as soon as a formula is typed into a cell, it will be intelligently copied into the cell’s homo-cells according to the target of the calculation. For instance, you set E3’s formula as =ord(A3,A2) in which A3 is of the same row in column A and A2 is of the parent row in column A. The formula is copied to E5 as =ord(A5,A2) in which A5 is of the same row and A2 is of the parent row, and to E8 as =ord(A8,A6) in which A8 is in the same row as E8 and A6 is still in the parent row of E8. That is the case with F3 for which you set the formula =ord(A3,A1) in which A3 is of the same row in column A and A1 is of its parent row’s parent row. It copies itself to F9, one of its homo-cells, as =ord(A9,A1) in which, still, A9 is of the same row and A1 is in its parent row’s parent row. The smart copying of formulas in esCalc spreadsheet is covered in esClac Structure: The Smart Coping of Formulas.
Now let’s look at the calculation process of the formula =ord(A5,A2) in E5. As the row where A2 resides belongs to level 1, the first thing to calculate is to find the master row of the band of level 1 where cell A5 resides, that is the second row. The band includes the second row to the fifth row that holds A5 whose homo-cells within the band are A3 and A4. Thus the homo-cell sequence number of A5 is 3 and E5’s result is 3, meaning A5 contains data of the third month in this quarter. As for E8’s formula =ord(A8,A6), A6 is a cell of level 1 and the master row of the band of level 1 where A8 resides is the sixth row. The band includes rows from the sixth to the ninth, in which A8 has A7 and A9 as homo-cells. The sequence number of A8 among these homo-cells is 2 and thus E8’s result is 2, indicating that A8 contains data of the second month in this quarter.
According to F9’s formula =ord(A9,A1), you can find that A1 belongs to level 0 and the first row is the master row of the band of level 0 where A9 belongs to and that includes all the rows from the first down to the tenth. Within this band, homo-cells are A3, A4, A5, A7, A8, and A9 whose sequence number is 6. So F9’s result is 6, which means A9 holds data of the sixth month among all the monthly data.
Parameter A in ord(A,L) function must not be omitted. If there is no parameter L, the function will calculate according to the level to which the current cell’s parent row belongs. For instance, E3’s formula =ord(A3,A2) is equivalent to =ord(A3).
You should note that L isn’t necessarily within the band which A belongs to. The function calculates only according to L’s level. Suppose you change F3’s formula to =ord(A3,A10), you get the same result.
In addition to the ord function for calculating the homo-cell sequence number, esCalc offers num(A,L) function to calculate the number of homo-cells of level L where A belongs to. The L only represents the data level. This function is similar to ord function in every aspect except that it returns the number of the homo-cells instead of their sequence numbers. Like the use of #, you can use ## in a formula to represent the number of homo-cells in the specified cell’s parent level.
Based on the preceding example, you can set formula =num(A3,A2) for E3, =num(A3,A10) for F3, =num(A2) for E2 and =## for F2. Here is the result you’ll get:
num calculates in the same way as ord does. Take E8’s formula =num(A8,A6) as an example. A6 is a level-1 cell. The level-1 band where A8 resides has the sixth row as its master row and includes rows from the sixth to the ninth. Within this band there are 3 homo-cells – A7, A8 and A9. Thus E8’s result is 3, indicating the quarter to which A8 belongs has three month’s data.
When the L is omitted from num(A,L) function, it is by default the level of the parent row of the row where the current cell stays, such as E2’s case. Both E2 and E6 get the same result of calculating the number of homo-cells. F2’s formula, which uses ##, also calculates the number of homo-cells within the band where the current row’s parent row resides and gets the same result as E2.
You could use # and ## to specify a level, in order to get the sequence numbers of the homo-rows or the number of homo-rows under this specified level. The syntax is L# or L##. Based on the above cellset, for instance, you modify E2’s formula to =A1#, F2’s formula to =A1##, E3’s formula to =A1# and F3’s formula to =A1##, so that you can get the homo-cell sequence numbers and the number of homo-cells across the level-0 band, or the whole cellset. Here is the result you’ll get:
3. The set of homo-cells
In esCalc, not only can you calculate the homo-cell sequence numbers and the number of homo-cells based on the specified cell, but you can use L{A} to get a sequence comprising all the homo-cells of band of level L where cell A resides. This sequence is the set of homo-cells.
In the following cellset:
Once the formulas are entered, they will be intelligently copied to homo-cells of B2, C2, B3 and C3. You’ll get the following results:
Let’s take an examination on these results. For instance, C3, whose formula is =A1{A3}. As the row where A1 resides is level 0 and the level 0 band is the whole cellset, C3 will get all the homo-cells of A3’s level from the cellset – A3,A4,A5,A7,A8 and A9 – to form a sequence of their values. In C2’s formula =A10{A2}, the level-0 band where A10 settles also includes the whole cellset in which the homo-cells of A2’s level are A2 and A6. So C2’s result is [“Quarter 1″,”Quarter 2”]. By observing the results of B10 and C10, you may find that, different from the L in ord function and num function, the L in L{A} designates both the data level and the band to which the data belongs. That’s why B10 and C10 get different results – one is the months of the first quarter and the other is those of the second quarter. When L is omitted from L{A} for getting a set of homo-cells, the function finds homo-cells from the band where the current row’s parent row settles. If you change B2’s formula to ={A2} and C2’s formula to ={A3}, their results remain unchanged.
The set of homo-cells enables the use of aggregate functions that are based on sequences in handling data analysis and calculations. According to the above-mentioned table of climate data, for instance, you can calculate the highest and lowest temperature for every quarter. To do that, enter ={B3}.max() in E2 and ={C3}.min() in F2. Or you can calculate the average precipitation over the two quarter by typing =round({D3}.avg(),2) in D10. Here is the result you’ll get:
Just as expected, you get the highest and lowest temperature in each quarter and the average precipitation during the two quarters in D10.
Moreover, esClac gives you the chance of using L[A;x] function to get the value of a certain homo-cell from the set of homo-cells according to x, the offset value of sequence numbers; or using L{A;a:b} function to get a subset of the set of homo-cells according to an sequence number offset counted from a to b. For instance:
The formula =A2[B3:-1] in C3 stands for the value of B3’s previous homo-cell in the band with A2 being the master row. A1{B3;-2:0} in D3’s formula gets the temperatures starting from the month before the previous one to date. The results of C3 and D3 are as follows:
In this cellset, C3 and its homo-cells calculate the lowest temperature of the previous month during the current quarter; D3 and its homo-cells calculate the lowest temperature during the recent three months.
4. Page setup and post-pagination calculations
To output and print out an esCalc file, click on File>Print on the menu bar, or press the shortcut key Ctrl+P, and the print preview interface will pop up. esCalc will divide the file into discrete pages while printing.
Click on File>Page setup on the menu bar to get a window as follows:
On the Page tab, you can set the paper, the margin, the print zoom and the alignment. For the paper setting, you can select a paper type, or define the width and height (measured by milliseconds) of the paper by selecting custom paper; then you can select a print direction: vertical or horizontal.
On the Page break tab, you can choose the header and footer rows/columns for repetition after pagination. The header rows should be the continuous level-0 rows starting from the first row and the footer rows should be the continuous level-0 rows starting from the last row.
esCalc can automatically calculate the number of rows and columns a page can hold during the pagination according to the page setup. On each page, the rows and columns set for the header area will repeat. The vertical direction comes before the horizontal one during pagination.
Besides the automatic pagination by the paper setting, you can also perform the after-row or after-column pagination. Right-click on the first cell of a row or a column and choose Property on the context menu. Then on the pop-up row property or column property tab, check Insert page-break after row/column to edit row and column property:
esClac provides several functions for performing calculations after the pagination. These include pgno() for getting the current page number, pgall() for getting the total number of pages, and pgcell(C) for getting the set of homo-cells based on C of the current page before further calculations. esCalc Basics: Formulas explains that a calculation cell calculates automatically as soon as a formula is entered and after that it will not calculate the second time. But this may bring about incorrect results when applied to these functions for performing post-pagination calculations. Therefore, their use should be restricted in related calculation cells in which the formulas start with two equal signs ==.