Thursday, September 15, 2005

Latest $50 project -- finding correlation between ad spending and revenues -- is done...

Edo recently submitted a request (Thanks, Edo!) for a means to analyze some advertising spending data and revenues over many periods, testing for correlation between each of multiple different advertising categories and the revenues.

Using =CORREL(), =HLOOKUP(), =MAX(), and =MIN() I set him up nicely with a solution that quickly and easily identifies top and bottom performers over the course of the entire time frame (cumulative for each period).

One should know that there's probably about a million other ways to go here.

Some other thoughts I had right off were to use the =RANK() function, both ranking ascending and descending, and then the =AVERAGE() function to average out the rankings over time to find consistent "winners" and "losers". Also, using simple conditional formatting, one could highlight ads that had particularly high (over a set parameter) or low (below a set parameter) correlations. And we haven't even left the use of the correlation statistic. There are many other statistics that return measures similar. If Edo was ambitious, maybe regression analysis is the way to go.

Regardless, here's another project done, and I didn't use a single line of VBA, but what the heck, it's another $50.

No comments: