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