Complex Computations in Categorized Tables

According to the box office revenue data of several movies in a week, find out data of the movie whose box office revenue rises for the longest continuous days in the week.

esCalc_excel_complex_table_1

To calculate the number of days when a movie’s box office revenue keeps rising, first you need to take movie as the primary keyword and date as the secondary keyword to sort the data. The result is shown below:

esCalc_excel_complex_table_2

In column D, calculate the consecutive days of increase in box office revenue per movie per date, such as Marvel’s The Avengers. Enter a formula in D3 and copy it to other cells in the same column using drag-and-drop. Manual editing is needed to perform the copy & paste. So there will be a great deal of work when the amount of data is large.

esCalc_excel_complex_table_3

Group and aggregate records by movie name to find out the maximum aggregate value in column D, and remove the summary rows. The result is shown below:

esCalc_excel_complex_table_4

Collapse rows to the group level and perform sort by the longest consecutive days of box office revenue rising. The result is shown below:

esCalc_excel_complex_table_5

Now you can view the movie with the longest days of consecutive box office revenue increase in the worksheet. To keep only the data of this movie, copy and paste the data to a new worksheet manually. If the work is done through the filter operation, then you must copy the grouping rows to another worksheet to filter. Otherwise, it will be hard to distinguish the detailed data rows and the grouping rows.

In Excel, special attention should be paid to the order when calculating. Finish calculating the desired data before data grouping. If you do calculation in every group, paste the formulas one by one manually to each category. The operation will become cumbersome when there are many categories. Besides, you are unable to specify levels for the filtered data, so data you are going to work on should first be organized, making the whole processing complicated.

esCalc_excel_complex_table_6

Group by the movie name with Sort before group checked:

esCalc_excel_complex_table_7

Calculate the daily number of continuous rising days of each movie:

esCalc_excel_complex_table_8

In the grouping rows, calculate the longest consecutive days in which the box office revenue keeps rising. To do so, enter the formula ={D3}.max() in D2. The result is shown below:

esCalc_excel_complex_table_9

With esCalc, the desktop BI software, you can directly filter the groups:

esCalc_excel_complex_table_10

In esCalc, you only need to follow the natural train of thought to operate stepwise to complete the complex calculation. Because only one formula suffices to do the same calculation in all homocells, the copy and paste operation is not needed when handling the batch calculation. Data filtering will be performed within homorows, so that the inter-influence between various kinds of data in a same column can be avoided.