# Thread: Tricky Std Deviation, Kurtosis, etc calculations (2002)

1. ## Tricky Std Deviation, Kurtosis, etc calculations (2002)

Hello All,

Here is my problem. If this is not enough info and you need more to help me out please respond and I will do so promptly! OK:

I have a set of data in one sheet. This data involves a large amount of numbers and each number is linked to a "strategy," there are about 10 strategies. In another sheet I have linked a few rows back to the relevant data from sheet 1, including which "strategy" is used for each sequence of data. On this second sheet I am calculating a Weighted Mean, Weighted Variance, Weighted Standard Deviation, Weighted Kurtosis, and Weighted Skewness. I have been able to do this individually for the entire data set, for one strategy, and a combination of two or three. However, when I try to simply filter the strategies that I want in this second sheet the formulas kind of rearrange and I have to do the calculations individually for all combinations of strategies. How do I make it so I can simply filter out whichever combination I need and always get the std. deviations, means, variances, etc that fit only the strategies that I filtered in? Thanks!

2. ## Re: Tricky Std Deviation, Kurtosis, etc calculations (2002)

Could you post a sample workbook or a stripped down copy of your workbook with dummy data so that we have an idea what you're working with?

3. ## Re: Tricky Std Deviation, Kurtosis, etc calculations (2002)

Yeah sure. Thanks Hans! P.S. I am further trying to plot the individual strategies on graphs and scatter plots, and smooth out the curves for the graphs in particular, ive done some work on it and I will attach another spreadsheet if you are interested in taking a look at it. Thanks again!

4. ## Re: Tricky Std Deviation, Kurtosis, etc calculations (2002)

The first post is the data, next post it excel calcs for std. dev, etc. I replaced the = with # so that it would not be too big for the site.

5. ## Re: Tricky Std Deviation, Kurtosis, etc calculations (2002)

Hello again, here is some work me and a buddy did on the graph. Its a bit much but mainl we tried to plot the same type of sample set of data onto a graph to show the std dev ranges, and the volumes at the various deviations through a curve. The graph you will see is very rough and because of lack of data, however with a larger set it looks a bit more coherent. However, still very spiky so we had to do some averaging to smooth it out. I hope this helps you help me! Thanks!

Actually I am having trouble posting it because the file is too big. Maybe I can email you?

6. ## Re: Tricky Std Deviation, Kurtosis, etc calculations (2002)

You can zip the workbook and attach the zip file.

BTW, I won't have time to look at the workbooks until later. Perhaps someone else will react sooner.

7. ## Re: Tricky Std Deviation, Kurtosis, etc calculations (2002)

Sure I will do that. Thanks for replying anyway! I do have some time on it though so if you do get a chance please feel free! Cheers.

Mike

P.S. I am at work and no zipping, I will try to figure something out though

8. ## Re: Tricky Std Deviation, Kurtosis, etc calculations (2002)

<P ID="edit" class=small>(Edited by sdckapr on 08-Jul-08 09:24. Oops forgot the "-3" in the Kurtosis...)</P>How about this?

I have a combobox to select each one, but I also created a table to display the values for all at once. [the combobox uses the first column in the table for its selctions...]

If you want to do it based on fitering, I think it will take a user defined function to check for visible values...

Steve

9. ## Re: Tricky Std Deviation, Kurtosis, etc calculations (2002)

I am not sure what you are charting, but to look at the distributions you can use Box/Whisker plots. The box contains 50% of the data (25% in each section). The line in the box is the median. Each whisker represents 25% of the data.

I did this using the unweighted percentiles since I could get these directly. I think to calculated a weighted percentile would require a created function.

Steve

PS I added a "count column" in the data extract as well to have the number of points...

10. ## Re: Tricky Std Deviation, Kurtosis, etc calculations (2002)

Hi all AND Steve,

Thanks Steve, that looks interesting but not quite what I am trying to do, maybe I did not explain myself much.

I have a set of data which I set up to give me the weighted std. deviations from the data. I now have to put the Std. Deviation curve onto a graph, given a certain range for the values on the x axis, and a percentage of "order" weighted by value on the y axis.

The way I have it set up is that every strategy is located on a separate page with its own volume (size) and own data points. I am trying to plot a std. deviations curve for each of the individually on separate pages. I need to have the "Price Difference" on the x axis (Bottom) as a range so lets say from
-200 through to +200 (Each one of these being a "bin" for the data that has a "price difference" equal to the bin number which may cut out some of the data points. This data has to be weighted. I have a basic idea of how to do this: one column that would count the percentage of "total value" that is available from bin -200 and on, and then a second column calculating how much is left from bin -199 and on, then a third column that would subtract the two to determine how much of the volume was between bin -200 and -199.. and so on dragged down the column..Sounds good in theory to me but I have been unable to make it work

11. ## Re: Tricky Std Deviation, Kurtosis, etc calculations (2002)

You can create bins of this type with the FREQUENCY function. How you would use it exactly I am not sure of since I seem to not understand what you are after completely.

If you need more help, could you provide a simple example worksheet of what you have and what you want the chart to look like. Perhaps 2 or 3 strategies in the separate sheets with some representative datasets. If you can't make the chart you want exactly in EXCEL, you could draw a sample in powerpoint and just explain what each item represents and how it should be calculated...

Steve

12. ## Re: Tricky Std Deviation, Kurtosis, etc calculations (2002)

Thanks for the help steve! I am attaching a word document with a screen shot of the graph ideally, and a sample set of some data for a strategy...I am also attaching an excel spreadsheet of what I have done so far. The = are replaced with # in this one so you would have to simply switch them around again... Thanks!

13. ## Re: Tricky Std Deviation, Kurtosis, etc calculations (2002)

and here is the word doc..

14. ## Re: Tricky Std Deviation, Kurtosis, etc calculations (2002)

I don't follow how the data in the word file comes from the excel sheet and how all the extra things in the XL file are related.

This charting question also seems to be unrelated to your original query on the calculations for the weighted statistics (which has already been provided) and thus no need for the intermediate calculations]

If your current question is about plotting a weighted distribution, what data do you want plotted in the excel sheet? Do you want the data in Bins (which to me suggests a Column Chart, not a XY) or plot each point individually?

Steve

15. ## Re: Tricky Std Deviation, Kurtosis, etc calculations (2002)

Hey Steve,

The data (meaning, strategy, total value, and price difference) in the word file is different from that in the excel, but it does not really matter. It is ok to plot either one for the graph, its up to you. The two data sets are unrelated.

The data that needs to be plotted are the individual "price differences" weighted according to their total value. Which ever way you think it may be best to represent it would be great, but I think the curve better represents the data in relation to 0, expressing the idea of std. dev.

P.S. I think I confused myself with exactly what I was asking previously. Basically i need a chart that shows how the "price difference" deviates from 0, and weighted...

Thanks sorry I can explain better..

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•