Excel UDF to calculate STDEV IF

Hi Experts,
I have a large dataset and i am struggling with slow calculation times due to some big array formulae.  As you can see the formula has a couple of slow calculating components: an Average IF and a STD IF array.
Can anyone provide me with some code for a UDF that will replicate:
1. an Average IF i.e. average of cells A1:A100 if cells B1:B100 = "Yes"
2. a STD IF i.e. stdev of a range of data in cells A1:A100 if the number is greater than zero


{=IFERROR((AVERAGEIF(Response,A34,_1._Motivating)-AVERAGE(_1._Motivating))/AVERAGE(STDEV(IF(Response=A34,IF(_1._Motivating>0,_1._Motivating))),STDEV(_1._Motivating)),"")}
JohnNZExcelAsked:
Who is Participating?
 
SteveCommented:
And this would be the function to calculate the StdDev:

Function NewStDev(Values As Range, GreaterThan As Double) As Double

Dim Arr1()

Dim CurTotal As Double
Arr1 = Values


For x = LBound(Arr1) To UBound(Arr1)
Debug.Print Arr1(x, 1)
If Arr1(x, 1) > GreaterThan Then
    CurTotal = CurTotal + Arr1(x, 1)
    y = y + 1
End If
Next

TheAverage = CurTotal / y

For x = LBound(Arr1) To UBound(Arr1)
Debug.Print Arr1(x, 1)
If Arr1(x, 1) > GreaterThan Then
    dev = dev + (Arr1(x, 1) - TheAverage) ^ 2
End If
Next
NewStDev = (dev / (y - 1)) ^ 0.5

End Function

Open in new window


See attached example workbook...
Average-and-StDev.xlsm
0
 
SteveCommented:
Excel 2003 (.xls) or do you have Excel 2007+?
0
 
SteveCommented:
IF 2007+ then

1) =AVERAGEIFS(A1:A100,B1:B100,"YES")
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
JohnNZExcelAuthor Commented:
Thanks for your quick action.  Sorry if i was not clear enough - i am looking to do this with VBA in a UDF
0
 
SteveCommented:
Using a UDF in VBA will be slower than the in-build functions (even the slower Arrays)

2) {=STDEV(IF(A1:A20>0,A1:A20))}

It can be done as UDF if you like.
0
 
SteveCommented:
Here is the UDF for the Average:

Function NewAverage(YesNoS As Range, Values As Range, sFind As String) As Double

Dim Arr1()
Dim Arr2()
Dim CurTotal As Double
Arr1 = YesNoS
Arr2 = Values

For x = LBound(Arr1) To UBound(Arr1)

Debug.Print Arr1(x, 1)
If Arr1(x, 1) = sFind Then
    CurTotal = CurTotal + Arr2(x, 1)
    y = y + 1
End If
Next
NewAverage = CurTotal / y

End Function

Open in new window

0
 
JohnNZExcelAuthor Commented:
Thanks for your speed and time
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.