If you enter a formula into a cell in an esCalc spreadsheet, it will automatically appear in each of the cell’s homo-cells and intelligently adapt itself to the new settlement. If you set one of the cell’s properties through a formula, all its homo-cells will receive the property setting simultaneously. Here we’ll discuss the esCalc rules and principles of the intelligent formula copying.
1. The basic smart-copy
In an esCalc cellset, the copying of a formula involves not simply the change of its position relative to positions of rows and columns, but an examination of the cellset’s structure and an analysis on the cells referenced by the formula. This is the smart-copy. Below is a climate data table:
Enter formulas in the third row. E3’s formula calculates which month the current month is through getting the sequence number of the current row among its homo-rows in A1’s level. F3’s calculates the current month’s average temperature difference by subtracting the lowest average temperature from the highest average temperature. Here’s the result you’ll get:
So no copy and paste operations are needed in esCalc. You just enter the formula in a cell, and all its homo-cells will perform the calculation at the same time.
If clicking on these cells that have performed the calculation, you can view their formulas in the right-hand property list. You may notice that the formulas in E3, E4, E5, E7, E8 and E9 have remain unchanged during the copying and been the same =A1#. That’s because there are no homo-cells on level-0, and cell A1 of level-0 referenced by these formulas has no reason to change. On the other hand, since each row has a unique sequence number among its homo-rows across the level-0 band, the same formula gets different results for different rows.
But the cells in column F – F3, F4, F5, F7, F8, and F9 – hold different formulas each. For instance, F4’s formula is =B4-C4 and F9’s is =B9-C9. You may find that the formula for each month references the highest temperature and lowest temperature of the same month. This implies that if a formula in a cell references cells of the same row, it will be intelligently copied to the cell’s homo-cells where it references cells from the current row.
When selecting E3 and F3 simultaneously, use the shortcut key Alt+delete to remove the formulas from all the homo-cells and Alt+Shift+delete to clear all the homo-cells of their values. Now let’s modify the esCalc cellset as follows:
E2 calculates the average precipitation amount in the first quarter. E4 calculates the difference between the precipitation amount in February and the quarterly average precipitation amount. F4 calculates the increment of precipitation in February compared with January’s precipitation amount. Enter in their formulas and you’ll get the following results:
First let’s look at E6’s formula, which is =round({D7}.avg(),2). D3, the sub-cell, in E2’s formula has changed to D7, the sub-cell corresponding to the current quarter. So E6’s result is the average precipitation amount in the second quarter.
Then let’s examine E4’s homo-cells, where E4’s formula =D4-E2 has been intelligently copied. For instance, E3’s formula is =D3-E2 and E8’s is =D8-E6, in both of which D4 is replaced by cells of the same row – D3 and D8 respectively. E2 referenced by E4’s formula contains the “average precipitation amount in the current quarter”, which can be interpreted as “homo-cell of E2 in E4’s parent row” from the esCalc’s hierarchical perspective. So the same E2 is in E3’s formula, and is changed to E6 – its homo-cell – in E8’s formula, but without a change in meaning. So esCalc supports the kind of formula copying during which a cell referenced by a formula will intelligently change to its homo-cells specified through a hierarchical system.
Let’s move on to make some observations on F4’s homo-cells. F4’s formula =D4-D3 calculates the difference between the precipitation amounts of two months by subtracting the precipitation amount in the previous homo-cell – D3 – from that in the current month – D4. The formula copying sticks to this rule too. For instance, F5’s formula is =D5-D4, F7’s is =D7-D5, and F9’s is =D9-D8, they all calculate by subtracting the precipitation amount in the previous homo-cell from that of the current one. In particular, as can be found from F7’s formula, the copying is not performed simply according to the displacement of the rows and columns. A check of F3’s formula, which is =D3-#REF!, indicates that the “precipitation amount of the previous month” hasn’t been found. In this case, the value of the missing cell is a null. The absence of the data of the previous month explains why the formula for calculating monthly data is entered in the fourth row. According to the article esCalc Structure: Functions, you can reference data of the previous homo-cell using the offset value based on a set of homo-cells in L[A;x] function . Thus F4’s formula can be =D4-A1[D4;-1], which has the same result as above. But the way of directly using cell values is more convenient and intuitive.
2. The smart-copy across bands
Here is a population census table where the smart copying of formulas is carried out:
C7 calculates the population increment of a state by subtracting the previous population data from the current one. D7 calculates the population growth rate and, in the right-hand property section its display format (Format) is set as #.0%. Here is the result:
In this example, the calculations involve data in cells from different bands. Such as C9’s formula =B9-B5, D9’s =round((B9-B5)/B5,3), C12’s =B12-B8 and D12’s =round((B12-B8)/B8,3). They are all correct thanks to the smart-copy of formulas.
Here one point worth noting: During the smart-copy, the cells referenced by a formula are not controlled by the bands specified by their parent levels, but determined merely according to the offset value between their homo-cell sequence numbers. In C7’s formula =B7-B3, for instance, the seventh row’s homo-cell sequence number is 4 and the third row’s is 1. Thus the calculation is getting the difference between the current population data and the third one before it.
The smart copying of formulas works well for a data table where every band has the same number of rows, like this example in which every population census involves three states. However, in a table where bands have different numbers of rows each, this way of smart-copy may lead to incorrect results. The following table, for instance, has the data of the state of Arkansas in population censuses in both 2000 and 2010. Enter the formula =B7-B3 in C7 and, for comparison, the formula =B7-A2[B3;0], which uses the offset value based on the set of homo-cells, in D7. Then you’ll get results as follows:
Through an examination, you may find that the formulas after C10 (inclusive) are all wrong, like the formulas =B10-B7 and =B13-B9 respectively in C10 and C13. The extra Arkansas data in the latest two population censuses is the cause of the mistakes during the smart formula copying according to homo-cell sequence numbers.
In contrast, the formulas in column D get the correct results. In D7’s formula =B7-A2[B3;0], A2[B3;0] stands for one of B3’s homo-cells whose sequence number in the previous level-1 band where A2 is the master row is the same as the current cell’s homo-cell sequence number in its band. Thus the formula would make correct smart-copies, such as =B10-A2[B7;0] in D10 and =B13-A6[B9;0] in D13,which have correct results. So, try to detect the potential errors in the cross-band smart formula copying.
3. Specifying a cell by level
Below is another population census table whose structure is different from the above one, as its data is stored by states:
C4 calculates the population increment and D4 calculates the population growth rate with its display format (Format) being set as #.0%. Here is the result you’ll get:
As these rows – 3, 4, 5, 7, 8, 9, 11, 12 and 13 – are homo-rows, the formulas typed in C4 and D4 will be automatically appeared and intelligently adjusted in their homo-cells. So C13’s formula is =B13-B12 and D13’s is =round((B13-B12)/B12,3); and formulas in C11 and D11 are respectively =B11-B9 and =round((B11-B9)/B9,3); and those in C7 and D7 are respectively =B7-B5 and =round((B7-B5)/B5,3).
Look at C7’s formula =B7-B5 that calculates the population increment of the state of Alaska in 1990. The formula automatically calculated the difference between the current population data and the “previous population data”, which is the value in the previous homo-cell B5, during the formula copying, ignoring the fact that B5’s data does not belong to Alaska. It is incorrect because the calculation is the comparison between Alaska in 1990 and Alabama in 2010.
In view of this, you can specify a cell by the level besides specifying it through an offset value based on a set of homo-cells, which is mentioned in the preceding section. Use A[L] in an esCalc formula to represent a cell A in the level-L band where the current cell stays. The L merely represents the level. Since referencing a cell by a formula using only its name A but without specifying its level will lead to the search of its homo-cells across the whole cellset, the modification of C4’s formula into =B4-B3[A2] will restrict the search of B3 within A2’s band, or the current state data. If the searching target is beyond the limit, the formula will return a null value. Similarly you can change D4’s formula to =round((B4-B3[A2])/B3[A2],3). Now you’ll get the following result:
By specifying the level where the target cell belongs to, only the data within the specified band is valid. This ensures that only data of the current state will participate in the calculation and prevents errors from happening during the formula copying.