A More Intelligent Tabular Data Computing Tool Overperforms Traditional Spreadsheet

The spreadsheet has an intuitive interface, simple and easy-to-use calculation function, and powerful chart tools. It is the most widely used desktop data processing software and plays an irreplaceable role in the office automation areas.

With nearly 20 years passing by, the version of Excel has evolved from 5.0 to 2012, Lotus1-2-3 has joined hands with IBM, and OpenOffice Calc has parted with Sun, LibreOffice Calc has implemented the real open source. Although the minor functions of spreadsheet are also under constant improvement, but the data calculation function as the core has almost never changed. Just like other spreadsheet software, esCalc also provides the typical traditional spreadsheet interface, import and export, statistics chart, and other minor functions. However, with the innovative model and outstanding engine, esCalc has a step ahead in the data calculation, and more intelligent tabular data calculation ability.

Tabular data refers to the 2 dimensional data of multiple rows and columns. This is the commonest data form to the business personnel. Most spreadsheets, reporting result, and database table are of the tabular data, for example: Employee information list, sales record, and supplier information list. Below please find the case to calculate the tabular data:

There is a piece of data, recording the product sales situation, including product serial number, type of product, sales region and product name, sales value, and sales persons. Now, we need to calculate: Top 3 categories of products with the greatest sales value, and top 3 regions in which these top 3 categories achieved the highest sales values. The intuitive solution is:

1. Group the data by the product type and sum up the sales value in each group.
2. Filter out the 3 groups with the most summary values.
3. Proceed to group the data by sales region, and summarize each group of sales value.
4. Filter out the 3 groups with the most summary values as the final result.

Here are a few examples:

To calculate the monthly sales comparison through link relative ratio method, and the comparison on year-on-year basis
To calculate the monthly salary, working attendance, and performance to calculate the net payroll of employee
To calculate the product whose sales is rising for consecutive 7 days
To find the months since 1999 in which the sales of those months are above the average sales of the respective years

There is no business relation between the cells on worksheets of the traditional spreadsheet application. Actually, these are only some independent cells; For esCalc, multi-leveled overall tabular calculation model is adopted. The business relation between data is established and maintained intelligently for the data in the tables. esCalc on the one hand reduced the operational difficulty, and on the other hand improved the calculation ability on the tabular data. This is reflected in the below featured functions:

1. Paste formula automatically

esCalc has an overall tabular calculation model. There is the business relation between cells, and expressions can be copied automatically between cells of the same business sense (called as homocell)

Case Description:
Given the population statistics data of some states, we now need to calculate: The comparative population increase in 2010, compared to the population in 2000.Firstly, filter the data and group by state, as shown in the below figure:

Traditional spreadsheet solution:
Take Excel for example, firstly, fill the expression in C2 =(C4-C3)/C3 in C2, then paste this formula to C5, C8, C11, and other cells, the result is as shown below:

Excel only allows users to copy the data manually, which means that users will have to copy the data for multiple times of the same number as groups. This can be endured when the data volume is small. However, it becomes unbearable when the data volume is great, for example, to collect statistics on the cities in this nation.

esCalc solution:
esCalc only requires users to compose an expression to finish the calculation. For example, in C2, if filling in the expression =(C3-C4)/C4, then the formula will appear in C5, C8, C10, and other cells correctly and automatically, as shown in the below figure:

esCalc advantages:

D2, D5, D8, and other cells share the same business sense, so they are all homocells to each other. To calculate the total population of the 2 years, you only need to input C3+C4 in D2, then the C6+C7 will be automatically copied and pasted in D5; D3 and D4 is not the homocell of D2. Therefore, they will not be copied in these cells.

The traditional spreadsheet lacks the overall tabular calculation model. There is no business relations between cells, and unable to differentiate the homocells of cells. Therefore, it is unlikely to carry out the formula copying intelligently like esProc does. When there are multi-levels and a large volume of data, the drawbacks of traditional spreadsheets will be magnified sharply and become a great trouble. The workload on users will be increased obviously and the chances of incorrect manipulation will increase.

Besides the intelligent calculation formula copying, these convenient features are also available for homocells: Auto-summarizing after grouping, auto-pasting the font color and other tabular style, auto-filtering or hiding, homocell auto-sorting, and homocell auto grouping.

2.Intelligent formula parsing

esCalc supports intelligent formula parsing. Once the formula is copied, the contents will be changed according to the current business logics; For the same formula, the calculation engine will perform the calculation according to the various business logics on the corresponding data.

Case Description:
According to the population statistics over the years in some states, calculate the proportion of each state in each year and the average population of each state. Suppose that data is grouped by year and the total population of each year is counted, as shown in the below figure:

Traditional spreadsheet solution:
Firstly, calculate the population proportion. Then, in D3, enter the formula “=C3/$C$2”, drag and copy it to cells from D4 to D8;In D4, enter the formula “=C10/$C$9”. Similarly, copy it to the cells from D11 to D15.For Excel users, they will have to modify or enter the formula repeatedly. It is because the denominator in the formula needs changing at least once a year, and the numerator will remain the same. The result is as shown in below figure:

Then, calculate the average population. In E3, enter the formula =SUBTOTAL(1,C3:C8);In E9, enter the formula =SUBTOTAL(1,C10:C15);Excel requires its users to enter the formula repeatedly for each year because the number of states may vary in each year. At last, calculate the total average value. In E16, enter the formula =SUBTOTAL(1,C3:C15) The result is shown in the below figure:

esCalc solution:
With esCalc, only one step is required to calculate the population, that is, enter the formula “=C3/C2” in D3. As can be seen, esCalc can not only copy the formula to homocells automatically, but also parse the formula intelligently in the process of copying. In other words, in the cells from D4 to D7, the denominator is C2, while in the cells from D10 to D15, the denominator becomes C9 automatically, as shown in the below figure:

Then, calculate the average. Firstly, calculate the average value in each state each year, and enter the formula in E2:={C3}.avg(). Then, calculate the total average value, and input the same formula in E16, that is, ={C3}.avg(),as shown in the below figure:

esCalc has performed the intelligent parsing on formulas. In E2, {C3}.avg() is parsed automatically to calculate the average value for each state in 2011;While in E16, for the same {C3}.avg(), it is parsed as average calculation on all data.
esCalc advantages:
esCalc can make the intelligent decision according to the various business senses of various cells. The formulas can be parsed intelligently, and the user’s intention can be understood correctly. The intelligent parse of formulas can reduce the manual operations, lower the workload, and reduce the incorrect manipulations to the maximum.

There are no business relations between the cells of traditional spreadsheet. Therefore, they are unable to perform the intelligent parsing, and only capable to perform the most simple and rigid migration between the relative positions. Even a little the calculating requirements change will bring about a great many manual operations, and the workload will be heavy and error-prone.

3.Data processing after grouping

esCalc features the overall tabular calculation model that is especially fit for the data processing after grouping.
Case Description:
Suppose that there are some average temperatures records of each month in a certain region and these records have been grouped by year. Now, we need to make statistics on the months in which the average temperature is higher than the average temperature of this year. The current data is as follows:

Solution to the traditional spreadsheet: Filter in each group respectively and then join
Firstly, filter out the data meeting the conditions in the group 1, that is, filter in the column A. Select the data of 2010 as the filter criteria. Once filtering is completed, perform filtering on the column C. The filter criteria are set to find the data greater than or equal to 68.52. Once filtered, the result is as shown below:

Copy the above-mentioned result to the new worksheet. Then, restore to the original data in the original worksheet. Repeat the above procedure for the second group, and the result is shown below:

Copy the above-mentioned results and join them to the result of the group 1. Then proceed to perform the calculation for the group 3 and group 4 until the data in every group is calculated. For each group of data, the calculation will be conducted in 5 steps: Filter the year; filter the temperature; copy; join; and restore the data. If there are N groups, then the procedure will comprise 5N steps. This solution requires a bit more steps, and the manual workload is heavy. If there are many groups, then the workload will become unacceptable.

There are still some solutions requiring less workload. These solutions can be roughly explained as given below: 

In the column D of each group of data, enter 1 calculation column formula and copy it to other cells of this group, for example “=C4>=$C$3”.For each group, perform the filtering on column D after the calculation is completed, and keep the record whose value is TRUE; Finally, remove the D column in which the data gets messy because of the filtering. This solution requires 2N+2 steps in total. Compared with the first solution, this solution cut down the steps by half. However, the workload is still relatively huge if there are a great number of groups.

esCalc solution: Complete all in one step
esCalc can solve in only one step: In cell C4, right-click to select the filter action, and the filter criteria of “Cell value” is set to “>=C3”,as shown in the below figure:

Click OK and the final result is shown below:

As can be seen above, esCalc solution only requires 1 step to finish, regardless the number of groups. esCalc solution has nothing to do with the number of groups. To complete the same calculation, Excel will bring about more troubles. For the first solution, the workload is quite huge. By comparison, the second solution requires less steps, but it is far less intuitive than the first solution. Users will have to convert their natural train of thoughts and resort to the calculation column to implement this solution indirectly, which requires a relatively stronger background on the technical competence and proficiency of analysts.

esCalc solution:
For the tabular data before grouping, the data is arranged in the neat and uniform places, and the association of business only exists in the neighboring cells. Therefore, even the tools without the overall tabular calculation model, such as Excel, can undertake the calculation easily. However, after grouping, in particular the grouping at multi-levels, the business relation exists in the form of scattering in many places. Excel cannot handle such business relations intelligently. Therefore, a great number of manual operations are required.

esCalc features a overall tabula calculation model, especially suitable for the operations after data grouping. No matter the post-grouping filtering, column calculation, or the sorting and grouping, esCalc can be more intuitive and straightforward than the traditional spreadsheet to handle these operations.

Calculation between tabular data

The interactive calculation between multiple sheets mainly include: Join multi-level or single level tables horizontally; Join multi-level or single level tables vertically; Copy some groups at the level and its subordinates to a new table. esCalc can greatly simplify the calculation between tabular data.
Case Description:
According to the basic salary worksheet, the absence record worksheet, and the performance worksheet, calculate the actual salary of employees. When computing, use the below formula: Payable weekly salary = standard weekly salary *(1+Performance bonus-Absence hours /40)+bonus. The original data is as follows:

For the Basic salary table, both the sheet name of Excel and the esCalc grid of esCalc are “Basic”

For the Absenteeism data, both the sheet name of Excel and the esCalc grid of esCalc are “Absence”

For the performance and bonus data, both the sheet name of Excel and the esCalc grid name of esCalc are “Performance”

Traditional spreadsheet solution: Complex formula
Firstly, import the Absenteeism data to the basic salary table, and fill the formula in D1: =IFERROR(INDIRECT(“‘Absence’!”&ADDRESS(MATCH(A2,’Absence’!$A:$A,0),2)),0), and copy this formula to other rows.
Then, import the performance and bonus information to the basic salary table. In E1, fill in the formula =IFERROR(INDIRECT(“‘Performance’!”&ADDRESS(MATCH(A2,’Performance’!$A:$A,0),2)),0). In F1, fill in the formula =IFERROR(INDIRECT(“‘Performance’!”&ADDRESS(MATCH(A2,’Performance’!$A:$A,0),3)),0).As mentioned above, copy it to other rows. The result is as shown in below figure:

In the last step, calculate the weekly salary payable in the G2 cell, and populate the formula =C2*(1+E2-D2/40)+F2, and copy to other rows manually.

esCalc solution: Join operation
Firstly, set the A2 cell of these 3 esCalc grids as “master cell”. Then, in the esCalc grid “Absence”, select B2, and perform the left join to join to the D2 of the esCalc grid “Basic”;After that, select B2 and C2 in the esCalc grid “Performance”, and perform the left join to join to the E2 of the esCalc grid “Basic”. The result is shown below:

Finally, calculate the payable weekly pay, the esCalc formula is the same to that in the Excel, i.e.. “C2*(1+E2-D2/40)+F2”. The only difference is that the esCalc users only need to write the formula once, and no need to copy.
As you can see, esCalc users only need to perform the most basic join operation to merge the data from multiple tables, not having to compose the code; Excel needs to combine several advanced functions together in a rather complex style.

esCalc advantages
The traditional spreadsheet does not have the overall tabular calculation model, and the calculation between multiple sheets is extremely difficult. The users of traditional spreadsheet tools have to perform a great many manual operations or write the complicated formula to complete the same calculation, and the calculation requires a strong technical background that is hard to achieve for average business personnel. esCalc features the overall tabular calculation model, which makes the calculation between multiple tables become the copy & paste alike simple operations. Even the business personnel of the weak technical background can handle it by themselves.

Senior functions for tabular data

esCalc also provides a great many advanced functions to handle the tabular data, and capable to confront the complicated calculations.

Case Description
According to the scoring statistics of the recent 3 games played by a basketball team, collect statistics on the players whose scores are among the top 5 in all games. No matter using esCalc or Excel to calculate, in this case, the first steps are both to group by the number of games, and sort the data in the group by score, as shown in below figure:

Traditional spreadsheet solution
Firstly, calculate the rankings of scores in each group. For example, in E3, fill in the expression =row()-2, then drag the formula to E3:E10 area. Once all groups are processed, it will be shown as below:

Then, filter out the data less than or equal to 5, as shown in the figure:

These data are to be copied to the new sheet, sorted by players, and summarized by the player type. The statistics is collected regarding the total number of games in which each player has scored the points among the top 5. The result is shown below:

If the total rounds are 3, then it indicates that the player scores in the 3 games are all among the top 5.Therefore, the result will collapse to the total line for all players. Then, filter out the players who play the games totaling 3. In this way, the players whose scores are among the top 5 in all games will be obtained. The final result is as follows:

If there are N games in this case, then Excel users need perform:2N+6 steps. There is also solution comprising less steps. But the relative formula is so complex that the normal business personnel could not understand, for example, you can use the formula:=IF(AND(COUNTIF($A$12:$A$16,A3),COUNTIF($A$20:$A$24,A3)),A3,””).The intention of this formula is roughly described as follows: Respectively compare the A3 of the first group with the cells from A12 to A16 of the second group, and then the cells from A20 to A24 of the second group. If both exist in these 2 groups, then A3 is the desired result.

The solution 1 converts the train of thoughts: to calculate the intersection of sets of top 5 players of each game, its natural train of thoughts is converted to judge if the times the player among the top 5 is equal to the number of games. The solution requires the relatively strong logic thinking and abstract thinking ability. The solution 2 implements the operations on sets from the underlying layer, so it requires the relatively strong technical background and the a bit long time to get familiar with the tools. Excel does not allow users to calculate the intersection of sets, no matter which ways they use.

esCalc solution:
Firstly, filter out the data for the players ranking the top 5 in each game with the filter criterion “#<5", # represents the serial number of the group. Unlike Excel, esCalc only needs to filter for once in this case. The result is shown below:

In the step 2, to identify the players in each group, just fill in the expression “={A3}” in E2.
In the step 3, to identify the players exist in more than one group, that is, intersection of sets, just fill in ={E2}.isect() in E1.The final result is as follows:

esCalc supports the intersection of sets directly. In this case, the calculation is very easy, and esCalc users are only required to perform 3 steps intuitively.

esCalc advantages
The advanced function of esCalc is especially designed for the business personnel. They are simple, easy-to-understand, and convenient to solve the complex problems, such as: year-over-year comparison, link relative ratio, intersection of set, difference set, ranking, and sequence number calculation.

The traditional spreadsheet lacks the overall tabular model, and therefore lacks the functions for the tabular data. The similar calculation is conducted through the manual operation or complex function joining. In some cases, users will have to use VBA to achieve the calculation goal, and always face the challenge of rising demand on technical background, time, and patience.
　　
All in all, esCalc features the overall tabular calculation model which makes the esCalc perfect with the above-mentioned 5 features. Therefore, esCalc can provide the more intelligent data calculation capability than the traditional spreadsheet. As a result, esCalc over-performs the traditional spreadsheet in respects of improving efficiency, lowering the technical requirements, reducing the human errors, streamlining the calculation procedure, and handling the complex problems.