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

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

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.

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?