View Full Version : UDF to calculate average according to condition, ignoring blanks

02-11-2011, 02:42 PM
Dear Experts,

I have a UDF calculating averages by getting data from subordinate WBS and ignoring blank cells. For instance, if 1.1 is broken down into 1.1.1 (10), 1.1.2 (12) and 1.1.3 (25), the formula finds the corresponding values for the subordinates of 1.1 and calculates the average ((10+12+26)/3 = 16).
If, however, 2.2 is broken down into 2.1 (50) and 2.2 (blank cell), than the average should be 50.

When I debug my UDF on, say, cell I40 (5.5, which has 3 subordinates 5.5.1 (82%), 5.5.2 (23%), and 5.5.3 (blank cell, that have a subordinate (85%)), I would expect to have an average of 63.(3)%.
This is exactly what I get in the Immediate Window (see below) when I get values from the cells.

?rgeCriteria.Cells(i, 1).Value

?rgeCriteria.Cells(i, 1).Offset(0, 8).Value
The UDF, however, returns me something different. In part because, I guess, it treats 5.5.3 as blank and does not assume that it may have subordinates. It also does not seem to be drawing results from other cells with the same UDF.
Please help to fix the UDF. Your help will be very much appreciated. I am attaching the file for your convenience.

Public Function AVGWBSNB(ByVal rgeCriteria As Range, _
ByVal sCriteria As Range) As Variant

Application.Volatile (True)

Dim i As Integer
Dim sub_num As Integer
Dim blank As Integer
Dim sum As Variant
Dim count As Integer

For i = 1 To rgeCriteria.Rows.count
If rgeCriteria.Cells(i, 1).Value Like sCriteria.Value & ".#" Then
sub_num = sub_num + 1
sum = sum + rgeCriteria.Cells(i, 1).Offset(0, 8).Value

If IsEmpty(rgeCriteria.Cells(i, 3)) Then
blank = blank + 1
End If
End If
Next i

If sub_num = 0 Then
If IsEmpty(sCriteria.Offset(0, 2)) Then
AVGWBSNB = sCriteria.Offset(0, 2).Value
End If
AVGWBSNB = sum / (sub_num - blank)
Exit Function
End If

End Function

Bob Phillips
02-12-2011, 03:59 AM
How about just using this ARRAY formula


02-12-2011, 07:02 AM
Xld, thank you very much. The formula still treats blanks as zeros.
For instance, I am getting 24%, 28% and 9% for 1, 1.1, 1.1.1 while I should be getting 44%, 47%, 17%.

02-12-2011, 10:25 AM
Bob, FYI, x-posted at Excel Forum (posted a formula solution) & OzGrid - I have insufficient post count to add the links myself

02-12-2011, 06:06 PM
A formula and a macro too are on Excel Forum.
Thanks a lot.

Bob Phillips
02-13-2011, 03:10 AM
Well, thank you very much for wasting my time, it is appreciated.

02-14-2011, 08:52 AM
Dear XLD,
My apologies for any inconveniences. The answer on Excel Forum was provided after you submitted your formula.
Thank you for your efforts.