In this multi-series of blog, I’ll touch on different ways to categorize data in buckets, or bins, and summarize in meaningful ways. Some will use Pivot Tables, some will not. But we’ll cover 3 common scenarios.
Let’s do the second one here…
We have data on employees’ join-dates (TABLE 1).
Objective: We want to tag their information based on their duration to-date with a descriptive text as shown in the Duration Scheme table.
Which basically says if the employee’s tenure so far has been 0 days, tag him/her as “1 month or less”. If 366, then “Over 1 year” and so on in-between.
And snippet of data set we have to work with is shown below:
The first thing we can do is find the tenure in days as follows by creating a new column and adding a formula for each row:
which is done simply as =TODAY()-B8 and pulling it down for the rest of the data set. So it’ll look like this:
Next, all we have to do is do a lookup using Join Date as the lookup value, into the Duration Scheme (see table above) and pull in its matching value from 2nd column, Fixed Tenure Buckets.
If the Fixed Tenure Buckets data is is E9 to F13 range, then the formula is: VLOOKUP(C8,$E$9:$F$13,2,1)
Then we pull it down for the rest of the rows. We can put in a column called Description and put the tag text from the scheme for each employee.
But we can do better. We can do this without needing the Tenure column by nesting TODAY() within the VLOOKUP as follows: VLOOKUP(TODAY()-B8,$E$9:$F$13,2,1)
In both cases, we are using closest match of VLOOKUP instead of exact match as the actual join date can be any day within a given month and we won’t need to account for each such situation.
Using both methods the final table is as follows. The first Description uses the Tenure column, next one uses the nested VLOOKUP with TODAY formula…
I will continue this theme to tackle different types of data in the next blog: Understanding buckets, bins, categorization (3/3).