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: B||20||20||# N/A|
|Value: C||30||# N/A||30|
|Value: D||40||40||# NAME?|
|Avgerage(A:E)||30||# N/A||# N/A|
The formula for calculating averages by ignoring errors is as follows:
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.