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.


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.

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!

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!

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,

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!

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

Please help!
Thanks in Advance

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

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

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


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


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

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?

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?

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

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


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

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?

Hello,

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.

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

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,

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.

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

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

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

Please e-mail me on

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

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