Structure

Formula copying

Records are represented by the rows in an Excel worksheet. Users can perform operations such as filtering, sorting on the rows, and, particularly, add computed columns (its values are computed from other fields) for the rows. It’s in this latter case where the formula copying becomes a problem.

To add a computed column involves all records (rows), but as Excel hasn’t the concept of explicit record, the formula entered in a certain row needs to be manually copied to other rows. Excel cleverly adopts the drag-and-drop method to do this. The method is very convenient-to-use for handling single-row records (that is, each record corresponds to a single row).

But at times the worksheet data we’ are handling are complicated in that one record corresponds more than one row. That’s because, for example, the record has much content that needs to take up two rows, or the record includes the lower-level sub-records (the details of an order, for instance). In those cases, the cells to which the formula is copied aren’t continuous any more, and the drag-and-drop method becomes powerless. We can imagine how much hassle there will be if all the copying is done manually row by row.

esClac solves the problem by both retaining Excel’s intuitive way of naming data items after cells and by introducing the concept of explicit records. It combines the strongest points of Excel and database client software. A formula entered to a certain cell will be automatically and correctly copied to its homo-cells (cells of the desired field in other records) without specialized copying actions, even if there are multi-row records and records with sub-records.

Here’s an order table:

esCalc_homeintro_structure_1

F1 calculates the total order amount using the formula ={E2}.sum(). We then enter the formula =round(E2/F1,4) in F2 to calculate the percentage of the amount of the current order in the total amount, that is – dividing the total value in F1 by the amount of the current order. At the same time, we set the display format of F2 as #0.00%, which means representing the value in percentage. After entering the formula in F2, here’s what we get:

esCalc_homeintro_structure_2

Check the homo-cells (F3~F11) of F2 and we find that they’ve all finished the computations. This shows that esCalc can copy the formula and display format in one cell to its homo-cells automatically and correctly.

It can also copy the formula in handling multi-row records as conveniently as in handling the single-row records, for example:

esCalc_homeintro_structure_3

The worksheet contains unit prices and quantities of vegetables and fruits purchased. D3 calculates purchasing amount of the pineapple with =floor(D2*B3,2). Here’s the result after the formula is entered:

esCalc_homeintro_structure_4

As soon as the formula is entered, it is copied to the cells corresponding to all products, i.e. D3’s homo-cells, to calculate their total purchasing amount.

Data editing

Excel is again at a disadvantage in editing multi-row records.

Excel doesn’t handle a record as a whole. Inserting, deleting and moving a record are operations performed based on rows and columns of a worksheet. There’s almost no problem about processing single-row records. But operations on rows become complicated in handling multi-row records and records with sub-records, and inserting and deleting fields based on columns are almost non-executable.

Because Excel isn’t good at handling multi-row records, it generally prevents them from appearing when generating the original data. So there’re not many chances for Excel users to encounter them. In many real-world businesses, however, it’s not a rarity that users find themselves in the face of a multi-level worksheet or multi-level data items. By the way, group operations will generate multi-level tables, as we’ll mention later.

Even with single-row records, Excel will still make mistakes in copying formulas for inter-row calculations (such as the calculation of YOY rate and the accumulated value) when rows are inserted or deleted. There’s the same problem in moving records through the copy. Both cases require modifying the results manually or recopying by drag-and-drop. In addition, since Excel doesn’t stress the concept of records, it doesn’t offer hot keys for record processing, making the modification and recopying not that easy.

But it’s easy for esCalc, which defines records, to perform those operations. It also provides convenient hot keys to trigger the actions in a shortcut way. Records (including their sub-records) can be deleted and moved as a whole with just one click, after which the inter-row calculation formulas will remain correct. To insert and delete fields based on columns is to change the data structure. esCalc will automatically copy these operations on one cell to all its homo-cells.

Here’s the employee table:

esCalc_homeintro_structure_5

There’s the formula =age(C3) in D3. The formula, as well as those in its homo-cells, is used to calculate the age of each employee. Meanwhile C2’s formula ={B3}.count() calculates the number of employees in each department, and D2’s formula =round({D3}.avg(),1) calculates the average age of the employees in each department. Suppose we want to delete duplicate department values in the first field of the employee table without affecting other data items. To do this we select B3 and press Ctrl+Backspace to delete A3 and its homo-cells. Here’s what we get:

esCalc_homeintro_structure_6

All homo-rows will change their structure at the same time. In the meantime formulas in C3 and its homo-cells will adapt themselves intelligently to the new structure. For instance, C3’s formula becomes =age(B3) automatically.

In esCalc we can merely change the structure of summary rows. For instance, to delete blank cells in the second column of the department summary rows, we select C2 and press Ctrl+Backspace to delete A2 and its homo-cells. Here’s what we get:

esCalc_homeintro_structure_7

This is a membership management table:

esCalc_homeintro_structure_8

The worksheet table records the number of new members and of those who leave each month. Enter ==D7+B8-C8 in D8 to calculate the number of members in the current month according to the number in the last month and the number of withdrawals in this month. Here a related calculation expression starting with two equal signs is used and data in corresponding cells will adjust intelligently according to any change of the table.

Now we insert the records of the missing months April and May in the table and enter data to them. Here’s the complete table:

esCalc_homeintro_structure_9

Because esCalc stores the inserted data also in the form of homo-rows, calculations in column D will still be correctly done and the membership statistics will be automatically updated along with the change of the data. If formulas are changed according to positions of cells instead of their structure, errors will occur when new rows are inserted.

Non-related calculations

Cells in Excel calculate in a related way. That means once the value of a referenced cell changes, a calculation cell will re-calculate; and if the referenced cell is deleted, error will occur to the calculation cell.

But the more commonly seen scenarios are these: After values of a computed column are obtained, values of cells referenced by formulas become useless and deletable; or we may change the original value to be referenced by a computed column and then compare the new value and the old value, in which case the old value is expected to remain what it was. For instance, the original data contains persons’ birthdays. Sometimes only birthdays during a certain time period are needed to compute the ages in the subsequent computations. Thus the birthday values can be deleted after the ages are obtained; other times the birthday values are changed and ages are calculated according to the changed birthdays. It’s not easy to deal with both scenarios in Excel.

esCalc offers two types of calculation cells: related calculation cell and non-related calculation cell. The value of a related calculation cell will change along with the change of a referenced cell, as with in Excel; a non-related calculation cell becomes irrelevant to the referenced cell once it finishes calculation, and either the change or the deletion of the referenced cell value can’t affect its value.

In reality, there are more non-related calculations than related calculations during interactive data analysis.

This is the population table of the state of Alaska:

esCalc_homeintro_structure_10

C8 and its homo-cells calculate the growth rate of every census for the state of Alaska. The formula in C8 is =round((B8-B7)/B7,3) and the display format is #0.0%. Now we select C2 and sort records by the growth rate in descending order. Here’s the result:

esCalc_homeintro_structure_11

Since each formula in column C is headed by a single equal sign, C2 and its homo-cells are non-related calculation cells which will keep their values unchanged, rather than re-calculate to get the wrong growth rates according to the new order.