March 26, 2013

Problem: Further Computations on Grouped Data in Spreadsheet

Spreadsheets can usually carry out grouping on data easily, but the further computing on grouped data is much more difficult. For example, a data (wine inventory) is after shown as below after group and summary :

Say, if we are to compute the total amount of each wine or even each type. In the spreadsheet, it is a bit difficult to carry on these calculations or actions based on the above: calculate the ”UnitPrice*Stock” amount; sort the data in the group by inventory level; add an additional level of grouping by Name to calculate the inventory of the same type of wine; calculate the average unit price of each type of wine; and even highlight the subtotal section such as the F4 and F10.

To calculate the resulting amount of ”UnitPrice*Stock”, a new column G need to be appended along with an expression of “=E4*F4”, and then drag the expression to every row of this column. Because UnitPrice is unavailable in the subtotal row, the formula is invalid there and must be removed for each row manually, which is error-prone since there are many types of wines.

To sort the data in a group by the inventory level, you can only sort manually on each group of data since the data is allocated to several irrelevant groups. For example, the 1st group of data is from the row 4~9. It is error-prone if the data volume is huge. Another solution is to ungroup and present the data in a 2D sheet without subtotal, sort by Type and Stock, and then summarize in groups. In this solution, if there is any data in the subtotal row like “proportion of subtotal to total”, then users will have to recalculate these data once the row of subtotal disappears in the procedure. All in all, both solutions suffer from obvious drawbacks.

Add another level of grouping by Name to calculate the inventory of a same type of wine. Because the data is not sorted by Name, the data cannot be grouped directly and we have to go back to solve the previous problem.

To calculate the average unit price of each type of wine, both the 2 solutions available are quite cumbersome. The first solution is to ungroup and paste the data into 2 pieces, either of which will respectively go through the average unit price calculation or the inventory summary calculation, then join the summary values of these 2 pieces of data. The second solution is to write the formula in every subtotal section, for example “=SUBTOTAL(1,E4:E9)” in E3. Since the data in various groups differ, users will have to write N different formulas again and again and errors may quite easily occur.

To highlight the subtotal section, users have no choice but to repeat the simple action on each group one after another manually. Without any means to automate, it is so sarcastic when considering the Office Automation.

In facts, the above problem can be solved with esCalc. esCalc introduces the concept of homocell to present the business relations between cells so as to enable the auto-processing on data of various business senses. For example, if inputting the formula =E4*F4 in G4 for once, then the formula will be pasted to all rows of the details data except for the summary row, as shown in the below sheet:

The data in the group is sorted by the inventory level. In the same way, just sort once in the F4, F5 or F11 cell, the respective positions will be auto-identified, and the data in each group will also be auto-sorted, as shown in the below sheet:

As you know, the esCalc homocell mechanism can solve other problems automatically, and we will not dwell on it for it’s the topic of another essay.

Related Articles: