Earlier this month, I had the pleasure of flying out of Chicago’s O’Hare airport. I was checking in at the ungodly hour of 6 AM on a Sunday, and hoped that would be a quiet time at the airport.
The streets near the downtown hotel were almost empty, so that was encouraging, but the closer the taxi got to the airport, the busier things got. Not a good sign!
There was no line at the Air Canada counter (woohoo!), but there was no counter agent either. He showed up a couple of minutes later, and that part of the process went quickly. (The same guy showed up at the gate later, to take our tickets, and attached the jetway to the airplane when it arrived. I figured he’d fly the plane too, but he didn’t!)
Then, it was on to the dreaded security checkpoint, and the inevitable long wait. One poor gentleman actually passed out in the line, and fortunately recovered quickly, aided by a paramedic, who was also in line. That’s another bad sign, if you have to step over people, to get through security.
Excel Box Plot Chart – Wait Times
And what does all this suffering have to do with Excel, you ask? Well, to help you plan your next trip, I found a website that lists the wait times for US airport security checkpoints.
From their data, I created an Excel Box Plot (Box and Whisker Chart), for a few of the security checkpoints at O’Hare. You can do something similar for your airport, and choose the best time to fly. Hint: It is not Sunday at 6 AM.
Create Statistics from Your Data
To create a box plot, you’ll calculate the the MIN, MEDIAN and MAX for each series.
Then, use the PERCENTILE function, to find the lower quartile (Q1) and upper quartile (Q3).
You can calculate the AVERAGE too, if you’d like to show that on the chart.
Calculate the Differences
Based on those numbers, you’ll calculate the differences, to find the size of each box and whisker.
Make Stacked Column Chart
Then create and format a stacked column chart (boxes), with error bars to show the highs and lows (whiskers).
Unfortunately, I don’t remember which checkpoint I went through, but I’ll bet that it was Checkpoint 6.
Jon Peltier’s Box Plot Utility
If you need to make box and whisker charts, or other custom chart types, the PTS Excel Chart Utility will make it much easier and quicker for you to create them.
It adds a new sheet in your workbook, calculates the statistics for your data, then creates and formats a box and whisker chart. It will pay for itself very quickly, in the time you’ll save.
Watch the Box Plot Video
To see the steps for creating an Excel Box and Whisker chart, watch this Excel video tutorial.
It’s recorded in HD, so select that option when viewing it, if you can, for a clearer picture.
______________
Since I have over three million miles in the air over the last 15 years, I normally bypass long security lines by slipping in the frequent-flyer queue. And I’m not sure about just how effective this data would be as one wouldn’t normally use security line wait times as the first priority in booking flights.
Having said that, I really like the way you setup this box plot with whiskers. I’ve never really used one (other than playing around) yet you’ve laid it all out in a simple, no nonsense way. A helpful reference for anyone looking to create a Box and Whisker chart for Excel.
Yes, I fully agree with Gregory as I have understood the concepts of percentiles by reading two posts (as it referred to December 2008 blog as well) and of course this chart also for the first time as Debra has a knack of teaching and making difficult topics simple to understand.
I have one request if you can write a blog on waterfall charts.
Thanks and best regards
Fakhar
[…] Box and Whisker Chart […]
Are those blue squares the averages? How do you put that on the plot?
Could I please ask, how the second Min and Max values work? I have a set of data I want to create a box and whiskers plot for, but there are lots of minus values.
When I calculate the min value (Q1-minimum score) both the Q1 and the minimum scores are minus values (-5.5 and -18 respectively) therefore when I calculate -5.5- -18 I obviously get a positive value. Would this be inaccurate as the minimum is looking at the lowest point of the data?
Therefore should I plus it instead?
(very poor mathematical understanding as you can probably tell!)
Thanks a lot
Hi, I want to create a box plot for my data for my PhD thesis. The dara are for seed emergence rate and root weight, data are too small with just 4 replications. When I do all mathematical value for box and whisker some result are zero. So how can i create a box plot with 0?? should I use the first calculation of min, max, q1, q2, mdian not the second calculation?
Many Thanks,
Moj