# Trouble with negative numbers in box and whisker chart

#### punchandpiearecoo

##### New Member
I used this quick tutorial from Peltier Tech in order to do a box and whisker plot. The problem for me is when my ranges include negative numbers. I'm dealing with investment returns (so one category is "1st quarter", one is "Year to date", etc.). When using the build-up method to create a box-and whisker, all sorts of problems come up when negative numbers get involved for the columns. For instance, here are the numbers I'm using:

For the periods 3Q 06, 2Q 06, Year To Date, and Since Inception:

75th percentile returns: 5.0%, -5.3%, -2.24%, 17.44%
25th percentile retunrs: -8.0%, -11.04%, -8.24%, 5.96%

Essentially I just want a "visible" bar that stretches from the 25th percentile to the 75th percentile and then I can add error bars that stretch from the bottom and top of the bar to the min and max returns for the period as well.

As such, it's pretty simple to construct when both numbers are negative; for 2Q for instance, the bar that stretches from 0 to -5.3% can just be made invisible and problem's solved. Similarly for the 3Q bar, in which nothing needs to be made invisible. My problem is that when all these different data series are combined into the one graph, Excel doesn't always put the data point from one particular series consistently on the bottom (i.e. for 2Q and YTD the bar I calculated to be the differential between 75th and 25th percentiles is appearing on top, where I want it to be on the bottom, but it appears correctly on the bottom for since inception - further, though, the since inception is now building up to 5.96% PLUS 17.44% which is not what I wanted, just 5.96% TO 17.44%.)

Any help would be tremendously appreciated.

Thanks

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Replies
1
Views
628
Replies
3
Views
1K
Replies
0
Views
3K
Replies
1
Views
6K
Replies
1
Views
764

1,207,256
Messages
6,077,324
Members
446,278
Latest member
hoangquan2310

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back