So, you want to make sense of your data -- maybe it’s some daily lead data like we have right here – but the problem is, when you put it on a graph, it just looks like noise. Weekdays and weekends confuse the issue, and confuse our understanding of what’s really going on underneath the surface. Right?
That’s okay, because Excel rocks and so do you. Let’s use what we learned from our last post to see what’s really going on here. First, we’ll assume that this data is being updated every day, and in that case we’ll want our chart to be dynamic. Using the MAX() formula will give us the most recent date in the data set, even when we refresh, so we’ll always have the latest insights. Next, we’ll create a dynamic date range beneath it, by writing a formula to subtract one from the previous cell, and then dragging it down (Pro Tip: if you drag down a multiple of 7 rows plus 1 more, you can set your horizontal axis label such that you are always showing full weeks – helps with readability, as well as scratching that perfectionist itch.)
Now the fun starts – we’re dishing out a 2-criteria SUMIFS(), and using a fixed input at the top of the row to specify a 7-day trailing range from the adjacent cell in column F. Once we drag this formula down, it will give us a 7-day trailing average from EACH date which, since it includes the seven days of the week, will begin to look very smooth. Oh yeah, that volatility noise we saw just met its match…
If you paid extra close attention to the way we’ve locked the cell references in our formula, you will now be able to drag that functionality down AND over, so include 14-day and 28-day trailing periods, like this.
You should be staring down three columns, each with trailing daily averages for those leads of 7-, 14- and 28-days, respectively. Now for some right-brained fun getting those bad boys into a chart to pre-attentively* visualize your data (*that’s a fancy word that means you can look at this thing without even thinking about it and know exactly what you need to know). When shown in this way, numbers become a story, volatility melts away, and we’re left with an understanding of how our business is changing over time. What’s more, we can update this data every day and continue to see the story unfold. Master the art of analysis, and pretty soon it won’t just be the story that you’re telling – it will be the story that you’re writing…
This is the second post in Penn Foster’s Analytics Blog Series, which seeks to demonstrate how organizations can utilize the power of analytics and data to improve business outcomes. Read more posts here:
Resources: Photo credit.