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

Tread Code	RPP	Fallnum
A	16.1	0
B	10	0
D	2	1
C	4	3
A	9	1
A	4	1
E	6.9	6

<colgroup><col width="65" span="3" style="width: 65pt; text-align: center;"> </colgroup><tbody>
</tbody>

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]

	A	B	C	D
1	Tread Code	RPP	Fallnum	StaDev.S = 3.535534
2	A	16.1	0
3	B	10	0
4	D	2	1
5	C	4	3
6	A	9	1	9
7	A	4	1	4
8	E	6.9	6

<tbody>
</tbody>

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