punchandpiearecoo
New Member
- Joined
- Apr 25, 2006
- Messages
- 3
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
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
