Tag Archives: Formula

LaunchTraX – A Post-Launch Brand Performance Tracking Tool for the Pharma Industry [2/5]

Hi Guys!!

With this post, I am uploading the first version of Post-Launch Brand Performance Tracking Tool ─ LaunchTraX (Download LaunchTraX_v1.0). Before jumping to the tool, I would like to share a small concept used in the pharmaceutical industry for the launch performance tracking.

Launch Date Standardization

Sales performance tracking generally involves comparing value/volume sales of brands for same time periods (e.g. Year 2011 sales of Drug A vs. Year 2011 sales of other drugs). However, in case of newly launched brands, it would make more sense to compare the sales of brands for equivalent months/quarters of launch, so as to relate the post-launch growth curve of different brands irrespective of their launch dates.

For instance, if I have recently launched a new drug (say in Jan 2011) in the Hypertension market, I can compare my drug’s first-12 month (Jan-11 to Dec-11) performance with one of its competitors (launched in Jul 2007) for the same period i.e. Scenario1 or with the competitor’s first 12-month (Jul-07 to Jun-08) i.e. Scenario2. These equivalent months of launch are represented as M1, M2…., Mn.

Launch Date Standardization - Scenarios

The Scenario2 is usually preferred for new brands to benchmark their post-launch performance with the competitor brands in the same market, thereby helping companies in fine-tuning their strategies. Continue reading

CompTraX a company profiling tool – small update

Hello friends,

The last couple of weeks have been hectic and we have been busy like bees, uploading the Dashboard 101 tutorials. This has kept me away from the blog—thankfully not for too long—and from the CompTraX updates.

I am back on track now and today, I would like to explain a set of formulas that I used while building the first version of CompTraX

You would have noticed that there is a sub-section on the Display Sheet of CompTraX that displays recent news.

spin button and news section

This section fetches the data from the Database Sheet with the help of a spin button (circled in red above). The cells which hold the relevant data in the database sheet look somewhat like:

snippet of database sheet containg news on pharma company

These cells have different news items in the same cell; separated by a line break (I have added color to different items to differentiate them in the pic). However, the Display Sheet shows the news items one by one on clicking the spin button. So, how is this accomplished?

The key lies in a group of four cells on the Process sing Sheet which looks something like this:

processing sheet

Cell 1: takes input from the data base sheet i.e. the group of news items

Cell 2: takes input from the spin button and helps decide which news item to show. E.g. in the image above the value ‘1’ means—show the first news.

Cell 3: has a formula that adds an identifier to demarcate the start and end of a news item. The formula is:

= SUBSTITUTE (SUBSTITUTE (A44, CHAR (10),”start1start”, B44), CHAR (10),”stop1stop”, B44)

This formula replaces the first instance of a ‘line break’ with the keyword ‘start1start’. Then it again acts on this modified string and replaces the first instance (which would earlier have been the second instance) of ‘line break’ with ‘stop1stop’. So we have a demarcated news item. Something like this:

‘start1start news item stop1stop’

Cell 4: has a formula which extracts this demarcated news item. The formula is explained below:

explanation of formula

It might seem a bit complicated at first look, so, I will try to explain it using another example—see the picture and the maths below.


Let’s say cell ‘A44’ has some text value. Formula in Step 3 adds a start and stop position to this string. Say this is stored in cell ‘C44’. Now, Step 4 extracts the demarcated news item from this string. As mentioned earlier, the entire set of formula is dependent on the spin button and with every click of the button the next news item is extracted.

That’s all for this post; hope you find this useful. In the next post I will upload a new version of CompTraX which would have a search interface. So, stay tuned and keep Excelling!