Excel is the favorite of business staff because of its simplicity and easy use, but actually many of the common operations are not so easy to perform with it. The computations of link relative ratio and year-on-year comparison fall into this category. This is because, during the copying of formulas, Excel can only compute the offset mechanically but cannot identify detailed data and monthly/annually subtotal, which always produces wrong results required to be modified manually. Lacking the function of copying formulas intelligently, Excel will copy formulas into detail area while dragging them across the summary area, producing lots of junk data there.
esCalc, on the contrary, offers the function of copying formulas intelligently. Thus it can compute link relative ratio and year-on-year comparison easily. In the following example, the sales record of a Volkswagen 4S store holds records of purchases of different vehicle models by different customers.
We are asked to compute the ratio of sales of each month to that of the previous one and to that of the same month in the previous year by the end of last month (suppose the current month is November 2012). Detailed data need to be kept for use in later computations.
To compute the year-on-year comparison, we just need to group and summarize data according to the year and the month and enter formula “=C458[A3]/C4[A3]” into cell F458, as shown below:
The above formula will be “copied” intelligently to compute all monthly subtotals, skipping the annually subtotal automatically at the same time. For instance, the formula for F890 is “=C890[A3]/C458[A3]”.
Likewise, we just need to enter the formula once to compute link relative ratio. Enter C4/C4600 into G4, for instance, and the result is as follows:
It can be seen that the formula has been “copied” intelligently into summary cells of the same level like G458 and G890, skipping those one level up, like G2, G3 and G4599 .
The reason esCalc can do this easily is that it has the concept of “homocell” – cells of the same business level – which enables formulas entered into the summary area to be copied into cells of the same level (or summary area of the same level) intelligently without affecting both the next-level detailed data and the up-level summary area.
In esCalc, once a formula is entered, other homocells will adopt and adjust it intelligently according to the business logic, whereas this is not the case in Excel.
In Excel, to compute link relative ratio by typing “=C458/C4” in cell F458 and dragging or copying it to other cells of column F won’t lead to expected formulas, like “=C890/C458” for F890. Instead it will become wrong ones like “=C890/C436”. That’s because Excel can only computes the offset mechanically, whose logic is because 458-4=454, thus 890-454=436.
In order to correct the mistake made by Excel, we have to enter every formula manually, which requires a lot of work when the data is huge.
In addition, there will appear meaningless formulas in cells, like F1324, in detail area, due to Excel’s inability to distinguish summary area from detail area. As a result, when a formula is dragging across the summary area, it will also be copied automatically into the detail area. Obviously this automatic copy is not wanted. So to avoid it, the only way is to enter the formula manually.
Similar errors will occur when we perform operation on YOY column in Excel: inability to distinguish summary area from detail area; incorrect copying of formulas and junk formulas in detail area.