Here goes the fifth in this series. We started with some basic stuff, then we reviewed proper use of averages, trends and standard deviation. We also looked at control limits, histograms and in my last post we explored moving averages.

This week I’m revisiting a topic which was immensely popular when I first published it in January 2007: a solution to draw box plots & whisker plots in Excel 2007.

Get the FREE MetricsAnalysis_110930.xlsx file which recaps everything from the previous articles, including basic stats, an histogram tool and a nifty box plot with automatic outliers detection!

What are box plots/whisker plots?

A box plots are a quick visualization method used in statistics. They show, at a glimpse, several very important elements describing a data sample:

  • the smallest observation
  • the lower quartile (25%)
  • the median (50%)
  • the higher quartile (75%)
  • the largest observation

In addition, a box plot can help visualize two other elements:

  • abnormal data (outliers)
  • average

This can be very handy in web analytics since box plots can easily reveal the limits of acceptable data and any outliers, they can be used to easily convey standard information but also highlight trends and abnormalities.

An example

As with previous articles, you can download the Excel 2007 example of a fully working box plot and whisker plot graph.

Boxplot data

Box plot calculations

Starting with our sample time series, we first calculate a number of useful elements to draw the boxplot: min & max, 1st quarter (q1), median (M) and 3rd quarter (q3), lower & higher control limits (lcl, hcl) and finally, the Inter Quartile Range (iqr).

Visualization

Drawing the box plot itself needs a little magic which would be too long to explain here, but the result is quite interesting.

Box plot, whisker plot in Excel 2007

Control limits revisited

Control limits

Using the box plot approach, we can review the way control limits are set so outliers are NOT influencing them. Basically, instead of just doing +/- of standard deviation, we use the Inter Quartile Range, which is basically the difference between 3rd quarter and 1st quarter of data. That is

iqr = q3 – q1
lcl = MAX(M – iqr * 1.5, min)
hcl = MIN(M + iqr * 1.5, max)

Outliers

The next interesting element is an automated identification of how many outliers there might be – either low or high, and the list of values. Those are also added to the box plot graph (however, this might need some manual tweaking of the graph).

Outliers
Automated outliers detection

Conclusion

As we’ve seen since the begining of this series, there’s a lot more to analysis than simply using the numbers our web analytics tools of choice provides us. Developing our analytical skills, what the data tells us and how we can slice & dice it is a cornerstone of the analyst role – the two others being a thorough understanding of the business and an awareness of the possibilities and constraints of the medium we’re using (the web, social media, etc.).

Coming up!

In June of 2008 I published the first version of a sample dashboard for Excel 2007. It became immensely popular as a learning example and I received lots of feedback from practitioners, independant consultants and agencies telling me they were using it in real contexts – although it was really built as an example. So now that we have some background, we’ll be able to move to the next stage: developing powerful analytics dashboards in Excel!

Message Sent

Thank you for registering.

Cardinal Path hosted a live session to connect with you and answer all your questions on Google Analytics.
Get all the expertise and none of the consultancy fees in this not-to-be-missed, rapid-fire virtual event.

Thank you for submitting the form.

Thank you for submitting the form.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you.

Click here to download access the tool.

Message Sent

Thank you for registering.

Message Sent

Thank you.

Message Sent

Thank you.

Message Sent

Thank you

Message Sent

Thank you

Message Sent

Thank you.

Message Sent

Thank you

Message Sent

Thank you.

Message Sent

Success!
Your message was received.

Thank you.

Thank you for registering.

Cardinal Path is continuing with its series of free training. Next we are conducting training on Google Data Studio. Check it out here.

Message Sent

Thank you for registering.

Thank you for your submission.

Your request has been submitted and a rep will reach out to you shortly.

Message Sent

Thank you for your interest.

Thank you for registering.

You should receive a confirmation email from GoToWebinar with your unique webinar login information. If you do not receive this email or have trouble logging in to the event, please email asmaa.mourad@cardinalpath.com.

Thank you for subscribing!

You're now looped into the world's largest GMP resource hub!

Thank you for your submission.

Thank you for your submission.

Thank you for your submission.

Thank you for your submission.

Message Sent

Thank you for registering.

Message Sent

Thank you for your submission.

Thank you for your submission.

Message Sent

Thank you for registering.

Thank you for registering.​

Paid media spend by Government websites increased a whopping 139% YoY in 2020.

2020 Online Behavior Live Dashboard

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

2020 Online Behavior Live Dashboard

Thank you for your submission.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Thank you for registering.

Message Sent

Success! Thank you
for reaching out.