In Excel, formulas can be automatically worked out according to their relative positions while being copied. This works well in continuous cells, but formulas are usually wrong when the operation is performed in incontinuous cells. The computation between multilayered summaries is the latter case.
It is comparatively easy to deal with this kind of computation in esCalc. For instance, compute the ratio of each product to sales amount of each salesperson. As shown in the following figure, the salespersons belong to the first level, and the products belong to the second level. The latter is summarized according to detailed data:
The ratio of apple juice to Andy’s sales amount is expressed as D3/D2, and the ratio of barbecue sauce to his sales amount is expressed as D14/D2. Apparently, the denominator in the formulas for the same product remains unchanged. Try entering D3/D2 in E3 and you’ll get the following result:
It can be seen that all computations have been completed by entering only one formula!
First, the formula is intelligently copied to other cells of the same level. No junk data will be produced in the detailed data area in the process and the higher level of summarizing rows can be automatically avoided. Second, the formula has been intelligently adjusted when copied to other cells. For instance, when the formula in E3 is copied to E398 of the same salesperson, the denominator is the same; while it is copied to E409 of another salesperson, the denominator will be intelligently adjusted. In this way, the formulas copied in esCalc are always in line with the business logic in any cases, without being modified manually.
By contrast, the same computations performed in Excel will involve writing a great many formulas.
First, the formula D3/D$2 in E3 can only be copied to E14. It cannot be dragged to the expected position; otherwise meaningless junk formulas will be produced in the detailed data area.
After the formula for Andy is copied, you have to reenter the correct formula by hand in order to perform the computation about the next salesperson David. You cannot just copy the formula because you’ll get a wrong formula by doing so. For instance, the formula for E409 should be D409/D$408, but the actual formula got by copying becomes D409/D$2. Why does this happen? This is because the denominator in the formula should have been changed according to different salesperson.
But as the formula cannot be intelligently adjusted in Excel, it will be wrong after copying. It must be entered manually. You can imagine the time and effort the work demands. And the manual operation in the whole process makes the error probability rather high.
Besides the automatic copying and intelligent adjustment of the formulas, esCalc is worth spreadsheet users’ attention for some other features, such as the flexible step-by-step computation, which manipulates data more freely than Excel does, complete set operations, which solves complicated computations more easily, and relational computation between sheets, which can realize the operation between multiple sheets without using a formula.