Monthly Top 5 Salesmen

Target

Find the salesmen whose sales are in top 5 each month according to the daily sales records in the database.

The original daily sales records of each salesman in the year are shown below:

Find the top 5 salesmen per month, and then get their intersection, as shown below:

1. Group data

First import data from the database through the wizard.

To compute the monthly sales of each salesman, the data should be placed in two layers by month and salesman. So, append a column and get the month according to the dates as below:

The result is:

The formula has been automatically copied to the other cells. Then group by month as below:

The result is:

The “Quick operation” performs sort before group by default. The operation on the menu provides more options.

Next, let’s group by salesman. Select any cell in the detailed area of D column to perform the same operation as above, and we will get the following result:

For the convenient observation of different data levels, row 2 and row 3 are marked with different colors as below:

Data observation can be more intuitive when data collapses to show different levels as below:

Here’s the table collapsed to level 1(month):

You can see that the cell style can be automatically copied between the summary areas. In esCalc, automatic copying is dominant.

Before proceeding to the next step, let’s remove the extra A and B columns, and expand the data as below.

2. Compute sales amount

The goal of this step is to compute the monthly sales of each salesman. For example, to compute Baker’s sales in January is to “add up the cells with the same month and same salesman as C4 (which are called homocells).” They are shown as follows:

esCalc computes in this way. First, enter a formula:

Then you get the following result:

The formula means:

It’s the characteristic of esCalc to intuitively write the formula according to business description. On the contrary, you need to define the starting and ending cells to write a similar formula with a traditional spreadsheet.

Next, collapse rows as follows:

You can see the formula is automatically copied to C35, C67 and some other cells. They are homocells of C3 that have the same business position. Non-homocells won’t copy the formula, such as C2 and C325.

Traditional spreadsheets lack such intelligent formula copying and the same computations have to be done manually.
Let’s go on.

3. Sort data

We need to sort the salesmen by sales amount in this step.

Traditional spreadsheets cannot distinguish the summary area and the detailed area due to the lack of homocell model, and therefore cannot sort data straightforwardly and correctly, while esCalc handles sorting easily. Let’s perform a simple action:

Here’s the result:

You see that the subjects of the sorting are homocells of C35, and cells holding detailed data are not affected. Besides, cells from February to December are also also automatically sorted since they are homocells of C35. For the same operation, the traditional spreadsheets require a great deal of manual work.

4. Filter data

In this step, you need to filter out the top five salesmen in each month. This sounds simple, but it is not easy to perform with traditional spreadsheets since they haven’t the concept of intra-group sequence number. esCalc can easily achieve this. Let’s enter a simple filtering formula as below:

Note: # represents a sequence number within the current group.

Here’s the filtering result:

All the months are done！

Intra-group sequence numbers are one of the properties of homocells. It’s difficult for traditional spreadsheets to perform the operation in the same way because of the absence of homocell model.

5. Get the intersection

This step is to find out the salesmen who appeared in each month, that is, the intersection. First collapse the rows to highlight the candidate salesmen in each month.

“{B3}” represents the homocells of B3. Here’s the result:

And then compute their intersection. Enter a formula as below:

Note: isect() is used for getting intersection.

Now we get the result as follows:

Ultimately, we have found the most excellent salesmen.

And esCalc made it easily!

By contrast, you need lots of manual operations and complex formulas to accomplish the task with the prevailing spreadsheets that don’t support set operations.