Calculating Standard Deviation in a column based on criteria from another column using VBA

[djurishi]

Hi,

I have tried looking everywhere on google but, no luck.

I need help calculating standard deviation based on criteria from other columns using VBA. What I mean is I need to calculate standard deviation when column A (Tread Code) is = to A and when Column C (Fall Num) is = to 1 and then based on that criteria calculate the corresponding values in B when those criterias are met. Below is an example of what I would be testing for. I am typically dealing with 40,000 rows x 20 columns. I would prefer not to use a pivot table since I have create a dashboard for upper management.

<!--StartFragment-->
<!--EndFragment-->​

[TABLE="width: 195"]
<colgroup><col width="65" span="3" style="width: 65pt; text-align: center;"> </colgroup><tbody>[TR]
[TD="width: 65, align: center"]Tread Code[/TD]
[TD="width: 65, align: center"]RPP[/TD]
[TD="width: 65, align: center"]Fallnum[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]16.1[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]D[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]E[/TD]
[TD="align: center"]6.9[/TD]
[TD="align: center"]6[/TD]
[/TR]
</tbody>[/TABLE]


Any help help would be great.

Thanks,
Daniel

 

[WarPigl3t]

Confused.

 

[djurishi]

Sorry for if you are confused!

What i need is

If ColA.value = "A" And ColC.value =1 Then

When ColA.value = "A" and ColC.value =1 take the ColB value and calculate standard deviation.

Based on this criteria with the table I posted I would take the value of ColB in row 5 and 6 and then it calculates standard deviation.

I am just trying to find the most efficient way to do this since the raw data that gets imported into excel which has 40,000 rows x 20 columns.

Hope this makes sense

 

[WarPigl3t]

Makes sense. I'm going to try and put a formula together for you. If I can't figure it out, I'll just create a VBA macro.

 

[WarPigl3t]

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Tread Code
[/TD]
[TD]RPP[/TD]
[TD]Fallnum[/TD]
[TD]StaDev.S = 3.535534
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]A[/TD]
[TD]16.1[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]B[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]D[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]C[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]A[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]A[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]E[/TD]
[TD]6.9[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I used a helper column. In cell D2 i put =IF(AND(A2 = "A", C2 = 1),B2,"")
Then drag the formula down the column all the way down to D8.
Then in Cell D1 I put ="StaDev.S = " & ROUND(STDEV.S(D2:D8),6)

 

[WarPigl3t]

type this into whatever cell you want...
=customFunction("Sample")

Put this code in your vba macros

Function customFunction(SoP As String)
    'Enter either "population" or "sample" into the function arguments. _
    'For example in cell F1 type =customFunction(sample)
    sampleOrPopulation = SoP
    trendCode = "A"
    trendSearch = "A"
    RPP = "B"
    fallNum = "C"
    fallSearch = 1
    roundDec = 2 'How many decimals do you want the answer to round?
    numRow = 2
    outMean = 0
    outCounter = 0
    Do Until Range(trendCode & numRow).Value = ""
        If Range(trendCode & numRow).Value = trendSearch _
        And Range(fallNum & numRow).Value = fallSearch Then
            outMean = outMean + Range(RPP & numRow).Value
            outCounter = outCounter + 1
        End If
        numRow = numRow + 1
    Loop
    If outMean <> 0 Then
        outMean = outMean / outCounter
        numRow = 2
        outCounter = 0
        Do Until Range(trendCode & numRow).Value = ""
            If Range(trendCode & numRow).Value = trendSearch _
            And Range(fallNum & numRow).Value = fallSearch Then
                outVariance = Range(RPP & numRow).Value - outMean
                outVariance = outVariance * outVariance
                varSum = varSum + outVariance
                outCounter = outCounter + 1
            End If
            numRow = numRow + 1
        Loop
        errorBool = False
        'for a sample use n-1 _
        same as outCounter -1
        If LCase(sampleOrPopulation) = "sample" Then
            outCounter = outCounter - 1
        ElseIf LCase(sampleOrPopulation) = "population" Then
            outCounter = outCounter
        Else
            errorBool = True
        End If
        If errorBool = False Then
            actualVariance = Round(varSum / outCounter, roundDec)
            standardDeviation = Round(Sqr(Abs(actualVariance)), roundDec)
            customFunction = standardDeviation
        Else
            customFunction = "Please specify if this is a sample or population into the function arguments"
        End If
    Else
        customFunction = "There are no Trend codes and Fallnums that match"
    End If

End Function

 

[Stadem]

For anyone with a similar problem as this, the problem can and probably should be solved using an array formula with nested IF statements like so:

=STDEV(IF(A1:A1000="A",IF(C1:C1000=1,B1:B1000,""),""))

The formula must be array-entered. Click on the cell, hit F2 or click in the formula bar, and press CTRL+SHIFT+ENTER to create the array formula.

 

[JAHoff]

I need to use an equation like that but I need to calculate STEV when A1:A1000 is between two values. I cannot get an AND function to work. Any help appreciated.

 

[JAHoff]

Got it