+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 : Error in making a box-and-whisker plot

  1. #1
    Registered User
    Join Date
    11-19-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    1

    Unhappy Error in making a box-and-whisker plot

    So, i'm trying to make a box-and-whisker plot by using the fourth stock chart : volume-open-high-low-close.

    I have made box-and-whisker plots like this before, i don't know why it isn't working now.

    This is how i formatted the data, it should be correct according to my teacher's handout and various other web tutorials.


    Statistic Title of Data Series
    Median 6
    Q1 4
    Min 2
    Max 18
    Q3 8

    But an "information" window keeps on popping up every time. It says "To create this stock chart, arrange the data on your sheet in this order: volume traded, open price, high price, low price, closing price. Use dates or stock names as labels."

    I tried this in my 2007 excel version and my other WPS program which is just a copy of the 2003 version of excel, why isn't this working??
    Help!
    Thanks!!

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Error in making a box-and-whisker plot

    Hello,

    I don't quite see how your data fits the volume-open-high-low-close structure, apart from the similarity of having five data points.

    Anyway, for that kind of chart you need at least two rows/columns of data, preceded by a date. Then select the data including the date label and create the chart. After that, you can change the date to a text label and remove the second data point for each series.

    See attached. First chart is with two data points, and one label has been changed from date to text. Second chart is the same starting point, then re-defined each series to look at one data point only.
    Attached Files Attached Files

  3. #3
    Spammer
    Join Date
    01-26-2010
    Location
    Worcester MA
    MS-Off Ver
    Excel 2003
    Posts
    184

    Re: Error in making a box-and-whisker plot

    I've written a Box and Whisker Chart (Box Plot) tutorial, and it's located at Excel Box and Whisker Diagrams (Box Plots).
    Jon Peltier
    http://PeltierTech.com/

  4. #4
    Spammer
    Join Date
    01-26-2010
    Location
    Worcester MA
    MS-Off Ver
    Excel 2003
    Posts
    184

    Re: Error in making a box-and-whisker plot

    Re Flying Curry's issue...

    A problem with the built-in stock charts in Excel 2007 is that they fail when there are less than a certain minimum number of categories (that is, too few rows if the series are in columns). I think it's because Excel's guess about whether your data is in rows or columns is based solely upon whether there are more rows or columns in the data range, independent on the chart type. Excel thinks there are insufficient rows for OHLC even though you intended to plot by columns and there are sufficient columns. Excel 2003 & earlier let you select by rows or columns in the chart wizard before the chart was ever created.

    The workaround is to select a bunch of empty rows, create the chart, then change the data range to exclude the empty rows.

    FWIW, the technique in my article Excel Box and Whisker Diagrams (Box Plots) gives a better representation of a box plot.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1