JohnNZExcel
asked on
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(Respo nse,A34,_1 ._Motivati ng)-AVERAG E(_1._Moti vating))/A VERAGE(STD EV(IF(Resp onse=A34,I F(_1._Moti vating>0,_ 1._Motivat ing))),STD EV(_1._Mot ivating)), "")}
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(Respo
Excel 2003 (.xls) or do you have Excel 2007+?
IF 2007+ then
1) =AVERAGEIFS(A1:A100,B1:B10 0,"YES")
1) =AVERAGEIFS(A1:A100,B1:B10
ASKER
Thanks for your quick action. Sorry if i was not clear enough - i am looking to do this with VBA in a UDF
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.
2) {=STDEV(IF(A1:A20>0,A1:A20
It can be done as UDF if you like.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your speed and time