Solved

Posted on 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)),"")}

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

7 Comments

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

It can be done as UDF if you like.

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

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

See attached example workbook...

Average-and-StDev.xlsm

Title | # Comments | Views | Activity |
---|---|---|---|

Check version | 13 | 45 | |

Excel Formula: define specific name every 100 lines | 6 | 20 | |

MS Excel default colors in styles | 2 | 13 | |

Help adding more columns to my pivot table | 19 | 17 |

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

Connect with top rated Experts

**17** Experts available now in Live!