Smarter Way to Compute after SUBTOTAL in Excel

Spreadsheet is popular with business users for its simplicity and usability. But it is a pity that some common computations are still tough to solve with spreadsheets. The inter-row computation of summary value is such tough problem.

According to the data of order below, how to calculate the rate of sales increase in each month?

Obviously, the rate of increase in February should be (D58-D2)/D2, which is a typical inter-row computation.The tough problem for traditional business spreadsheet software is that the traditional business spreadsheet software only allows for manual formulas entering. Copying or dragging formulas to other cells will only lead to the wrong result. For example, copy a formula to the cell of March, as shown in below figure:

75-fold increase? Obviously wrong! The correct formula should be (D113-D58)/D58, while the resulting formula by copying is (D113-D57)/D57.The reason for this phenomenon is that the traditional business spreadsheet software only allows for the rigid formula-pasting based on the relative positions, lacking the intelligent adjustment mechanism.

Obviously, if the data volume is huge, entering all formulas manually will be such a pain and error-prone.

Then, let’s talk about esCalc. As brand new business spreadsheet software reputed for great computing capability, esCalc is highly expected on this problem.

The same data is shown below:

In esCalc, you only need to input the formula once to solve this problem! For example, enter (D58-D2)/D2 for February, and the result is shown below:

No doubt you’ve seen that all computations are finished by entering the formula for once. No need to copy or adjust the formula. Take the formula for March for example, it is (D113-D58)/D58, just the same as I expected.

esCalc boasts an unique homocell model which arranges cells not in a simple relative positions, but in an auto-established business association. The immediate benefit is that the formulas will be copied automatically, that is, the formula will be copied and pasted to the cells at the same business level automatically. In the above-mentioned case, for the March, April, and February bands, the respective cell in the respective summery row can be regarded as the homocells to each other. Therefore, the formula written in the cell of February will be copied and pasted to the corresponding cells of March and April.

Needless to say, such copying is not the migration of the relative positions in the traditional business spreadsheet software. This is a kind of Intelligent Migration, for example, migrate the formula for February to the homocell for March, as mentioned above.

Through the auto-pasting and intelligent migration of formulas, esCalc can relieve the great amount of manual work. Because it is implemented automatically, the possibilities of errors are also reduced greatly.

Seeing is believing. The computational capability of esCalc, as legend has it, is truly powerful. Let Excel trembles at esCalc!