ddAverage(TheRange,Optional=Exclude)

ddAverage is a function within ddGlobal and forms part of the Func_Numeric suite of macros. This function allows you to exclude specific numbers from your calculation.

Assume you are given 100 accounts with a score assigned to it, but you would like to exclude all the accounts with a negative score from your calculation, then this is the formula you could use. (assume a negative score of -1 indicate an error in the extract file so you don’t want that score in your calculation of what the population’s average score is).

When this macro was written, the AverageIF and AverageIFs functions that is now available in Excel did not exist. The AverageIF and AverageIfs has to some extend replace this formula but I still like ddAverage as it is much more user friendly to enter a formula. (I will however concede that one can get very creative with the new excel functions)

See the attached sample spreadsheet on how ddAverage works as well as the “new” AverageIf formula found in Excel.

  Scenario 1 Scenario 2
Value: A 10 10
Value: B 20 20
Value: C -1 blank
Value: D 40 40
Value: E 50 50
Average(A:E) 23.8 30
AverageIf(A:E,">-1",A:E) 30 30
ddAverage(A:E,-1) 30 30

The three formulas used is entered as follows:

=+AVERAGE(B2:B6)
=+AVERAGEIF(B2:B6,">-1",B2:B6)
=+ddAverage(B2:B6,-1)

The first two macros is standard excel functions while the third one is part of the ddGlobal set.

Function ddAverage(XRange As Range, Optional Exclude As Double = -99999999) As Double
'This function calculates an average over a range, but exclude certain values as well as errors
'It excludes all cells which values are not Numeric (or blank) or which is equal to exclude
'can be applied over error fields as well.
'Written by Denis Dell
'last updated by Denis Dell (2/2/2005)
Dim RowN, ColN, Counter As Integer
Dim Total As Double
Counter = 0
Total = 0
  With XRange
    For RowN = 1 To .Rows.Count
      For ColN = 1 To .Columns.Count
        If IsNumeric(.Cells(RowN, ColN).Value) And Not IsEmpty(.Cells(RowN, ColN).Value) And Exclude <> (.Cells(RowN, ColN).Value) Then
          Counter = Counter + 1
          Total = Total + .Cells(RowN, ColN).Value
        End If
      Next
    Next
    If Counter = 0 Then ddAverage = 0 Else ddAverage = Total / Counter

  End With
End Function

ddAverage does not work if there is errors in TheRange, so that may be an future enhancement should it ever be required.

This entry was posted in Articles, ddGlobal, Tips and tagged , . Bookmark the permalink.



Leave a Reply