Free Microsoft Excel 2013 Quick Reference

# Calculating standard deviation from histogram data

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.

## Related Results

### Calculate Monthly Standard Deviation From Daily Data

Hi,

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.

### Calculating Returns and Standard Deviation from a data sample of share prices.

Hi Guys,

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!

### Calculate one standard deviation from mean

I need to caclulate one standard deviation from the mean of the following figures:

91.6
90.9
92.4
92.5

Can anyone help? Thanks so much!

### Up/Downside standard deviation

Hello Everyone,

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,

### How to calculate Mean, Median, Mode and standard deviation in group data?

Hello,

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!

### Median, Average, and Standard Deviation from large set of data

Hi all,

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???

### Monthly Standard deviation for daily returns

Hi,

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

### Average With Standard Deviation In Pivot Graph/Chart

I've just discovered how extraordinarily usefull pivot-tables can be. But I am still having problems with its pivot-charts, especially in VBA.

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
If wsExists(wksPivotTable, wkbNow) Then wkbNow.Sheets(wksPivotTable).Delete
On Error Resume Next
wkbNow.Charts(wksPivotChart).Delete
On Error Goto 0
'---------------------------------------------------------
'---------------------------------------------------------
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
'-------------------------------------------------
'-------------------------------------------------
For Each g In arrRow
With .PivotFields(CStr(g))
.Orientation = xlRowField
.Position = 1
End With
Next g
'-------------------------------------------------
'-------------------------------------------------
For Each f In arrColumn
With .PivotFields(CStr(f))
.Orientation = xlColumnField
.Position = 1
End With
Next f
'-------------------------------------------------
'-------------------------------------------------
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
'---------------------------------------------------------------
'---------------------------------------------------------------
With pChart
.SetSourceData Source:=pTable.Range("B4")
.Location Where:=xlLocationAsNewSheet
.ChartType = xlColumnClustered 'chart type
.name = wksPivotChart
End With
End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```

### Create Standard Deviation From Array

Hi everyone,
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 Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```

### Selectively Calculating Standard Deviation Using Array Formulas or Database Functio

I have a large range (A30:L5672) of data. Each cell in column C contains one of 25 different record labels (text) which groups my data into 25 different groups. Column H contains test scores. I want to calculate the standard deviation of the test scores for each of the groups of data without restructuring my range of data. Does anyone know how to do this, perhaps using array formulas or excel's database functions? I'd appreciate assistance. Thanks. Ben

### Standard deviation calculations with excel

Hi,

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?

### Excel Charts: Can I show standard deviation on selected series?

3D bar chart:
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?

### Calculate Standard Deviation By Cell Color

Hi,
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?

```
VB:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult

''''''''''''''''''''''''''''''''''''''
'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 Function

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```

### Log standard deviation

This may be a little off topic, but i have a data series that i am trying to get the log normal standard deviation for. Ive tried using the lognormdist function but am unsure how to derive the standard deviation from there. If anybody has an idea for this please let me know.

Thanks

Hi

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

### Standard Deviation question

I want to compute the standard deviation of each row of data, but each row might have different starting points. In other words Row 1 has data from a1:a12, but Row 2 has data from A5:A12. Rather than right unique formulas for each row, is there some function similar to =averageif() where it will only take the standard deviation from non-zero cells?

### MS Excel: Standard Deviation Conditional Calculation

Hello,

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.

### Daily Standard Deviation

Dear members,
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!!

### Calculating Standard Deviation for certain numbers only

Hi all...

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,

### How to calculate Standard deviation of product of numbers?

There are 3 columns, A, B and C. Each column has 10 rows each containing
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.

### Calculating AVERAGE DEVIATION from a SPECIFIED POINT??

Hi People,

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

### Standard deviation questions

please i would love to have some help .i am attaching a zip with excel to with data to solve some of these questions,that i dont undestand

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?

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

### How do you find the SD(standard deviation ) for the multiple reg

How can we find the Standard deviation from the multiple regression out comes ?
Through using the Excel software ?