Tag Archives: MS Excel Dynamic Charts

Substituting the list box with formula!

I was discussing with my colleagues last week, the ability of excel formula to solve most of the problems that we have in our pharma analytic lives. To an extent he agreed but was more of the opinion that “formula are good but most of the time I find myself writing macros as they are easy to implement”, while I was more of the opinion that formula are very creative, fast, better than macros many a times, and we should use macros only when necessary i.e. for doing things that formulas are not supposed to do—to be very honest I find more and more blogs about people trying to exploit formulas and make them do things that were never thought of; but lets not go into all this.

While we were discussing all this, he came up with a challenge for me. Continue reading

Use dynamic charts to display special points/events on line graph

One of my colleagues came up with a visualization problem today. He was making an MS Excel dashboard for his client. The client wanted to track the sales of a drug across various countries (using third-party monthly sales data). The data itself was really simple—it had sales value in USD million for 10 months since launch of the drug in various countries.[To know more about the ‘months since launch’ way of representing data, follow D’s post on LaunchTraX].

To give you a better idea of the data set, I have created a mock data table for this post (lets call it the sales_table; M1 = one month since launch; M10= 10 months since launch).

His idea was to use a dynamic ‘line with marker chart’ to represent this. Continue reading