Dimitri
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 5.5.3.1 (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
5.5.3
5.5.2
5.5.1
?rgeCriteria.Cells(i, 1).Offset(0, 8).Value
0.85
0.23
0.82
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 = ""
Else
AVGWBSNB = sCriteria.Offset(0, 2).Value
End If
Else
AVGWBSNB = sum / (sub_num - blank)
Exit Function
End If
End Function
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 5.5.3.1 (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
5.5.3
5.5.2
5.5.1
?rgeCriteria.Cells(i, 1).Offset(0, 8).Value
0.85
0.23
0.82
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 = ""
Else
AVGWBSNB = sCriteria.Offset(0, 2).Value
End If
Else
AVGWBSNB = sum / (sub_num - blank)
Exit Function
End If
End Function