support

Getting Computed Column after Grouping

Below is a food sales table which is grouped by the food type. Calculate the sales amount of each food, sales amount subtotal of each type of food, and the total sales amount of all foods.

esCalc_excel_computed_column_1

In Excel, there are two common methods to calculate the sales of each food:

1. Method 1:

esCalc_excel_computed_column_2

2. Method 2:

esCalc_excel_computed_column_3

To calculate the subtotal sales amount of each type of food and the total sales amount of all foods:

In Excel, calculating the total sales amount in each category requires entering formulas one by one:

esCalc_excel_computed_column_4

In Excel, formulas can be generated automatically on the subtotal and total rows when performing group and aggregate operations. However, it is rather inconvenient if you enter formulas on the already grouped data, or calculate several kinds of aggregate values in the subtotal or total rows during the grouping and aggregation.

To perform a second grouping and aggregation after the first grouping in Excel, it is much convenient for you to use SUBTOTAL function than SUM function. However, you will still have to type in or copy and paste formulas manually. As data volume, particularly the number of groups, increases, the processing becomes even more cumbersome and error-prone. Besides, Excel dictates a strict requirement on the table format for the use of group and aggregate functions.

esCalc_excel_computed_column_5

Calculate the sales amount of each food:

esCalc_excel_computed_column_6

To calculate the subtotal sales amount of each type of food and the total sales amount of all foods:

Type the formula ={F4}.sum(), say, in F3 to calculate the subtotal sales amount of a type of food, and the subtotal sales amount of all types of foods will be calculated automatically. Then, in F2, enter the formula ={F4}.sum() or ={F3}.sum() to calculate the total sales amount of all foods. In esCalc, a formula entered in any cell of a set of homocells will be analyzed automatically and intelligently according to the target cell’s position. The result is shown below:

esCalc_excel_computed_column_7

esCalc introduces the innovative concept of homorows for storing spreadsheet data of the same information background. With homorows, you only need to enter the formula in one cell, and then each of its homocells will be auto assigned with an auto migrated formula according to the position. The desired statistics will be calculated at a time, which is smart and efficient.