It is difficult to perform inter-row computation of inconsecutive summaries in Excel due to the fact that usually all formulas should be entered by hand, which is not only troublesome but also easy to get wrong. This is because Excel lacks a smart adjustment mechanism and only supports copying formulas mechanically according to their relative positions. Besides, the computation will produce lots of junk data in the detail data area.
But in esCalc, you can deal with this kind of computation easily, as shown in the following example:
There is data of a sales order and it is required to compute the sales increment of each product each month.
“The computation between summaries” means the sales of each month is computed by suming up details and subtraction can be made between them. For example, the sales increment of Apple juice in February equals D9-D3. While “inconsecutive” means you cannot get the correct sales increment by making subtraction between the summaries of different products. For example, the sales increment of Barbecue sauce in January should be D58-0 instead of D58-D53.
Now try entering the formula D9-D3 for February in E9. The result is as follows:
As you can see the formula is automatically copied for all months without manual intervention! Whereas the similar operation in Excel requires writing complicated if function and you even have to turn to the technicians constantly.
Let’s check the formula for March, as shown in the green box in the above figure. It is D14-D9 and it is correct. This shows that, in esClac, the formula is not only copied automatically, but also adjusted intelligently. Unfortunately, Excel hasn’t this ability.
Then check the formula for inconsecutive months, the result is D58-D5 as shown in the blue box in the above figure. It is wrong.
But esCalc can correct it by using “[]” to restrict the range of the data. Thus the formula will be D9[A2]-D3[A2], meaning the computation is restricted to cell A2 (Apple juice). In this way, the computation on inconsecutive data will be avoided. The following figure shows the formula after modification:
Finally, you get the correct result and complete the whole computation with only one formula.
You may notice the smartness the esCalc shows in copying the formula. For example, the range of data in [] remains unchanged when the formula for February is copied to other months of the same product (Apple juice). Yet it changes automatically when the formula is copied to months of another product.
By contrast, Excel faces two difficulties in performing this kind of computation. First, all values of increment must be entered by hand instead of by copying (in this case there are thousands of values). Second, the formulas for inconsecutive months should be dealt with specially. There are hundreds of such formulas in this case ( for there are hundreds of products). The results after aformula is copied in Excel are as follows:
Both results are wrong! It seems that there is no choice but to enter all formulas by hand. Of course you can use VBA to solve the problem, but it is difficult to master unless you are a professional programmer.