+ Reply to Thread
Results 1 to 6 of 6

Box and Whisker chart with Current level dot points

  1. #1
    Registered User
    Join Date
    10-07-2020
    Location
    new jersey
    MS-Off Ver
    2019
    Posts
    55

    Box and Whisker chart with Current level dot points

    Hello Excel Community.

    I have built Box and Whisker chart with Current Level Dot points as shown in Attached excel sheet. The problem is that Five number summary does not tally with Chart that is built.

    Please help Me!!!

    Thanks
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by haroonkhurshid; 12-24-2020 at 01:52 PM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Box and Whisker chart with Current level dot points

    Who, what or where is "Five number summary..." to be found??
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Box and Whisker chart with Current level dot points

    If I understand, the problem is that your stacked column "box" chart has both positive and negative numbers, and Excel's chart engine does not handle mixed positive and negative values the way you are expecting.

    I don't have time right now to research and fiddle with this for you (maybe someone else will come along). When I have seen this talked about, it has been in the context of stacked column "waterfall" charts (https://peltiertech.com/excel-waterf...bridge-charts/ ). You might spend some time with the waterfall chart tutorial to understand how Excel handles mixed positive/negative data in a stacked column chart. I see two possible solutions:

    1) Figure out how to build the mixed positive/negative stacked column so that the box plot turns out correctly.
    2) Translate the "chart" data so it is all positive (or all negative) then hide the chart's axis and add a "dummy" series axis.

    My apologies for not being able to walk you through the complete solution, but that should explain the problem and point you towards finding a solution.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    10-07-2020
    Location
    new jersey
    MS-Off Ver
    2019
    Posts
    55

    Re: Box and Whisker chart with Current level dot points

    Hi,

    Excel file contains the yellow highlighted cell which are five number summary for each of the equity. I also inserted box and whistle example image.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Box and Whisker chart with Current level dot points

    Here's an idea (I don't have access to Excel, so I cannot readily test it, but I think this should work):

    1) "Transpose" the box data up 10 units by adding 10 to the box1 row and making rows 14:15 positive.
    1a) I entered 10 into A13, then edited the formula in I13 to be =I7+$A13 (copy across). I put 10 into its own cell, so that I can easily change it if I want to try a different value.
    1b) Remove the "-" from the front of the formulas in row 14 and 15. That should give the correct size boxes, but 10 units higher than the raw data suggests.
    2) I have not changed anything about the "current series", so these should still match the raw data. Select chart -> select "current" series -> format -> move to secondary axis.
    3) The "difference" between the primary and secondary axes should be the 10 units that I used to translate the box data, so I format the secondary axis (and primary axis as needed) so that both axes span the same range and are 10 units different. I ended up choosing to format the primary axis to have max of 16 and min of 2 and the secondary axis to have a max of 6 and a min of -8.

    At this point, if you ignore the primary axis and read everything on the secondary axis (eventually, I would format the primary axis to have no axis labels), I think it should be mostly correct. There may still need to be some adjustments to formulas and formatting to get it exactly right, but that should be substantially closer to correct.

    Will something like that work?

  6. #6
    Registered User
    Join Date
    10-07-2020
    Location
    new jersey
    MS-Off Ver
    2019
    Posts
    55

    Thumbs up Re: Box and Whisker chart with Current level dot points

    Hi,

    The trick of the transpose worked. I did that and it came out as it should.

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Adding overlay data points to a box and whisker plot
    By BryceM in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-11-2019, 09:37 AM
  2. Format the second level of multi-level category axis - clustered chart
    By mushkitoes in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-04-2018, 09:30 AM
  3. Whisker Chart w. Tails
    By Number7stunner in forum Excel General
    Replies: 7
    Last Post: 02-17-2017, 07:03 PM
  4. Box and whisker chart, source from table
    By bradleyisaac in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-10-2015, 10:45 AM
  5. Box-and-whisker chart (box plots)
    By James in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-07-2011, 01:01 PM
  6. Bar chart with whisker
    By kvonm in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 11-12-2006, 11:19 PM
  7. [SOLVED] box-and-whisker chart
    By Jan Heijs in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-10-2005, 09:06 AM

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