?
Solved

Excel UDF to calculate STDEV IF

Posted on 2012-08-20
7
Medium Priority
?
798 Views
Last Modified: 2012-08-20
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)),"")}
0
Comment
Question by:JohnNZExcel
  • 5
  • 2
7 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 38313845
Excel 2003 (.xls) or do you have Excel 2007+?
0
 
LVL 24

Expert Comment

by:Steve
ID: 38313853
IF 2007+ then

1) =AVERAGEIFS(A1:A100,B1:B100,"YES")
0
 

Author Comment

by:JohnNZExcel
ID: 38313859
Thanks for your quick action.  Sorry if i was not clear enough - i am looking to do this with VBA in a UDF
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Expert Comment

by:Steve
ID: 38313874
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
 
LVL 24

Expert Comment

by:Steve
ID: 38313896
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
 
LVL 24

Accepted Solution

by:
Steve earned 2000 total points
ID: 38313939
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
 

Author Closing Comment

by:JohnNZExcel
ID: 38314784
Thanks for your speed and time
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question