Skip navigation

I need to create bollinger bands in Numbers

746 Views 17 Replies Latest reply: May 8, 2012 10:46 AM by Jerrold Green1 RSS
1 2 Previous Next
Five_point Calculating status...
Currently Being Moderated
May 3, 2012 12:18 AM

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)
    Currently Being Moderated
    May 3, 2012 3:03 AM (in response to Five_point)

    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 (28,190 points)
    Currently Being Moderated
    May 3, 2012 5:45 AM (in response to Five_point)

    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 (12,650 points)
    Currently Being Moderated
    May 3, 2012 1:50 PM (in response to Five_point)

    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)

  • Wayne Contello Level 6 Level 6 (12,650 points)
    Currently Being Moderated
    May 3, 2012 6:16 PM (in response to Five_point)

    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?

  • Jerrold Green1 Level 7 Level 7 (28,190 points)
    Currently Being Moderated
    May 3, 2012 6:29 PM (in response to Wayne Contello)

    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 (12,650 points)
    Currently Being Moderated
    May 3, 2012 6:32 PM (in response to Five_point)

    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 (28,190 points)
    Currently Being Moderated
    May 3, 2012 6:37 PM (in response to Five_point)

    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

  • Wayne Contello Level 6 Level 6 (12,650 points)
    Currently Being Moderated
    May 3, 2012 6:51 PM (in response to Wayne Contello)

    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.

  • Jerrold Green1 Level 7 Level 7 (28,190 points)
    Currently Being Moderated
    May 4, 2012 6:44 AM (in response to Five_point)

    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

1 2 Previous Next

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.