Help graphing background standard deviation bands.

[brightw]

Hey all,

I know how to graph background bands, but I don't know how to plot it so that the mean is in the middle of the graph and the standard deviations are evenly spaced apart. If possible, I would love to see the std. deviations along the right hand side vertical axis as well. I've attached the graphs and relevant data; and an example of what I want it to look like.

Thank you very much!

example: http://peltiertech.com/Excel/pix5/HorizBands09.png

-brightw

Wagner Model 1.xlsx

[MrShorty]

Have you been through Jon's accompanying tutorial that goes with the attached picture? http://peltiertech.com/WordPress/exc...izontal-bands/
Anything specific that you had trouble understanding?

[brightw]

Hi MrShorty,

Thanks for your response. I have, but it doesn't seem to teach me how to start the bands at the mean of the entire graphical data set, rather at the bottom and in intervals of specific numbers rather than evenly distributed standard deviations.

-brightw

[MrShorty]

In glancing through it, I don't think Jon intended for this to be a tutorial about calculating means and standard deviations. Assuming you know how to calculate these quantities, it should just be a matter of substituting the appropriate formulas/quantities for the values he shows in columns F:H of his sample spreadsheets.

1) Start by calculating average and standard deviation and how many standard deviations you want to show.
2) From these values calculate the Y value for the lowest band. Continue for each subsequent band until you get to the upper band.
3) Apply to chart as explained.

[brightw]

Y Value for the lowest band meaning each standard deviation, correct?

If not, how would I go about calculating it? =Min(#:#)?

[MrShorty]

I don't think that is quite correct.

Start at the x axis and count up. Assuming you want to show bands for up to 2 standard deviations. The first band will be everything more than two standard deviations below the mean. The top of this band will be 2 standard deviations away from the average (average-2*stddev). The second band will represent everything between 1 and 2 standard deviations, and its top will be average-1*stddev. The third band will be between the average and 1 standard deviation below, and its top will the average. And so on until you also have the two bands above the average.

[brightw]

I still can't quite seem to grasp my head around it. I've followed your directions multiple times, but the standard deviations are always off in the end. At least one is always off. Could you provide an example by any chance?

[MrShorty]

I probably could, but I expect it will be easier to work from whatever you have now. Especially if the bands are there and just "off" by a little, it should just be a matter of determining which one is off and what n (average+/-n*stddev) to multiply it by to get it where it should be. Can you upload your current workbook with the bands as you have them?

[brightw]

I can't because I'm not allowed to access this website while at work and I can't email documents to external email addresses from my place of work (I'm interning with a bank and they are very sensitive when it comes to privacy and complacency). I'm very sorry, I wish I could.

[MrShorty]

Using the Wagner model spreadsheet in post 1, I put together bands for a subset of one of the data sets.

I observe that there will be some difficulty when the stdev is large so that it wants to put bands below 0. one will need to spend some time figuring out how to get that part right.

Perhaps with this as a starting point, you can explain where you are getting stuck.