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)
I've asked the mods to move this to the Numbers Forum where all the experts will see it.
I've asked the mods to move this to the Numbers Forum where all the experts will see it.
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
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)
this would work if I my chart were a finished product. My chart, however, is a living document.
Thanks!
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?
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
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.
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
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).
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
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
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.
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 |
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
is there a way to fill the area between the MA's (even where it goes negative). For instance, an equality? ie
"Data + STDEV>x>Data - STDEV"
I need to create bollinger bands in Numbers
