PDA

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

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.

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

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

=IF(AND(COUNTIF(\$A\$3:\$A\$45,A3&".?")=0,ISBLANK(C3)),"",AVERAGE(IF(LEFT(\$A\$3:\$A\$45,LEN(A3))=A3,\$C\$3:\$C\$45)))

Dimitri
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%.

DonkeyOte
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

Dimitri
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.

Dimitri
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.