Filtering Each Group

Below are monthly mean temperatures in a certain region grouped by year. Find out the months in each year whose mean temperatures are higher than the annual mean temperature value of the current year.

Excel

Because the mean temperature of each year differs, the filtering criterion differs, too. In Excel, you are unable to process the data of all years at the same time. There are two methods to handle the task.

1. Method 1:

Such a method is complicated to operate. If there’s huge data and there are many groups, the workload will be very heavy. What’s worse, the former statistics will get lost from the final result and cannot be utilized directly in the subsequent analysis.

2. Method 2:

Add auxiliary calculation data in column D to check if the monthly temperature is not less than the average value of the current year:

Then, filter the data in column D and keep the records with TRUE. The result is shown below:

Because SUBTOTAL function is used to calculate the mean temperature of each year, a second calculation will be performed after filtering, and the results in column D will change correspondingly. But, this will not affect the correctness of filtering.

With this method, users can avoid the trouble of editing data manually and get higher efficiency than with the first method. However, users will have to change their natural thinking to add a computed column, which increases difficulty in solving the problem. In addition, because the formulas vary with each year, you will have to set them one by one. The workload is still heavy when the number of groups is great.

esCalc

Filter:

In esCalc, the filtering expression will be analyzed intelligently according to the structure. It is quite convenient that one step of filtering is just enough to achieve the goal.