Need helps with VBA excel on calculate the mean and standard

mars91 · Jul 8, 2011

I am new to VBA Marco for Excel. I am still learning it. I had only basic knowledge of it. I am doing a project, I need some helps with the code. What I need to do is to count Standard Deviation and Mean. I got the code from Anthony’s vba page.. But I need further improve to do something different. Sorry, I am still quite weak in macro. Thus, I need help with it.

Below is the code to calculate std.dev and mean.

Code:

Sub compute() 
Dim Arr(10) As Single 
Dim Average As Single 
Dim Std_Dev As Single 

For i = 1 To 10 
Arr(i) = Sheets("Sheet1").Cells(i, 1) 
Next i 

Average = Mean(10, Arr) 
Std_Dev = StdDev(10, Arr) 
Sheets("Sheet1").Cells(12, 1) = Average 
Sheets("Sheet1").Cells(13, 1) = Std_Dev 

End Sub 



Function Mean(k As Long, Arr() As Single) 
Dim Sum As Single 
Dim i As Integer 

Sum = 0 
For i = 1 To k 
Sum = Sum + Arr(i) 
Next i 

Mean = Sum / k 

End Function 

Function StdDev(k As Long, Arr() As Single) 
Dim i As Integer 
Dim avg As Single, SumSq As Single 


avg = Mean(k, Arr) 
For i = 1 To k 
SumSq = SumSq + (Arr(i) - avg) ^ 2 
Next i 

StdDev = Sqr(SumSq / (k - 1)) 

End Function

This code work well. The problem is that it counts from cells A1 to A10. So I must have values enter in the 10 cells in order to get the correct values. This mean if one of the cells is blank, its will count the blank cell with a value of 0. The outcome result of Mean on cell A12 and Standard Deviation on cell A13 will be wrong.

What I want to do ??

In the excel sheet which I am working on. There are around 10 different names of items.
Example, cell A1 have the item name as “AB” and its “value” is in cell B1. Cell A2 have the item name as “CD” and its “value” is in cell B2.
There are about 10 different names with values in the cells beside them, over 60 thousand data and all are in disorderly manner. What I want is like improve on this code so it can count the right number of cells for each item instead of up to 10 rows. It is like getting the last row of each item. So I can get Mean and Standard Deviation of each of the Item correctly.
I still trying to figure out how to code it, really need help.

Thank a lot!

mirabeau · Jul 8, 2011

If you've got about 60,000 (or indeed any number of rows) with item names in column A and their values (maybe some empty) in Column B, then you might consider this code

Code:

Sub av_and_sd()
Dim d As Object, nr&, a
Dim c(), i&, x
Set d = CreateObject("scripting.dictionary")
nr = Range("A:B").Find("*", searchorder:=xlByRows, _
    searchdirection:=xlPrevious).Row
a = Range("A:B").Resize(nr)
ReDim c(1 To nr, 1 To 6)
For i = 1 To nr
    x = a(i, 1)
    If Not d.exists(x) Then
        d.Add x, d.Count + 1
        c(d(x), 1) = a(i, 1)
        If Not IsEmpty(a(i, 2)) Then
            c(d(x), 4) = 1
            c(d(x), 5) = a(i, 2)
            c(d(x), 6) = a(i, 2) ^ 2
        End If
    Else
        If Not IsEmpty(a(i, 2)) Then
            c(d(x), 4) = c(d(x), 4) + 1
            c(d(x), 5) = c(d(x), 5) + a(i, 2)
            c(d(x), 6) = c(d(x), 6) + a(i, 2) ^ 2
        End If
    End If
Next i
For i = 1 To d.Count
    c(i, 2) = c(i, 5) / c(i, 4)
    c(i, 3) = ((c(i, 6) - c(i, 2) * c(i, 5)) / (c(i, 4) - 1)) ^ 0.5
Next i
[d2].Resize(d.Count, 3) = c
[d1] = "Item": [e1] = "Average": [f1] = "StDev"
End Sub

mirabeau · Jul 8, 2011

Although I see you seem to have made the same post more than once
http://www.mrexcel.com/forum/showthread.php?t=562840

Usually, it's a good idea not to re-post your entire problem, but it's very acceptable to jog people's memory/motivation by adding another post to the original thread which contains just "Bump" or similar.

This brings it back to the top of the list on page 1, and thus to more readers' attention.

mars91 · Jul 8, 2011

Thank a lot!
I will try out the code that you give. Well,i do not understand your code.
I do not have blank for those values after updated all the data. Do you have any simpler code?? I am still quite weak in vba. Srry bro,thank you for helping me. Or can you guide me about you code??

mirabeau · Jul 8, 2011

mars91 said:
Thank a lot!
I will try out the code that you give. Well,i do not understand your code.
I do not have blank for those values after updated all the data. Do you have any simpler code?? I am still quite weak in vba. Srry bro,thank you for helping me. Or can you guide me about you code??

Hmm,

Explaining details of how that code works isn't that easy if you haven't done much in the way of VBA.

I posted it in the expectation you would try running it, say on a small set of sample data, and reporting back whether or not it does what you want.

Basically, all that code does is to select each item from column A, even if they are in highly disorganized order, and calculate the averages and standard deviations of the numbers for each item from column B.

Most of the selection and calculating operations are done in the computer's memory rather than on the worksheet itself, so it should be fairly fast. Say maybe less than a second to do your 60,000 rows.

The code bit called "scripting.dictionary" near the top is just a device to select out the numbers associated with each AB, CD etc. even if these are in disorganized order.

You can maybe do it simpler by first sorting your disorganized data into organized form, then selecting worksheet ranges and using the standard Excel Average() and StDev() formulas.

But you used VBA code so I guess that's what you wanted.

John_w · Jul 8, 2011

Re: Need helps to modify VBA excel code for the mean & std dev..

Looping through thousands of rows and performing calculations like that will be extremely slow. Instead, try the following code which uses Advanced Filter and worksheet functions AVERAGE and STDEVP.

Advanced Filter needs column headings to work, so you must have column headings in Sheet1 row 1. The code writes to columns E:H on Sheet1 and A:B on Sheet2, so these mustn't contain data otherwise it will be deleted.

Code:

Sub Compute_Average_and_Standard_Deviation()

    Dim row As Long, lastRow As Long
    Dim average As Double, stdDev As Double
    
    With Sheets("Sheet1")
    
        'Create headings for computed columns and Advanced Filter criteria
        
        .Columns("E:H").ClearContents
        .Range("F1").Value = "Average"
        .Range("G1").Value = "Std Dev"
        .Range("H1").Value = .Range("A1").Value     'H1 - Advanced Filter criteria column heading
        
        'Filter column A (Name) unique values to column E
        
        .Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("E1"), Unique:=True
        
        'For each unique Name (in column E row 2 downwards)
        
        row = 2
        While Not IsEmpty(.Cells(row, "E"))
        
            'Put this Name in H2 - Advanced Filter criteria value
            
            .Range("H2").Value = .Cells(row, "E").Value
            
            'Filter the values matching this Name to Sheet2
            
            Sheets("Sheet2").Cells.ClearContents
            .Columns("A:B").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("H1:H2"), CopyToRange:=Sheets("Sheet2").Range("A1"), Unique:=False
            
            'Compute average and standard deviation
            
            With Sheets("Sheet2")
                lastRow = .Cells(Rows.Count, "B").End(xlUp).row
                Debug.Print lastRow
                average = Application.WorksheetFunction.average(.Range(.Cells(2, "B"), .Cells(lastRow, "B")))
                stdDev = Application.WorksheetFunction.StDevP(.Range(.Cells(2, "B"), .Cells(lastRow, "B")))
            End With
            
            .Cells(row, "F").Value = average
            .Cells(row, "G").Value = stdDev
            
            row = row + 1
        Wend
        
        'Clear Advanced Filter data
        
        .Columns("H").ClearContents
        Sheets("Sheet2").Cells.ClearContents

    End With
    
End Sub

mars91 · Jul 10, 2011

Re: Need helps to modify VBA excel code for the mean & std dev..

HI,
Sorry,i dont not understand what you mean by Advanced Filter needs column headings to work, so you must have column headings in Sheet1 row 1. The code writes to columns E:H on Sheet1 and A:B on Sheet2, so these mustn't contain data otherwise it will be deleted.

Sorry,my macro is still weak. Thus i have problem understanding you code.
Need help. Thanks

mars91 · Jul 10, 2011

Okay,thank you. I am trying with that code. I will let you know if it is what i want.

Zack Barresse · Jul 10, 2011

I have combined your threads to one.

mirabeau · Jul 10, 2011

mars91,

did you have some problem with the code I posted in Post#2?

some feedback would be both courteous and helpful to any readers, and probably ultimately produce a better result for yourself.

Need helps with VBA excel on calculate the mean and standard

mars91

New Member

Excel Facts

mirabeau

Banned user

mirabeau

Banned user

mars91

New Member

mirabeau

Banned user

John_w

MrExcel MVP

mars91

New Member

mars91

New Member

Zack Barresse

MrExcel MVP

mirabeau

Banned user

Similar threads

Forum statistics

Share this page

Need helps with VBA excel on calculate the mean and standard

New Member

Excel Facts

Banned user

Banned user

New Member

Banned user

MrExcel MVP

New Member

New Member

MrExcel MVP

Banned user

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock