## I need to create bollinger bands in Numbers

746 Views 17 Replies Latest reply: May 8, 2012 10:46 AM by Jerrold Green1
Previous Next
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.

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)
• 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.

• 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

• 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):

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

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

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)

• 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?

• 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

• 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).

• 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

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

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.

• 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:

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:

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

Regards

Jerry

Jerry

Previous Next

#### More Like This

• Retrieving data ...

#### 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.