AvgNErr(TheRange)

AvgNErr is a function within ddGlobal and forms part of the Func_Numeric suite of macros. This macro was originally created as I actually love to have controlled errors in my formulas (especially the #N/A error) as there is a hidden trick with #N/A and graphs.  The #N/A error does not display on graphs.  This suddenly allows on the ability to create some really funky moving graphs and animations on the screen especially when you use CTRL+SHFT+A and CTRL+SHFT+Z to manipulate the numbers…

Any event, because I occasionally include “#N/A”s in my cells, I needed a set of macros that is able to perform basic calculations by ignoring cells with errors in them.  If you want to calculate the average of 5 numbers, and one of the cells have an error in it, then the standard Excel functions will get seriously confused and just return the first error it encounters.

See the below example for how AvgNErr works:

  Scenario 1 Scenario 2 Scenario 3
Value: A 10 10 10
Value: B 20 20 # N/A
Value: C 30 # N/A 30
Value: D 40 40 # NAME?
Value: E 50 50 50
Avgerage(A:E) 30 # N/A # N/A
AvgNErr(A:E) 30 30 30

The formula for calculating averages by ignoring errors is as follows:

AvgNErr(B2:B6)

Below is the source code for the macro

Function AvgNErr(XRange As Range) As Double
'This function calculates an average over a range.
'It excludes all cells which values are not Numeric (or blank)
'can be applied over error fields as well.
'Written by Ursula
'last updated by Denis Dell (4/9/2001)in order to exclude a blank field
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) Then
          Counter = Counter + 1
          Total = Total + .Cells(RowN, ColN).Value
        End If
      Next
    Next
    AvgNErr = Total / Counter
  End With
End Function

Microsoft have made some serious enhancements in the 2010 version of Excel and now includes the following functions which was not available back in the day when Pentium 1′s still ruled the world.

+Average()
+AverageA()
+AverageIF()
+AverageIFs()
+AvgDev()

If you’d like to learn more about these standard built in macros, the Excel help documents is quite detailed.

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



Leave a Reply