I have lots of data in the form of a histogram. Example:

A1 = 0-10 range, B1 = 500 counts in that range

A2 = 11-20 range, B2 = 345 counts in that range

A3 = 21-30 range, B3 = 756 counts ...

and so on.

Is it possible for me to calculate the standard deviation? for these 1601 data points?

Thank you.

A1 = 0-10 range, B1 = 500 counts in that range

A2 = 11-20 range, B2 = 345 counts in that range

A3 = 21-30 range, B3 = 756 counts ...

and so on.

Is it possible for me to calculate the standard deviation? for these 1601 data points?

Thank you.

- Calculate Monthly Standard Deviation From Daily Data
- Calculating Returns and Standard Deviation from a data sample of share prices.
- Calculate one standard deviation from mean
- Up/Downside standard deviation
- How to calculate Mean, Median, Mode and standard deviation in group data?
- Median, Average, and Standard Deviation from large set of data
- Monthly Standard deviation for daily returns
- Average With Standard Deviation In Pivot Graph/Chart
- Create Standard Deviation From Array
- Selectively Calculating Standard Deviation Using Array Formulas or Database Functio
- Standard deviation calculations with excel
- Excel Charts: Can I show standard deviation on selected series?
- Calculate Standard Deviation By Cell Color
- Log standard deviation
- Standard Deviation Help Please??
- Standard Deviation question
- MS Excel: Standard Deviation Conditional Calculation
- Daily Standard Deviation
- Calculating Standard Deviation for certain numbers only
- How to calculate Standard deviation of product of numbers?
- Calculating AVERAGE DEVIATION from a SPECIFIED POINT??
- Standard deviation questions
- How do you find the SD(standard deviation ) for the multiple reg
- How do you find the SD(standard deviation ) for the multiple reg

I've daily data of a stock indices returns and I would like to calculate the monthly standard deviation.

Currently, I'm using the following worksheet functions:

=STDEVP(C2:C20)*SQRT(COUNT(C2:C20))

However, the range changes from month to month, which makes the process of calculating the monthly standard deviation to be quite tedious if I've about 10 years worth of data.

I assume I could somehow substitute the range with a dynamic range, but I'm struggling to come up with the correct formulation that would do that.

Any advice is very much appreciated. Thanks.

I need some help urgently. From the Shares Data attached,I wanna calculate the following using VBA specifically:

1.Daily Returns

2.Daily Standard deviation of return of each share

3.Variance- Covariance matrix of the daily returns.

I hope you guys can help!

Thanks a mil!

91.6

90.9

92.4

92.5

Can anyone help? Thanks so much!

Any idea on how to compute a up/downside standard deviation from a data series?

Also, a harmonic and geometric mean with negative values?

Many thanks,

I am trying to create a worksheet capable of calculating the turnaround processing time for emails in a 14 days time frame.

I am wondering if it is possible to create a dynamic excel worksheet which can calculate Mean, Median, Mode and standard deviation within a range of data as Column B listed below changes every forthnightly.

Sample data below:

Column A Column B

Number of days Email

O – 1 600

2 - 3 700

4 - 5 100

6 - 7 50

7 - 14 30

- First row: 600 emails took a day to complete

- Last row: 30 emails took between 7 - 14 days to complete

1. To find the average processing time

2. To find the median processing time in days

3. What is the standard deviation (in days)

Would greatly appreciate if someone could help.

I apologise if I was unable to articulate the whole scenario clearly.

Many Thanks!

I'm stumped. I have a very large set of data (over 200.000 records in

MSAccess DB) that I'm analyzing using Pivot Tables. Average and Standard

deviation functions are "built-in" so no problem there... now, how can I

calculate the median???

Please help!

Thanks in Advance

I am trying to compute the monthly standard deviation from the daily returns across large data of a stock. Can anyone help me with the vba code to calculate automatically. I want the vba macro not the functions for the project.

Thanks

My problem: In particular, I have a pivot-table with all "amount of data-sets", "average" and "Standard Deviation" of my data, which I'd like to visualise in a pivot-chart. I would like to have the pivot-chart show a xlColumnClustered Chart with the "average" and the "Standard Deviation" (as y-errors of the average!!!) - and if possible the "amount of data-sets" in/on/next to the correspondent column. I don't think that is something new or unusual, but I simply haven't found out, how to do so at all (in VBA).

Reached so far: What I have been able to encode is the establishment of the pivot-table and the chart (see code below). But I cannot get the chart to show the "Standard Deviation" as a y-error of the "average"-values! (Also, but this is less important, I neither get to show the "amount of data" ON the correspondent column.) For a usual chart, I do know how to add the y-error ... but for the pivot-chart, I don't know how to do so, cause relying on defined data-ranges for the chart, would not be a good idea in case somebody simply rearranges the pivot-chart, I fear.

Below you find the code, I have been able to make up so far, I appreciate any help,

Andy

[EDIT] I know, there is the button to add a "y-error" where you can choose from. But it does not contain the standard deviation (sample), whereas this is exactly what I can calculate in the table ... but I dunno how to make the standard deviation (sample) appear as the y-error in my chart.

VB:, strNFormat) ' ' This macro creates a pivot-table and a pivot chart based on the data in rPivot. ' wks: worksheet of rPivot ' rPivot: range of data for pivot-table ' strTblNme: future name of the pivot-table ' arrData: array field contains four infos about every field ' arrRow: array field, contains info about the row field ' arrColumn: array field, contains info about the column field ' strNFormat: (mostly = "0.00") '------------------------------------------------- Dim wkbNow As Workbook Dim rStart, rUsed As Range Dim myPiv As PivotTable Dim pTable As Worksheet Dim pChart As Chart Dim b As PivotField Dim i As Integer Dim e, f, g As Variant '------------------------------------------------- '--------------------------------------------------------- ' Clean up and delete old pivotTable and pivotChart '--------------------------------------------------------- Set wkbNow = ActiveWorkbook Application.DisplayAlerts = False If wsExists(wksPivotTable, wkbNow) Then wkbNow.Sheets(wksPivotTable).Delete On Error Resume Next wkbNow.Charts(wksPivotChart).Delete On Error Goto 0 Application.DisplayAlerts = True '--------------------------------------------------------- ' Add Pivottable '--------------------------------------------------------- wkbNow.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=wksIndexName & "!" & rPivot.Address(ReferenceStyle:=xlR1C1)).CreatePivotTable TableDestination:="", TableName:=strTblNme, DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) Set pTable = ActiveSheet pTable.name = wksPivotTable Set rStart = pTable.Cells(3, 1) Set myPiv = pTable.PivotTables(strTblNme) '--------------------------------------------------------- ' Fill Pivottable '--------------------------------------------------------- With myPiv '------------------------------------------------- 'Add RowField Variables '------------------------------------------------- For Each g In arrRow With .PivotFields(CStr(g)) .Orientation = xlRowField .Position = 1 End With Next g '------------------------------------------------- 'Add ColumnField Variables '------------------------------------------------- For Each f In arrColumn With .PivotFields(CStr(f)) .Orientation = xlColumnField .Position = 1 End With Next f '------------------------------------------------- 'Add DataFieldContent '------------------------------------------------- For Each e In arrData If e(1) Then .AddDataField pTable.PivotTables(strTblNme).PivotFields(CStr(e(0))), "Data-sets of " & CStr(e(0)), xlCount If e(2) Then .AddDataField pTable.PivotTables(strTblNme).PivotFields(CStr(e(0))), "Mean of " & CStr(e(0)), xlAverage If e(3) Then .AddDataField pTable.PivotTables(strTblNme).PivotFields(CStr(e(0))), "StDev " & CStr(e(0)), xlStDev Next e '------------------------------------------------- 'Set Number format for every data-field '------------------------------------------------- pTable.UsedRange.NumberFormat = strNFormat End With '--------------------------------------------------------------- ' Add Pivot Chart '--------------------------------------------------------------- Set pChart = Charts.Add With pChart .SetSourceData Source:=pTable.Range("B4") .Location Where:=xlLocationAsNewSheet .ChartType = xlColumnClustered 'chart type .name = wksPivotChart End With End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I am fairly new to VBA, i require some help creating a Standard Deviation UDF using arrays, i have made a userform that allows a user to select anywhere between 1 to 30 companies (A1: AD1) and its corresponding stockprices (A1:A60) in a sheet called companydata, using the following sub, my problem is that i am unsure how to create a function using the info the user selects. I have also created a returns function (below) but this is vba is saying that there is a out of range error, Will anyone be able to help please.

Below is the code i am using to allow for my sub:

VB:) Dim NumofDataPoints As Long Dim NumSelectedComapanies As Long Dim i As Integer Dim j As Integer Dim w As Worksheet Set w = Worksheets("CompanyData") With UserForm1.ListBox1 For i = 0 To .ListCount - 1 If .Selected(i) Then NumSelectedComapanies = NumSelectedComapanies + 1 End If Next i 'size of array NumofDataPoints = 60 Redim result(1 To NumSelectedComapanies, 1 To NumofDataPoints) As Double ' to fill array with data from CompanyData worksheet Dim count As Integer count = 0 For i = 0 To .ListCount - 1 If .Selected(i) Then count = count + 1 For j = 1 To NumofDataPoints result(count, j) = w.Cells(j + 1, i + 1) ' i used to correspond with NumSelectedComapanies Next j End If Next i End With End Sub '********************************* Function returns(a() As Double) As Double() Dim ret() As Double Dim i As Integer Dim j As Integer Dim w As Worksheet Set w = Worksheets("CompanyData") Redim ret(1 To 30, 1 To UBound(a)) As Double For i = 1 To UBound(a) For j = 1 To UBound(a) ret(i, j) = Log * (w.Cells(j + 2 / j + 1)) Next j Next i returns = ret End Function '**************************** ' this is how i am calling the above in my userform Dim result() As Double, k As Integer, j As Integer Call Test(result) Dim stockret() As Double stockret = returns(result) For k = 1 To UBound(result) For j = 1 To UBound(result) Worksheets("Sheet1").Cells(1 + j, 1 + k) = stockret(k, j) Next j Next k End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Would someone be able to help me with standard deviation within excel? I have used the formula =STDEV(number one, number two...etc). I think the problem is that my data is not a normal distribution, thus the incorrect standard deviation value. But I need to some how calculate standard deviation? Is there a way that I can with excel? I have over 20 000 data elements, thus making it harder to work out. Could someone please suggest some ideas that I could possibly implement to help correctly calculate standard deviation?

Column 1 has labels

Column 2 has data

Column 3 has calculated Standard Deviation

I need to show one cell from column three as a standard deviation on one of

the data items in Column 2. Can this be done?

ColorFunction is a great tool, but I would like to use it to calculate the Standard Deviation of color filled cells. Can the following code be modified to do this?

Thanks for your help.

VB:Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult '''''''''''''''''''''''''''''''''''''' 'Written by Ozgrid Business Applications 'www.ozgrid.com 'Sums or counts cells based on a specified fill color. ''''''''''''''''''''''''''''''''''''''' lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell,vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks

I don't know too much about standard deviation, but I was wondering if somebody could help me with a function to find 1 standard deviation (68%??) from the largest value (not the mean value) within a column of values. What i'm really after is the 'standard deviation boundaries' either side (i.e. 34% of values above the largest value and 34% below the lowest value) as a highlight.

im using excel mac 2008 so no vba im afraid.

here is an example of real data:

0

3,244

71,488

131,887

175,025

166,642

215,774

280,951

314,720

258,964

232,864

222,386

109,465

116,129

39,505

80,128

171,146

145,920

133,806

211,861

262,766

372,249

508,658

Can someone please help me with my query? I have attached my file ("STDEV.xls").

I have a large database and would like to calculate standard deviation based on a condition "Yes" or "No".

The attached file is a small selected example of my database.

In cell B10, I am using the following array formula to calculate the standard deviation: "{=STDEV(($D$1:$IV$1="No")*(D2:IV2))}".

Please note that in some instances, the cell in the "Value" row will be blank. Again, I have shown this above.

Please can someone help me to obtain the correct answer of "6.00" and not "6.31" as calculated by the array formula.

What change(s) do I need to make to my array formula?

Many thanks.

I am trying to calculate the daily standard deviation from a column of daily percentage changes in a stocks price. What I would need is a monthly value that calculates the standard deviation of the daily stock prices in the respective month.

I have attached a simplified version of my excel sheet;

-Relevant columns are marked blue

-Column C gives the number of the month so that I can calculate the monthly return

-I would like to have the standard deviation of each month's daily return similarly to the monthly returns calculated in column H.

I would we really grateful for any help or hint how it could work!!

I was wondering, how do you use STDEV so that it only takes into account numbers that aren't equal to zero ( != 0).

For example,

Columns A-H have 7 numbers, 3 of which are 0. How do I use =STDEV so that it only calculates the standard deviation only using the 4 numbers?

Thanks a lot,

numbers. I need to find the standard deviation of the product of the numbers

in each row.

Eg:

a b c product

2 5 4 40

3 6 5 90

4 7 6 168

We need to find STDEV of the product column.

I need this without calculating the product separately just in one formula

which would give the stdev of the product.

I have a set of % score values (e.g. 88%, 94%, 82%, 67%)

I would like to know if there is a relatively simple formula that can be used to calculate the AVERAGE DEVIATION from 100%, as opposed to the AVERAGE DEVIATION from the mean, which is what the excel function =AVEDEV will return.

E.g. The Average Deviation from the mean for the the above set of values is 8.25%, whereas the Average Deviation from 100% is 17.25%

I can create a complex manual formula which sums absolute values of the variation from 100% of each score, and then divides by the count total of the number of scores, however this is flawed for several reasons, including the fact it is very time consuming when used across a large set of scores, and it is corrupted when a non score exists in a cell (some of the 'scores' may be '-', in which case they should be ignored and not included in the calculation of the average deviation from 100% by treating as a 0% score (and a deviation of 100% from 100%)

I hope this makes sense.

Any help would be much appreciated.

James

1- Is Hollyoaks significantly less popular than Eastenders at the 99% confidence level (Assume that 99% is 2.5 standard deviations)

2- Is the proportion of people who have no interest in Soaps significantly less than those who prefer to watch the Street (Coronation Street) at the 95 %confidence level.

3-Is the proportion of respondants who prefer the Street significantly more than those who prefer Emmerdale at the 95% confidence level?

(Assume 95% is 2 standard deviations from the mean)

4-What is the Standard Error for the proportion of respondents who prefered Eastenders?

Show your answer as a percentage to 4 decimal places

hope you dont mind...all the work.it probably looks to be very easy stuff for you,but for me it is a nightmare.

Attached Files

Through using the Excel software ?

Please e-mail me on

Through using the Excel software ?

Please e-mail me on basem@demountable-partitions.com