esCalc Solves Spreadsheet Problems – Inter-row computation of Continuous Summary

Uncategorized 238 0

It is difficult to perform inter-row computation of continuous summary value in Excel. Usually we have to enter all formulas by hand, which is a taxing job and easy to get wrong. This is because Excel can merely copy formulas mechanically based on relative position and cannot process the formulas of inter-row computation intelligently. Besides, junk data will be generated in the details data area.

But esCalc can solve the problem easily. Please look at the following example:

Compute the sales increment of each month on the sales data of consecutive years.

esCalc_problem_continuesum_interow_1

E37-E3, the increment value of February of 2011, is the computation of summary values. Because the summary values are continuous, the increment value of January of 2012 should be obtained by performing subtracting beyond the year, that is, E353-E320.

Try entering the formula of February:E37-E3, into F37, the result is as follows:

esCalc_problem_continuesum_interow_2

We can see that the formula has been automatically copied into all cells. Now check if these copies are correct. Take the formula of March (which is presented in the red box in the above figure) as an example. That is E67-E37 and it is correct! Then check the increment value of January of 2012:564039-349329=214710, and it is also correct.

At last, let’s expand and examine the data of March to see if there are any junk formulas left in the details data, as shown below:

esCalc_problem_continuesum_interow_3

No junk remains! esCalc has done a good job. In esCalc, it takes us less than a minute to make statistics on the sales increment of each month in ten consecutive years.

But it is difficult to perform the same operation in Excel.

For instance, enter formula E37-E3 in F37, drag and copy it to other cells, we’ll find the formula for March becomes E67-E33 though it should be E67-E37; and the formula for January of 2012 is E353-E319 but it should be E353-E320. Why? Because, in Excel, we can only adjust the formula according to the moving distances to paste it to other cells. The copied formulas will be definitely wrong unless the number of orders of each month from January to November is the same and it happens that the number of orders of December is exactly one less!

Even if formulas can be “adjusted intelligently” in Excel, they cannot be copied by dragging. Because, by doing so, junk data will be produced in the details data area, and this can only be avoided by entering each formula manually.

We can imagine the nearly one hundred formulas required to be entered during computing the sales increment of each month in ten consecutive years. But in esCalc, just one entering is needed.

FAVOR (0)
Leave a Reply
Cancel
Icon

Hi,You need to fill in the Username and Email!

  • Username (*)
  • Email (*)
  • Website