Previous 1 2 Next 17 Replies Latest reply: May 8, 2012 10:46 AM by Jerrold Green1
Five_point Level 1 Level 1 (0 points)

I am trying to chart a performance margin on a chart.  I tried to chart a shaded area around a moving average using plus an minus STDEV's but the problem is that the STDEV cant show a negative number and this moving average travels into negative territory.  Below is what i have. 

screen-capture-1.jpgscreen-capture-3.jpg
for the moving average:  =SUM(X815:X838)/25
For the top and bottom areas for the shaded area: =AC839+STDEV(AC815:AC838)
                                                                                         =AC839-STDEV(AC815:AC838)


iMac, Mac OS X (10.6.7)
  • noondaywitch Level 6 Level 6 (8,130 points)

    I've asked the mods to move this to the Numbers Forum where all the experts will see it.

  • Jerrold Green1 Level 7 Level 7 (29,935 points)

    FP,

     

    I'm making the assumption that you are using a dual mode Category chart with the Stacked Bar type for the shaded area and LIne type for the moving average. I don't have an answer for how you can prevent the chart from reassigning the data order when the sign changes on the data in the stacked bar. My suggestion is to aply an offset and chart the entire business in positive territory and then fudge the value axis label to make it appear to chart into the negative. You can do this with some graphics.

     

    Regards,

     

    Jerry

  • Wayne Contello Level 6 Level 6 (15,490 points)

    Assuming you know how to create the high and low portions of the band you can make three graphs which you superimpose (place on top of each other).  If you format the fill color of the top graph (the closing price and moving average) as "No fill" then you will be able to see the other two graphs (the high and low) of the bands.

     

    I would make the upper band the first chart (as an Area Chart):

    Screen Shot 2012-05-03 at 3.42.30 PM.png

     

    Then place the lower band on top (as an Area Chart), selec the chart and make the fill for the chart "No Fill":

    Screen Shot 2012-05-03 at 3.42.06 PM.png

     

     

    Then place the graph of the closing price and Moving Average on top (again, with no fill):

    Screen Shot 2012-05-03 at 3.41.43 PM.png

     

    To make the area charts you do have to reverse the order of the data for the area (compared to the scatter chart I used for the closing price and moving average)

  • Five_point Level 1 Level 1 (0 points)

    this would work if I my chart were a finished product.  My chart, however, is a living document. 

    Thanks!

  • Wayne Contello Level 6 Level 6 (15,490 points)

    Why does a living document change the concept?  The chart is created from data in tables.  If the data changes so does the chart.  Right now I don't know what you are requesting help with.... so I threw out and idea.  If I didn't address the problem can you help focus me to the real problem?

  • Five_point Level 1 Level 1 (0 points)

    This is exactly what I have, only I have it all in one chart.  The problem is that The Standard Deviations that I am using to creat the shaded region will not give me a negative number when my moving average yeilds a negative number.  I need an alternative to Standard Deviations. 

    for the moving average:  =SUM(X815:X838)/25

    For the top and bottom areas for the shaded area: =AC839+STDEV(AC815:AC838)

                                                                                              =AC839-STDEV(AC815:AC838)

     

    I think the Key Point here is that the line i'm plotting moves into and out of negative territory

  • Five_point Level 1 Level 1 (0 points)

    wayne,  my previous response about the living document was to Jerrold's idea

     

     

    What i have is a profit and loss blotter for an active trader.  For this particular appplication I am charting the standard deviation of the trader's profits and losses in relation to the risk taken per trade. 

  • Jerrold Green1 Level 7 Level 7 (29,935 points)

    Wayne,

     

    I think you may have missed that FP has data going into negative values. Perhaps if your illustration included this condition it would be easier for FP to evaluate.

     

    Jerry

  • Wayne Contello Level 6 Level 6 (15,490 points)

    1) you can use average(<RANGE>) instead of SUM(<RANGE>)/<SIZE OF RANGE>

    2) you can offset the standard deviations so bands are always positive by:

    For the top  areas for the shaded area: =AC839+STDEV(AC815:AC838) + OFFSET

                       bottom areas =AC839-STDEV(AC815:AC838) + OFFSET

     

    The OFFSET is a value you adjust from a set up table (perhaps new to your document).

  • Jerrold Green1 Level 7 Level 7 (29,935 points)

    FP,

     

    Your problem is a chart problem, not a function problem. Using another dispersion/deviation statistic won't solve it.

     

    If you want to explore the chart options we'll need the specifics on how you are making the chart.

     

    Jerry

  • Five_point Level 1 Level 1 (0 points)

    You guys are awesome,  I have to go back to work (hawaii)  I will attach something when I get home again. 

    we're almost there

     

    Jason 

  • Wayne Contello Level 6 Level 6 (15,490 points)

    Screen Shot 2012-05-03 at 8.49.32 PM.png

    This is possible but to me it too much work to coerce this behavior.  There are problems with offsetting because the range of the graphs for the bands and the scatter chart need to be different.  There is a better program called DataGraph that I really like.

  • Five_point Level 1 Level 1 (0 points)

    Below is two hundred values from one of my data sets(there is more than 800 so far) .  These numbers are represent a ratio of the profits or losses in relation to the risk taken in a financial position. 

    The initial picture that i posted showed this data as bars.  The 25 period average of these numbers in a red line [e.g. =SUM(X13:X36)/25]  the shaded area in blue is the MA+25 period STDEV and the white-out portion is the MA- 25 period STDEV

     

     

    P/L%

    9.30

    3.59

    1.55

    -0.857

    -1.26

    -1.04

    -1

    -1.07

    1.80

    -1.05

    2.50

    -0.937

    -0.38

    0.176

    -0.0476

    -2.4

    -1

    2.87

    3.72

    -2.5

    -5.00

    2.54

    -1

    -1.19

    -0.115

    1.64

    0.167

    -2.69

    -0.375

    36.2

    0.444

    -0.00000000000

    -0.406

    -1.33

    -0.771

    9.54

    0.148

    -1.05

    2.89

    -0.800

    0.735

    -1.88

    -0.667

    2.3

    -2.38

    -1.1

    -1.29

    -0.105

    -0.0235

    -1.83

    -0.333

    -3.25

    -0.667

    -1.06

    -2.10

    1.23

    -0.964

    0.100

    -0.898

    10.6

    -0.480

    0.696

    -2

    -0.138

    13.6

    -0.812

    -0.727

    0.789

    -1

    4.57

    -0.477

    -0.778

    -1.78

    -0.189

    -1.29

    3.85

    -2

    -1.54

    -1.53

    -1.09

    -1.3

    -0.78

    -0.722

    -2.20

    0.400

    -2.25

    0.0852

    -0.286

    0.883

    -2.75

    5.17

    0.286

    -1.20

    -0.389

    -1.96

    -1.33

    -4.31

    8.03

    -0.0262

    -3.00

    -2.33

    -0.842

    1.44

    0.250

    -1

    -1.11

    -1.69

    -0.889

    -0.685

    -0.800

    -0.778

    -3.17

    -7

    1.02

    -1.10

    -1

    -2.00

    -1.20

    1.8

    -1.25

    -0.667

    -2.08

    -0.944

    0.385

    1.78

    -1.60

    -1

    -3

    0.645

    -2.42

    -0.00353

    -1.24

    -0.400

    -1.21

    0.551

    -1.25

    0.333

    4

    -0.227

    0.0769

    1.93

    -0.135

    -1.80

    2.85

    4.80

    -3.50

    0

    1.92

    -1.57

    1.50

    3.00

    6.82

    0.725

    -0.566

    -1

    -1

    -1

    2.52

    -0.800

    -0.444

    2.58

    -0.333

    -0.154

    2.3

    1.75

    0.47

    1

    -1.6

    -2

    -0.553

    -0.778

    -0.923

    -0.429

    -1.03

    -0.383

    -0.923

    -3.20

    -2.4

    -6.50

    -0.778

    -3.25

    -2.22

    -0.667

    -0.0625

    1.67

    -0.333

    -3.50

    -0.519

    -2.93

    -5.08

    -2.70

    -2.80

    -1.67

    1.16

    -0.941

    -0.136

    -1.66

    5.67

    -0.286

    -1.05

  • Jerrold Green1 Level 7 Level 7 (29,935 points)

    FP,

     

    I charted your data and got this result:

    Screen Shot 2012-05-04 at 9.35.32 am.png

     

    As you can see, I used normal (simple) Line Chart marks for the Average and "Limits" and Bars for the individual data points. Doing the shading is a complication that you may choose to forgo in the interest of simplicity. It would be easy to shade the limits in this particular case, but it would fall apart if the data were different.

     

    It would look like this if you chose Area Fill for the limits:

     

    Screen Shot 2012-05-04 at 9.42.51 am.png

     

    It only works because the limit lines don't cross through zero.

     

    Regards

     

    Jerry

     

    Jerry

Previous 1 2 Next