To compute Q1-Q3 use the QUARTILE.INC() function. Min and max are easy – use the =MIN() and =MAX() formulas on each data series (represented as columns A – E in my example). These will be the primary data in your box plot. Step 1: Calculate Quartiles and Extremes.Ĭreate another table with the following rows for each series: min, q1, q2, q3, max. If we create an auxiliary data containing this data and follow my advice from my Error Bars in Excel post, we can create a nice looking box plot. Often we draw “whiskers” at the top and bottom representing the extreme values of each series. I am using the data from the Michelson-Morley experiment:Ī box plot shows the median of each data series as a line, with a “box” whose top edge is the third quartile and whose bottom edge is the first quartile.
You’ll need to start with a table containing the data you want to plot. Here is a workbook that has the finished product if you don’t want to follow along. Read this post to find out how to create box plots that look like this:
(If you are looking for a more comprehensive reference for charts and graphs in Excel, I recommend this book.) In Excel 2013, with a little bit of imagination you can create nice looking box plots without writing any code. R, Python’s matplotlib, and many other charting libraries support box plots right out of the…box, but Excel does not. They’re useful because they show variation both between and within data series. Box plots are widely used among data scientists and statisticians.
0 kommentar(er)
