Expert: Ahmed Salami - 7/11/2004

Question
Hi, thanks for taking the time to look over my issue. I'm using excel to try to calculate standard deviations, but unfortunately the excel geeks aren't sure how to do it. I've created a pictural representation of what I'm trying to achv but I'm going to explain it as well. As you can see I have two columns of data, price and volume. I'm trying to figure out where the center 70% of the volume range is located (70% = 1 stnd deviation)...what are the price boundries. I'd like to do this with 2 standard deviations as well (95% of the volume).

Price Volume
1000, 240
1001, 1000
1002, 1004
1003, 1129----- upper limit of 2 stnd dev
1004, 995
1005, 1500
1006, 2766----- upper limit of 1 stnd dev
1007, 2209
1008, 3994
1009, 2900
1010, 3499
1011, 2388
1012, 1388
1013, 1909
1014, 2133----- lower limit of 1 stnd dev
1015, 1788
1016, 988------ lower limit of 2 stnd dev
1017, 701
1018, 239

These upper and lower limits aren't accurate they are just displaying what I'd like to be able to do. My end result would tell me that the range of 1 stnd dev = 1006 - 1014, and the range of 2 stnd dev's = 1003 - 1016. Don't worry about an excel formula if you don't use excel. At this point I'll take any help I can get. Thanks for your time.

Namaste, Josh  

Answer
Hi Josh,
I'm sure you are terribly disappointed. I had a problem with my pc, i'm really sorry.
Your question has to do with Normal Distribution where data (volume in your case)are converted to standard z scores. The center 68.27% of the volume range would now be located between z values 1 unit to either side of the normal distribution curve. The center 95.45% of the volume range would now be located between z values 2 units to either side of the normal distribution curve.
But first you need the mean and the standard deviations of the data, you then convert data to z scores and then complete the work.
If you, however want me to do everything for you, then just let me know.
I'm never like this.
Regards.