MaxNErr(TheRange)

MaxNErr is a function within ddGlobal and forms part of the Func_Numeric suite of macros. This macro was originally created as I tend to use controlled errors in my formulas.  As such, since I want to be able to ignore an error and still do some basic mathematical instructions I needed to write a whole set of “NErr” (No Error”) macros.

If you want to calculate the maximum number out of 5 numbers, and one of the cells have an error in it, then the standard Excel function (Large, or Max) will get seriously confused and just return the first error it encounters.

See the below example for how MaxNErr 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
Max(A:E) 50 # N/A # N/A
Large(A:E,1) 50 # N/A # N/A
MaxNErr(A:E) 50 50 50

The formula for calculating the maximum value in the five rows is:

MaxNErr(B2:B6)

Below is the source code for the macro

Function MaxNErr(TheRange As Range) As Double
'The function return the maximum number in a range.
'Last updated by Denis Dell
Dim i, j As Integer
  With TheRange
    For i = 1 To .Rows.Count
      For j = 1 To .Columns.Count
        If IsNumeric(.Cells(i, j).Value) Then
            If .Cells(i, j).Value > MaxNErr Then MaxNErr = .Cells(i, j).Value
        End If
      Next j
    Next i
  End With
End Function

Macro is short, efficient and it works.  I am sure it could be optimised more, but right now it does what it says on the tin.

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



Leave a Reply