In this blog, I’ll share a method to map customers, based on their date of birth, to appropriate classification (for analytics), and appropriate price for services as per an established table/scheme. This will be done in Excel.
Objective
Imagine the business owner has a different pricing scheme based on customer categorization; a common practice. The prices of services (e.g. tickets/rides, etc) are established by the business as follows:
You’re given only the data of the customers’ DOB. Your objective is to create a clean data table from the dataset to determine which age category the customers fell into, and what was/would be the charge.
The definition of Child…Senior are also defined as follows by the business:
Solution
It doesn’t matter if your data is for 1 customer or 1 million, the solution below is the same when you drag the formulas down/across as needed. So let’s start with just 1 customer’s data.
First, we need to find the customer’s age based on their DOB input. Once we have the age, we can map it to the correct age category (Adult, Child, etc.) as per the scheme. Finally, based on that categorization, we can utilize the pricing scheme to find the appropriate price for that customer.
From DOB, the age is obtained by: =YEAR(NOW())-YEAR(date)
where date is the DOB in any Date format that Excel understands (this is because internally, Excel works with a serial number representation of the date regardless of how it’s formatted on screen).
From Age to Age Classification, we can use IF or IFS as the simplest method. So it’d be something like:
=IFS(age>=65,”Senior”,age >= 18,”Adult”,age>= 13, “Teen”, TRUE, “Child”)
where age is the age in years and you’ll reference the cell# in the formula where you placed the data.
Finally, from Age to Price, we use VLOOKUP using the second table as this:
=VLOOKUP(class,$D$21:$E$24,2,FALSE)
where class is the cell# with classification label. And D21 is where the pricing scheme’s first dataset begins, followed by the Price to charge in E column. By anchoring the reference, we can simply copy/drag/paste the formula as we wish without losing sight of the scheme. We also use an exact match here (FALSE is the last argument).
That’s it. You’re done. The final table may look like this for a few sample customers. Now, we can pivot off of this dataset for further analysis.
NOTE: For more detailed explanation of this, actual worksheet, formulas used, etc. feel free to contact me directly as I may offer assistance in the topic(s) with help of donation (since I do not do this for a living and my time is extremely limited). Please see Home page for details/link.
